Parallel optimized triggers in parallel processing database systems

• US 6,374,236 B1
• Filed: 12/04/1999
• Issued: 04/16/2002
• Est. Priority Date: 12/04/1999
• Status: Expired due to Term

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation (NCR Voyix Corporation)
• Inventors: Chen, James, Hodgens, Mark Anthony, Kaufmann, Frederick Stuart, Stegelmann, Rolf Günter Erich, Hoang, Chi Kim
• Primary Examiner(s): Black, Thomas
• Assistant Examiner(s): MOFIZ, APU M
• Application Number:
  US09/454,729
• Time in Patent Office: 864 Days
• Field of Search: 707/2, 707/1, 707/10, 707/100, 707/503, 707/512, 707/530, 709/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method, apparatus, and article of manufacture for accessing a subject table in a computer system. The subject table is partitioned across a plurality of processing units of the computer system. A trigger is defined for the subject table, such that when a triggering event occurs on the subject table, a triggered action is performed. Both the triggering event and triggered action can be performed in parallel by the computer system. A spool table is generated for each partition of the subject table, wherein the spool table stores one or more necessary records accessed from the subject table by the triggering event. In this manner, triggered actions that refer to these necessary rows need not access the subject table again.

Claims

1. A method for accessing a subject table in a computer system, comprising:

(a) defining a trigger for a subject table partitioned across a plurality of processing units of a computer system, wherein each of the processing units manages at least one partition of the subject table;

(b) performing a triggering event in the computer system as defined by the trigger, wherein the triggering event is split into a plurality of step messages in order to access the subject table partitioned across a plurality of processing units of the computer system, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the subject table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units;

(c) generating a spool table in the computer system in response to the triggering event, wherein the spool table stores one or more necessary records accessed from the subject table by the triggering event, the spool table has a plurality of partitions, each of the partitions of the spool table corresponds to one of the partitions of the subject table, and each of the processing units manages at least one of the partitions of the spool table; and

(d) performing a triggered action in the computer system as defined by the trigger and in response to the triggering event, wherein the triggered action is split into a plurality of step messages in order to access the necessary records stored in the spool table, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the spool table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units.

2. The method of claim 1, wherein the generating step (c) further comprises:

retrieving the necessary records from the partition of the subject table, and storing the necessary records into the corresponding partition of the spool table.

3. The method of claim 1, wherein an optimizer function uses information from the spool table to generate more efficient execution plans for the triggered actions.

4. The method of claim 1, wherein a definition of the trigger includes a REFERENCING clause that allows a user to specify a name for the spool table.

5. An apparatus for accessing a subject table in a computer system, wherein the table has a plurality of partitions, comprising:

(a) a computer system having a plurality of processing units, each with zero or more data storage devices coupled thereto, wherein the data storage devices store at least one subject table;

(b) logic, performed by the computer system, for;

(1) defining a trigger for a subject table partitioned across a plurality of processing units of a computer system, wherein each of the processing units manages at least one partition of the subject table;

(2) performing a triggering event in the computer system as defined by the trigger, wherein the triggering event is split into a plurality of step messages in order to access the subject table partitioned across a plurality of processing units of the computer system, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the subject table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units;

(3) generating a spool table in the computer system in response to the triggering event, wherein the spool table stores one or more necessary records accessed from the subject table by the triggering event, the spool table has a plurality of partitions, each of the partitions of the spool table corresponds to one of the partitions of the subject table, and each of the processing units manages at least one of the partitions of the spool table; and

(4) performing a triggered action in the computer system as defined by the trigger and in response to the triggering event, wherein the triggered action is split into a plurality of step messages in order to access the necessary records stored in the spool table, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the spool table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units.

6. The apparatus of claim 5, wherein the logic for generating (3) further comprises logic for:

retrieving the necessary records from the partition of the subject table, and storing the necessary records into the corresponding partition of the spool table.

7. The apparatus of claim 5, wherein an optimizer function uses information from the spool table to generate more efficient execution plans for the triggered actions.

8. The apparatus of claim 5, wherein a definition of the trigger includes a REFERENCING clause that allows a user to specify a name for the spool table.

9. An article of manufacture embodying logic for accessing a subject table in a computer system, the logic comprising:

(a) defining a trigger for a subject table partitioned across a plurality of processing units of a computer system, wherein each of the processing units manages at least one partition of the subject table;

(b) performing a triggering event in the computer system as defined by the trigger, wherein the triggering event is split into a plurality of step messages in order to access the subject table partitioned across a plurality of processing units of the computer system, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the subject table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units;

(c) generating a spool table in the computer system in response to the triggering event, wherein the spool table stores one or more necessary records accessed from the subject table by the triggering event, the spool table has a plurality of partitions, each of the partitions of the spool table corresponds to one of the partitions of the subject table, and each of the processing units manages at least one of the partitions of the spool table; and

(d) performing a triggered action in the computer system as defined by the trigger and in response to the triggering event, wherein the triggered action is split into a plurality of step messages in order to access the necessary records stored in the spool table, each of the step messages is assigned to one of the processing units managing one or more of the partitions of the spool table, and at least some of the step messages can be performed simultaneously and in parallel by the processing units.

10. The method of claim 9, wherein the generating step (c) further comprises:

retrieving the necessary records from the partition of the subject table, and storing the necessary records into the corresponding partition of the spool table.

11. The method of claim 9, wherein an optimizer function uses information from the spool table to generate more efficient execution plans for the triggered actions.

12. The method of claim 9, wherein a definition of the trigger includes a REFERENCING clause that allows a user to specify a name for the spool table.