Method and system for updating value correlation optimizations

• US 7,272,591 B1
• Filed: 01/22/2004
• Issued: 09/18/2007
• Est. Priority Date: 01/22/2004
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation (NCR Voyix Corporation)
• Inventors: Sinclair, Paul Laurence, Ghazal, Ahmad Said
• Primary Examiner(s): Cottingham; John
• Assistant Examiner(s): Rayyan; Susan F
• Application Number:
  US10/762,735
• Time in Patent Office: 1,335 Days
• Field of Search: 707 1- 6, 707100-102
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A method, computer program, and database system are disclosed for executing database queries. The database includes a first table (T1) having a primary key (PK) column and a first correlated value column (CV1) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV2). One implementation of the method includes preparing a database query for execution based at least in part on application of a derived constraint rule (DCR) having the form, (PK=FK)→CV₂+C₁≦CV₁≦CV₂+C₂, where C₁ and C₂ are constants and “→” means “implies,” to produce an execution plan. Abort steps that are conditioned on changes in the DCR are also included in the execution plan. The plan is then executed.

Claims

1. A method for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), comprising the steps of:

preparing a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan;

including abort steps conditioned on changes in the DCR in the execution plan; and

executing the plan.

2. The method of claim 1, further comprising the step of including lock acquisition steps in the execution plan prior to the abort steps.

3. The method of claim 1, further comprising the step of placing locks barring at least inserts and updates of T2 and updates of T1 from before execution until after execution.

4. A method for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), comprising the steps of:

preparing a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan that includes one or more DCR dependent steps;

including update steps conditioned on changes in the DCR in the execution plan, the update steps modifying the DCR dependent steps based at least in part on those changes; and

executing the plan.

5. The method of claim 4, further comprising the step of including lock acquisition steps in the execution plan prior to the update steps.

6. The method of claim 4, further comprising the step of placing locks barring at least inserts and updates of T2 and updates of T1 from before execution until after execution.

7. The method of claim 4, further comprising the step of including abort steps dependent on changes in the DCR and a DCR dependent step executing prior to an update step.

8. A method for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), comprising the steps of:

storing a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

C₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

in a database system dictionary;

placing a read lock on the stored DCR;

after placing the read lock, preparing a database query for execution based at least in part on application of the DCR to produce an execution plan; and

executing the plan.

9. A computer program, stored on a computer-readable storage medium, for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), the program comprising executable instructions that cause one or more computers to:

prepare a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan;

include abort steps conditioned on changes in the DCR in the execution plan; and

execute the plan.

10. The computer program of claim 9 further comprising executable instructions that cause a computer to include lock acquisition steps in the execution plan prior to the abort steps.

11. The computer program of claim 9 further comprising executable instructions that cause a computer to place locks barring at least inserts and updates of T2 and updates of T1 from before execution until after execution.

12. A computer program, stored on a computer-readable storage medium, for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), the program comprising executable instructions that cause one or more computers to:

prepare a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan that includes one or more DCR dependent steps;

include update steps conditioned on changes in the DCR in the execution plan, the update steps modifying the DCR dependent steps based at least in part on those changes; and

execute the plan.

13. A database system for executing database queries, comprising:

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data, including rows organized in tables, stored in one of a plurality of data-storage facilities;

a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁);

a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), and

an optimizer configured to;

prepare a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan that includes one or more DCR dependent steps; and

include update steps conditioned on changes in the DCR in the execution plan, the update steps modifying the DCR dependent steps based at least in part on those changes.

14. A database system for executing database queries, comprising:

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data, including rows organized in tables, stored in one of a plurality of data-storage facilities;

a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁);

a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), and

an optimizer configured to;

prepare a database query for execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

to produce an execution plan; and

include abort steps conditioned on changes in the DCR in the execution plan.

15. The database system of claim 14 where the optimizer is further configured to include lock acquisition steps in the execution plan prior to the abort steps.

16. The database system of claim 14 where the optimizer is further configured to place locks barring at least inserts and updates of T2 and updates of T1 from before execution until after execution.

17. A computer program, stored on a computer-readable storage medium, for executing database queries, the database comprising a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁) and a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), the program comprising executable instructions that cause one or more computers to:

store a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

in a database system dictionary;

place a read lock on the stored DCR;

after placing the read lock, prepare a database query for execution based at least in part on application of the DCR to produce an execution plan; and

execute the plan.

18. A database system for executing database queries, comprising:

one or more nodes;

a plurality of CPUs, each of the one or more nodes providing access to one or more CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more virtual processes;

each virtual process configured to manage data, including rows organized in tables, stored in one of a plurality of data-storage facilities;

a first table (T1) having a primary key (PK) column and a first correlated value column (CV₁);

a second table (T2) having a foreign key (FK) column related to the primary key column of the first table and a second correlated value column (CV₂), and

an optimizer configured to;

store a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV₂+C₁≦

CV₁≦

CV₂+C₂

where C₁ and C₂ are constants, and “

→

”

means “

implies,”

in a database system dictionary;

place a read lock on the stored DCR; and

after placing the read lock, prepare a database query for execution based at least in part on application of the DCR to produce an execution plan.