Method and system for updating value correlation optimizations

• US 7,672,926 B2
• Filed: 08/13/2007
• Issued: 03/02/2010
• Est. Priority Date: 01/22/2004
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Sinclair, Paul Laurence, Ghazal, Ahmad Said
• Primary Examiner(s): Jalil; Neveen Abel
• Assistant Examiner(s): JACKSON, ISAAC TYRONE
• Application Number:
  US11/837,556
• Publication Number:
  US 20070276798A1
• Time in Patent Office: 932 Days
• Field of Search: None
• 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. A frequency of errors due to changes in DCRs is taken into account when preparing 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 (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), comprising the steps of:

preparing a database query for execution on a database comprising 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), said execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV2+C1≦

CV1≦

CV2+C2

where C1 and C2 are constants, and “

→

”

means “

implies,”

to produce an execution plan;

taking into account a frequency of errors due to changes in DCRs when preparing the execution plan, wherein taking into account the frequency of errors due to changes in DCRs when preparing the execution plan comprises determining that a higher cost execution plan that risks no errors due to changes in DCRs is preferable; and

executing the plan.

2. The method of claim 1 wherein taking into account the frequency of errors due to changes in DCRs when preparing the execution plan comprises:

determining that a higher cost execution plan that may require correction of an error due to changes in DCRs is preferable because it has a low cost if there is no error due to changes in DCRs.

3. The method of claim 1 wherein taking into account the frequency of execution errors due to changes in DCRs when preparing the execution plan comprises:

selecting an execution plan that balances the cost of correcting errors due to changes in DCRs and the cost of execution without errors due to changes in DCRs.

4. A computer program, stored on a tangible 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 (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), the program comprising executable instructions that cause one or more computers to:

prepare a database query for execution on a database comprising 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), said execution based at least in part on application of a derived constraint rule (DCR) having the following form;

(PK=FK)→

CV2+C1≦

CV1<

CV2+C2

where C1 and C2 are constants, and “

→

”

means “

implies,”

to produce an execution plan;

take into account a frequency of execution errors due to changes in DCRs when preparing the execution plan, wherein when taking into account the frequency of errors due to changes in DCRs when preparing the execution plan, the executable instructions cause one or more computers to determine that a higher cost execution plan that risks no errors due to changes in DCRs is preferable; and

execute the plan.

5. The computer program of claim 4 wherein when taking into account the frequency of errors due to changes in DCRs when preparing the execution plan, the executable instructions cause one or more computers to:

determine that a higher cost execution plan that may require correction of an error due to changes in DCRs is preferable because it has a low cost if there is no error due to changes in DCRs.

6. The computer program of claim 4 wherein when taking into account the frequency of errors due to changes in DCRs when preparing the execution plan, the executable instructions cause one or more computers to:

selecting an execution plan that balances the cost of correcting errors due to changes in DCRs and the cost of execution without errors due to changes in DCRs.

7. 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 (CVO;

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), 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)→

CV2+C1≦

CV1≦

CV2+C2

where C1 and C2 are constants, and “

→

”

means “

implies,”

to produce an execution plan; and

take into account the frequency of execution errors due to changes in DCRs when preparing the execution plan, wherein when the optimizer takes into account the frequency of errors due to changes in DCRs when preparing the execution plan it determines that a higher cost execution plan that risks no errors due to changes in DCRs is preferable.

8. The system of claim 7 wherein when the optimizer takes into account the frequency of errors due to changes in DCRs when preparing the execution plan it:

determines that a higher cost execution plan that may require correction of an error due to changes in DCRs is preferable because it has a low cost if there is no error due to changes in DCRs.

9. The system of claim 7 wherein when the optimizer takes into account the frequency of errors due to changes in DCRs when preparing the execution plan it:

selects an execution plan that balances the cost of correcting errors due to changes in DCRs and the cost of execution without errors due to changes in DCRs.