INFERRING DIMENSIONAL METADATA FROM CONTENT OF A QUERY

• US 20150088856A1
• Filed: 09/20/2013
• Published: 03/26/2015
• Est. Priority Date: 09/20/2013
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: KOCIUBES, ADAM, HUNTER, JAMES
• Granted Patent: US 9,990,398 B2
• Time in Patent Office: Days
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24544 Join order optimisation

Abstract

Methods, computer systems, and stored instructions are described herein for inferring dimensional metadata from content of a query that references tables. A server analyzes the content of the query to determine which table(s) of the referenced tables could be fact tables and/or which table(s) of the referenced tables could be dimension tables. The server transforms the query to a transformed query that operates on a candidate fact table and candidate dimension table(s) of the referenced tables other than the candidate fact table. The transformed query, when executed, processes at least some data from dimension(s) using the candidate dimension table(s) before processing at least some data from the candidate fact table. Alternatively or additionally, the server generates an execution plan that operates on the candidate fact table and the candidate dimension table(s). The execution plan, when performed, processes data from dimension(s) using the candidate dimension table(s) before processing data from the candidate fact table.

Claims

1. A method comprising:

storing a query that references multiple tables;

analyzing content of the query to determine which of the multiple tables could be fact tables, wherein the multiple tables include one or more candidate fact tables that could be fact tables and one or more other tables that could not be fact tables;

based at least in part on determining which of the multiple tables could be fact tables, generating an execution plan for the query, the execution plan operating on a candidate fact table of the one or more candidate fact tables and one or more candidate dimension tables of the referenced multiple tables other than the candidate fact table;

wherein the execution plan, when performed, processes at least some data from at least one dimension using at least one of the one or more candidate dimension tables before processing at least some data from the candidate fact table;

wherein the method is performed by one or more computing devices.

2. The method of claim 1, wherein determining which of the multiple tables could be fact tables comprises determining which of the multiple tables are referenced by aggregation operators.

3. The method of claim 1, wherein determining which of the multiple tables could be fact tables does not account for sizes of the multiple tables.

4. The method of claim 3, wherein multiple execution plans are generated for the query, the multiple execution plans including one or more execution plans for each of the one or more candidate fact tables, the method further comprising selecting, from among the multiple execution plans, the execution plan that operates on the candidate fact table and the one or more candidate dimension tables based at least in part on a size of the candidate fact table.

5. The method of claim 1, further comprising identifying the one or more candidate dimension tables at least in part by determining, based at least in part on the content of the query, that the candidate fact table appears in one or more equijoins with the one or more candidate dimension tables.

6. A method comprising:

storing a query that references multiple tables;

analyzing content of the query to determine which of the multiple tables could be fact tables, wherein the multiple tables include one or more candidate fact tables that could be fact tables and one or more other tables that could not be fact tables;

based at least in part on determining which of the multiple tables could be fact tables, transforming the query to a transformed query that operates on a candidate fact table of the one or more candidate fact tables and one or more candidate dimension tables of the referenced multiple tables other than the candidate fact table;

wherein the transformed query, when executed, processes at least some data from at least one dimension using at least one of the one or more candidate dimension tables before processing at least some data from the candidate fact table;

wherein the method is performed by one or more computing devices.

7. The method of claim 6, wherein determining which of the multiple tables could be fact tables comprises determining which of the multiple tables are referenced by aggregation operators.

8. The method of claim 6, wherein determining which of the multiple tables could be fact tables does not account for sizes of the multiple tables.

9. The method of claim 8, wherein the query is transformed into multiple transformed queries, including one or more transformed queries for each of the one or more candidate fact tables, the method further comprising selecting, from among the multiple transformed queries, the transformed query that operates on the candidate fact table and the one or more candidate dimension tables based at least in part on a size of the candidate fact table.

10. The method of claim 6, further comprising identifying the one or more candidate dimension tables at least in part by determining, based at least in part on the content of the query, that the candidate fact table appears in one or more equijoins with the one or more candidate dimension tables.

11. The method of claim 6, further comprising:

determining that the query includes a particular fact key that is joined to a particular dimension key and that the query includes a group by operation that references the particular fact key; and

based at least in part on determining that the query includes the particular fact key that is joined to the particular dimension key and that the query includes a group by operation that references the particular fact key, replacing the particular fact key in the group by operation with the particular dimension key.

12. The method of claim 6, further comprising:

determining that the query includes a maximum or minimum operation that references a group by operation and a particular column that would otherwise qualify as a dimension; and

