Deferred maintenance of sparse join indexes

• US 8,032,503 B2
• Filed: 08/05/2008
• Issued: 10/04/2011
• Est. Priority Date: 08/05/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Bouloy, Carlos, Au, Grace, Gui, Hong
• Primary Examiner(s): TIMBLIN, ROBERT M
• Application Number:
  US12/186,217
• Publication Number:
  US 20100036886A1
• Time in Patent Office: 1,155 Days
• Field of Search: 707/639, 707/696, 707/695, 707713-715, 707/741
• US Class Current: 707/696
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

A system and method include defining a snapshot join index using a sparse condition in a join index definition. A new sparse condition of the snapshot join index is compared with an old sparse condition. Rows in a base table are identified as a function of the comparing, and the join index table is updated using the identified rows.

Claims

1. A method comprising:

defining a snapshot join index from a join index table using a sparse condition in a join index definition;

comparing a new sparse condition of the snapshot join index with an old sparse condition, wherein the comparing identifies an incremental delta condition between the new sparse condition and the old sparse condition;

identifying rows in a base table as a function of the comparing; and

updating rows in the join index table as a function of the identified rows in the base table.

2. The method of claim 1 wherein updating is performed in a selected batch window to minimize impact to other activities in a data warehouse that provides the snapshot join index.

3. The method of claim 1 wherein the join index table is an aggregate join index table.

4. The method of claim 3 wherein identifying rows further comprises calculating aggregates.

5. The method of claim 1 wherein a syntax for defining the new sparse condition comprises:

ALTER JOIN INDEX ji_name CHANGE FROM

WHERE old_sparse condition

TO

WHERE new_sparse condition.

6. The method of claim 1 wherein identifying rows in a base table comprises generating spools that contain old rows to be deleted and new rows to be inserted.

7. The method of claim 6 wherein updating the join index table comprises updating using the rows identified in the spools to merge delete from/merge into the join index table.

8. The method of claim 1 wherein the join index table comprises multiple tables.

9. The method of claim 8 wherein the join index is changed by one or more of:

purging old historical data from the join index;

expanding the join index to include more historical data;

expanding the join index to include more recent data; and

purging recent data from the join index.

10. A tangible non-transitory computer readable medium having instructions stored thereon to cause a computer to implement a method comprising:

defining a snapshot join index from a join index table using a sparse condition in a join index definition;

comparing a new sparse condition of the snapshot join index with an old sparse condition, wherein the comparing identifies an incremental delta condition between the new sparse condition and the old sparse condition;

identifying rows in a base table as a function of the comparing; and

updating rows in the join index table as a function of the identified rows in the base table.

11. The computer readable medium of claim 10 wherein updating is performed in a selected batch window to minimize impact to other activities in a data warehouse that provides the snapshot join index.

12. The computer readable medium of claim 10 wherein the join index table is an aggregate join index table.

13. The computer readable medium of claim 12 wherein identifying rows further comprises calculating aggregates.

14. The computer readable medium of claim 10 wherein a syntax for defining the new sparse condition comprises:

ALTER JOIN INDEX ji_name CHANGE FROM

WHERE old_sparse condition

TO

WHERE new_sparse condition.

15. The computer readable medium of claim 10 wherein identifying rows in a base table comprises generating spools that contain old rows to be deleted and new rows to be inserted, and wherein updating the join index table comprises updating using the rows identified in the spools to merge delete from/merge into the join index table.

16. The computer readable medium of claim 10 wherein the join index table comprises multiple tables, wherein the join index is changed by one or more of:

purging old historical data from the join index;

expanding the join index to include more historical data;

expanding the join index to include more recent data; and

purging recent data from the join index.

17. A system comprising:

one or more processing units;

one or more data storage units coupled to the one or more processors;

one or more optimizers executing on the one or more processing units that are configured to;

define a snapshot join index from a join index table using a sparse condition in a join index definition;

compare a new sparse condition of the snapshot join index with an old sparse condition, wherein the comparing identifies an incremental delta condition between the new sparse condition and the old sparse condition;

identify rows in a base table as a function of the compare; and

update rows in the join index table as a function of the identified rows in the base table.

18. The system of claim 17 wherein the join index table is an aggregate join index table and wherein identifying rows further comprises calculating aggregates.

19. The system of claim 17 wherein a syntax for defining the new sparse condition comprises:

ALTER JOIN INDEX ji_name CHANGE FROM

WHERE old_sparse condition

TO

WHERE new_sparse condition.

20. The system of claim 17 wherein identifying rows in a base table comprises generating spools that contain old rows to be deleted and new rows to be inserted, and wherein updating the join index table comprises updating using the rows identified in the spools to merge delete from/merge into the join index table.

21. The system of claim 17 wherein the join index table comprises multiple tables, wherein the join index is changed by one or more of:

purging old historical data from the join index;

expanding the join index to include more historical data;

expanding the join index to include more recent data; and

purging recent data from the join index.