OPTIMIZING ACCESS TO A DATABASE BY UTILIZING A STAR JOIN

• US 20070083490A1
• Filed: 09/27/2006
• Published: 04/12/2007
• Est. Priority Date: 09/29/2005
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Au, Grace, Ramesh, Bhashyam, Chen, Haiyan
• Granted Patent: US 7,814,094 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24544 Join order optimisation

Abstract

A database query is optimized for a star database schema that includes at least one fact table and at least one dimension table, where the database query includes an IN-List condition. During optimization, the IN-List is transformed into a relation and the relation is joined with the dimension table to produce a join result. The join result is then joined to the fact table.

Claims

1. A method for use in optimizing a database query for a star database schema including at least one fact table and at least one dimension table, in which the database query includes an IN-List condition, where the method includes:

transforming the IN-List into a relation;

joining the relation with the dimension table to produce a join result; and

joining the join result to the fact table.

2. The method of claim 1 further including:

determining whether an IN-List exists within a WHERE clause.

3. The method of claim 1 further including:

determining whether an IN-List exists within an ON clause.

4. The method of claim 1, further including:

evaluating, for each of multiple different join paths, a cost associated with joining the relation with the dimension table, and

selecting a lowest cost path from the different join paths.

5. The method of claim 1 further including:

evaluating, for each of multiple different join paths, a cost associated with joining the join result with the fact table, and

selecting a lowest cost path from the different join paths.

6. The method of claim 1 further including:

evaluating, for each of multiple different join paths that include plans that use IN-List as a single table condition and plans that use IN-List as dimension table, a cost associated with the join path, and

selecting a lowest cost path from the different join paths.

7. The method of claim 1 further including:

applying an index when joining the join result of to the fact table.

8. The method of claim 6, where the index is a primary index of the fact table.

9. The method of claim 6, where the index is a secondary index of the fact table.

10. The method of claim 6, where the index is a primary index of a join index that at least partially covers the fact table.

11. The method of claim 6, where the index is a secondary index of a join index that at least partially covers the fact table.

12. The method of claim 1, further including recognizing single column IN-Lists.

13. The method of claim 11, further including recognizing IN-predicate single column IN-Lists and disjuncted single column conditions residing on the same column.

14. The method of claim 1, further including recognizing multiple-column IN-Lists.

15. The method of claim 14 further including recognizing at least one of:

a single-column IN-List, a list of disjuncted compound conditions residing in a set of columns, and a combination of one or more single column IN-Lists or multi-column IN-Lists.

16. A database system for accessing a database, the database system including:

a parallel processing system including one or more nodes;

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

multiple virtual processes, each of the one or more CPUs providing access to one or more of the virtual processes, each process configured to manage data stored in one of a plurality of data-storage facilities;

an optimizer for optimizing a database query for a star database schema including at least one fact table and at least one dimension table, in which the database query includes an IN-List condition, the optimizer configured to;

transform the IN-List into a relation;

join the IN-List relation with the dimension table to produce a join result; and

joining the join result to the fact table.

17. The database system of claim 16, where the optimizer is configured to determine whether an IN-List exists within a WHERE clause.

18. The database system of claim 16, where the optimizer is configured to determine whether an IN-List exists within an ON clause.

19. The database system of claim 16, where the optimizer is configured to:

evaluate, for each of multiple different join paths, a cost associated with joining the IN-List

relation with the dimension table; and

select a lowest cost path from the different join paths.

20. The database system of claim 16, where the optimizer is configured to:

evaluate, for each of multiple different join paths, a cost associated with joining the join result to the fact table; and

select a lowest cost path from the different join paths.

21. The database system of claim 16, where the optimizer is configured to:

evaluate, for each of multiple different join paths including plans that use IN-List as single table condition and plans that use IN-List as dimension table IN-List star join plan, a cost associated with each join path; and

select a lowest cost path from the different join paths.

22. The database system of claim 16, where the optimizer is configured to:

identify an index for use in joining the join result to the fact table; and

apply the index when joining the join result to the fact table.

23. The database system of claim 16 in which the index is a primary index of the fact table.

24. The database system of claim 16 in which the index is a secondary index of the fact table.

25. The database system of claim 16 in which the index is a primary index of a join index that at least partially covers the fact table.

26. The database system of claim 16 in which the index is a primary index of a join index that at least partially covers the fact table.

27. The database system of claim 16, where the optimizer is configured to recognize single column IN-Lists.

28. The database system of claim 16, where the optimizer is configured to recognize IN-predicate single column IN-Lists and disjuncted single column conditions residing on the same column.

29. The database system of claim 16, where the optimizer is configured to recognize multiple-column IN-Lists.

30. The database system of claim 16, where the optimizer is configured to recognize any of:

a single-column IN-List, a list of disjuncted compound conditions residing in the same set of columns, and a combination of one or more single column IN-Lists or multi-column IN-Lists.