based at least in part on determining that the query includes the maximum or minimum operation that references the particular column that would otherwise qualify as the dimension, moving the particular column out of the maximum or minimum operation and into the group by operation.

13. The method of claim 6, further comprising:

determining that the query includes one or more redundant join operations; and

based at least in part on determining that the query includes the one or more redundant join operations, removing at least one of the one or more redundant join operations from the query.

14. One or more non-transitory computer-readable storage media storing instructions which, when executed, cause:

storing a query that references multiple tables;

analyzing content of the query to determine which of the multiple tables could be fact tables, wherein the multiple tables include one or more candidate fact tables that could be fact tables and one or more other tables that could not be fact tables;

based at least in part on determining which of the multiple tables could be fact tables, generating an execution plan for the query, the execution plan operating on a candidate fact table of the one or more candidate fact tables and one or more candidate dimension tables of the referenced multiple tables other than the candidate fact table;

wherein the execution plan, when performed, processes at least some data from at least one dimension using at least one of the one or more candidate dimension tables before processing at least some data from the candidate fact table.

15. The one or more non-transitory computer-readable storage media of claim 14, wherein the instructions, when executed, cause determining which of the multiple tables could be fact tables at least in part by determining which of the multiple tables are referenced by aggregation operators.

16. The one or more non-transitory computer-readable storage media of claim 14, wherein the instructions, when executed, cause determining which of the multiple tables could be fact tables without accounting for sizes of the multiple tables.

17. The one or more non-transitory computer-readable storage media of claim 16, wherein multiple execution plans are generated for the query, the multiple execution plans including one or more execution plans for each of the one or more candidate fact tables, and wherein the instructions, when executed, further cause selecting, from among the multiple execution plans, the execution plan that operates on the candidate fact table and the one or more candidate dimension tables based at least in part on a size of the candidate fact table.

18. The one or more non-transitory computer-readable storage media of claim 14, wherein the instructions, when executed, further cause identifying the one or more candidate dimension tables at least in part by determining, based at least in part on the content of the query, that the candidate fact table appears in one or more equijoins with the one or more candidate dimension tables.

19. One or more non-transitory computer-readable storage media storing instructions which, when executed, cause:

storing a query that references multiple tables;

analyzing content of the query to determine which of the multiple tables could be fact tables, wherein the multiple tables include one or more candidate fact tables that could be fact tables and one or more other tables that could not be fact tables;

based at least in part on determining which of the multiple tables could be fact tables, transforming the query to a transformed query that operates on a candidate fact table of the one or more candidate fact tables and one or more candidate dimension tables of the referenced multiple tables other than the candidate fact table;

wherein the transformed query, when executed, processes at least some data from at least one dimension using at least one of the one or more candidate dimension tables before processing at least some data from the candidate fact table.

20. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, cause determining which of the multiple tables could be fact tables at least in part by determining which of the multiple tables are referenced by aggregation operators.

21. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, cause determining which of the multiple tables could be fact tables without accounting for sizes of the multiple tables.

22. The one or more non-transitory computer-readable storage media of claim 21, wherein the query is transformed into multiple transformed queries, including one or more transformed queries for each of the one or more candidate fact tables, and wherein the instructions, when executed, further cause selecting, from among the multiple transformed queries, the transformed query that operates on the candidate fact table and the one or more candidate dimension tables based at least in part on a size of the candidate fact table.

23. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, further cause identifying the one or more candidate dimension tables at least in part by determining, based at least in part on the content of the query, that the candidate fact table appears in one or more equijoins with the one or more candidate dimension tables.

24. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, further cause:

determining that the query includes a particular fact key that is joined to a particular dimension key and that the query includes a group by operation that references the particular fact key; and

based at least in part on determining that the query includes the particular fact key that is joined to the particular dimension key and that the query includes a group by operation that references the particular fact key, replacing the particular fact key in the group by operation with the particular dimension key.

25. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, further cause:

determining that the query includes a maximum or minimum operation that references a group by operation and a particular column that would otherwise qualify as a dimension; and

based at least in part on determining that the query includes the maximum or minimum operation that references the particular column that would otherwise qualify as the dimension, moving the particular column out of the maximum or minimum operation and into the group by operation.

26. The one or more non-transitory computer-readable storage media of claim 19, wherein the instructions, when executed, further cause:

determining that the query includes one or more redundant join operations; and

based at least in part on determining that the query includes the one or more redundant join operations, removing at least one of the one or more redundant join operations from the query.