Transforming a single-table join predicate into a pseudo-join predicate

• US 8,554,761 B1
• Filed: 05/25/2012
• Issued: 10/08/2013
• Est. Priority Date: 05/25/2012
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ahmed, Rafi
• Primary Examiner(s): LU, KUEN S
• Application Number:
  US13/481,722
• Time in Patent Office: 501 Days
• Field of Search: 707/714, 707/802
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24537 of operators

Abstract

Methods, computing devices, and computer-readable media are provided for transforming an initial query with a single-table outer join predicate to a second, semantically equivalent query that includes a multi-table predicate instead of the single-table outer join predicate. Because the second query does not include the single-table outer join predicate, the second query may be available for further transformations or optimization techniques, may produce a more efficient or otherwise different execution plan, or may be executed more efficiently or differently. The single-table outer join predicate may be in a full outer join ON clause of the initial query or nested in a left outer-joined LATERAL VIEW of the initial query. The multi-table predicate may be in a WHERE clause of the second query. Unlike the initial query, the second query allows use of a temporary organization of data from multiple tables for evaluation of multiple rows of the left outer join.

Claims

1. A method comprising:

transforming a first query to a transformed query;

wherein the first query includes;

a first outer join of a first database object and a second database object, and

a first join predicate that specifies a first condition of the first database object for the first outer join, wherein the first join predicate does not reference the second database object;

wherein the transformed query does not include the first join predicate but does include;

a second predicate that specifies a second condition of the first database object, wherein the second predicate references the second database object;

wherein the transformed query is semantically equivalent to the first query;

causing execution of the transformed query instead of the first query;

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

2. The method of claim 1, wherein the first outer join is a full outer join of the first database object and the second database object, and wherein the second predicate is for a left outer join of the first database object and the second database object.

3. The method of claim 1, wherein the first outer join is a left outer join on a lateral view that references the first database object and the second database object;

wherein the first join predicate is nested in the lateral view; and

wherein the transformed query does not include the lateral view.

4. The method of claim 1, wherein the first join predicate is in an ON clause of the first outer join.

5. The method of claim 1, wherein the second predicate is in a WHERE clause of the transformed query.

6. The method of claim 1, wherein the first query includes one or more filter predicates outside the first outer join;

wherein the one or more filter predicates reference a column;

wherein the transformed query includes the one or more filter predicates on a same level as the second predicate;

wherein the one or more filter predicates, if evaluated in the first query or in the transformed query, would eliminate, from a set of rows, rows for which the referenced column is null.

7. The method of claim 1, wherein the first join predicate references a column, wherein the first join predicate, if evaluated for the first outer join in the first query, would preserve, from a set of rows, rows for which the referenced column is null;

wherein the first join predicate, if evaluated as a filter predicate for the first outer join in the first query, would eliminate, from a set of rows, rows for which the referenced column is null; and

wherein the second predicate, if evaluated in the transformed query, would preserve, from a set of rows, rows for which the referenced column is null.

8. The method of claim 1, wherein two or more predicates in the first query, including the first join predicate, reference the first database object but not the second database object, and wherein two or more corresponding predicates in the transformed query reference the first database object and the second database object.

9. The method of claim 1, wherein the second condition is based on whether a value of the second database object is null or not null.

10. The method of claim 1, wherein the first query includes a third predicate that references the first database object and the second database object, wherein the first predicate is in an ON clause of the first query, wherein the transformed query includes a fourth predicate in a WHERE clause of the transformed query, and wherein the fourth predicate is for a left outer join of the transformed query.

11. The method of claim 1, wherein the second predicate is for a left outer join of the first database object and the second database object, wherein the transformed query, if executed, would cause a temporary organization of data from the first database object and the second database object and utilization of the temporary organization of data for evaluation of multiple rows of the left outer join.

12. One or more non-transitory computer-readable storage media storing instructions which, when executed by one or more computing devices, cause:

transforming a first query to a transformed query;

wherein the first query includes;

a first outer join of a first database object and a second database object, and

a first join predicate that specifies a first condition of the first database object for the first outer join, wherein the first join predicate does not reference the second database object;

wherein the transformed query does not include the first join predicate but does include;

a second predicate that specifies a second condition of the first database object, wherein the second predicate references the second database object;

wherein the transformed query is semantically equivalent to the first query;

causing execution of the transformed query instead of the first query.

13. The one or more non-transitory computer-readable storage media of claim 12, wherein the first outer join is a full outer join of the first database object and the second database object, and wherein the second predicate is for a left outer join of the first database object and the second database object.

14. The one or more non-transitory computer-readable storage media of claim 12, wherein the first outer join is a left outer join on a lateral view that references the first database object and the second database object;

wherein the first join predicate is nested in the lateral view; and

wherein the transformed query does not include the lateral view.

15. The one or more non-transitory computer-readable storage media of claim 12, wherein the first join predicate is in an ON clause of the first outer join.

16. The one or more non-transitory computer-readable storage media of claim 12, wherein the second predicate is in a WHERE clause of the transformed query.

17. The one or more non-transitory computer-readable storage media of claim 12, wherein the first query includes one or more filter predicates outside the first outer join;

wherein the one or more filter predicates reference a column;

wherein the transformed query includes the one or more filter predicates on a same level as the second predicate;

wherein the one or more filter predicates, if evaluated in the first query or in the transformed query, would eliminate, from a set of rows, rows for which the referenced column is null.

18. The one or more non-transitory computer-readable storage media of claim 12, wherein the first join predicate references a column, wherein the first join predicate, if evaluated for the first outer join in the first query, would preserve, from a set of rows, rows for which the referenced column is null;

wherein the first join predicate, if evaluated as a filter predicate for the first outer join in the first query, would eliminate, from a set of rows, rows for which the referenced column is null; and

wherein the second predicate, if evaluated in the transformed query, would preserve, from a set of rows, rows for which the referenced column is null.

19. The one or more non-transitory computer-readable storage media of claim 12, wherein two or more predicates in the first query, including the first join predicate, reference the first database object but not the second database object, and wherein two or more corresponding predicates in the transformed query reference the first database object and the second database object.

20. The one or more non-transitory computer-readable storage media of claim 12, wherein the second condition is based on whether a value of the second database object is null or not null.

21. The one or more non-transitory computer-readable storage media of claim 12, wherein the first query includes a third predicate that references the first database object and the second database object, wherein the first predicate is in an ON clause of the first query, wherein the transformed query includes a fourth predicate in a WHERE clause of the transformed query, and wherein the fourth predicate is for a left outer join of the transformed query.

22. The one or more non-transitory computer-readable storage media of claim 12, wherein the second predicate is for a left outer join of the first database object and the second database object, wherein the transformed query, if executed, would cause a temporary organization of data from the first database object and the second database object and utilization of the temporary organization of data for evaluation of multiple rows of the left outer join.

23. A method comprising:

transforming a first query to a transformed query;

wherein the first query includes;

a first outer join of a first database object and a second database object, and

a first join predicate that specifies a first condition of the first database object for the first outer join, wherein the first join predicate does not reference the second database object;

wherein the transformed query does not include the first join predicate but does include;

a second predicate that specifies a second condition of the first database object, wherein the second predicate references the second database object;

wherein the transformed query is semantically equivalent to the first query;

generating an execution plan for executing the transformed query instead of the first query;

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

24. The method of claim 23, wherein the first outer join is a full outer join of the first database object and the second database object, and wherein the second predicate is for a left outer join of the first database object and the second database object.

25. The method of claim 23, wherein the first outer join is a left outer join on a lateral view that references the first database object and the second database object;

wherein the first join predicate is nested in the lateral view; and

wherein the transformed query does not include the lateral view.

26. The method of claim 23, wherein the first join predicate is in an ON clause of the first outer join.

27. The method of claim 23, wherein the second predicate is in a WHERE clause of the transformed query.

28. The method of claim 23, wherein the first query includes one or more filter predicates outside the first outer join;

wherein the one or more filter predicates reference a column;

wherein the transformed query includes the one or more filter predicates on a same level as the second predicate;

wherein the one or more filter predicates, if evaluated in the first query or in the transformed query, would eliminate, from a set of rows, rows for which the referenced column is null.

29. The method of claim 23, wherein the first join predicate references a column, wherein the first join predicate, if evaluated for the first outer join in the first query, would preserve, from a set of rows, rows for which the referenced column is null;

wherein the first join predicate, if evaluated as a filter predicate for the first outer join in the first query, would eliminate, from a set of rows, rows for which the referenced column is null; and

wherein the second predicate, if evaluated in the transformed query, would preserve, from a set of rows, rows for which the referenced column is null.

30. The method of claim 23, wherein two or more predicates in the first query, including the first join predicate, reference the first database object but not the second database object, and wherein two or more corresponding predicates in the transformed query reference the first database object and the second database object.

31. The method of claim 23, wherein the second condition is based on whether a value of the second database object is null or not null.

32. The method of claim 23, wherein the first query includes a third predicate that references the first database object and the second database object, wherein the first predicate is in an ON clause of the first query, wherein the transformed query includes a fourth predicate in a WHERE clause of the transformed query, and wherein the fourth predicate is for a left outer join of the transformed query.

33. The method of claim 23, wherein the second predicate is for a left outer join of the first database object and the second database object, wherein the transformed query, if executed, would cause a temporary organization of data from the first database object and the second database object and utilization of the temporary organization of data for evaluation of multiple rows of the left outer join.

34. One or more non-transitory computer-readable storage media storing instructions which, when executed by one or more computing devices, cause:

transforming a first query to a transformed query;

wherein the first query includes;

a first outer join of a first database object and a second database object, and

a first join predicate that specifies a first condition of the first database object for the first outer join, wherein the first join predicate does not reference the second database object;

wherein the transformed query does not include the first join predicate but does include;

a second predicate that specifies a second condition of the first database object, wherein the second predicate references the second database object;

wherein the transformed query is semantically equivalent to the first query;

generating an execution plan for executing the transformed query instead of the first query.

35. The one or more non-transitory computer-readable storage media of claim 34, wherein the first outer join is a full outer join of the first database object and the second database object, and wherein the second predicate is for a left outer join of the first database object and the second database object.

36. The one or more non-transitory computer-readable storage media of claim 34, wherein the first outer join is a left outer join on a lateral view that references the first database object and the second database object;

wherein the first join predicate is nested in the lateral view; and

wherein the transformed query does not include the lateral view.

37. The one or more non-transitory computer-readable storage media of claim 34, wherein the first join predicate is in an ON clause of the first outer join.

38. The one or more non-transitory computer-readable storage media of claim 34, wherein the second predicate is in a WHERE clause of the transformed query.

39. The one or more non-transitory computer-readable storage media of claim 34, wherein the first query includes one or more filter predicates outside the first outer join;

wherein the one or more filter predicates reference a column;

wherein the transformed query includes the one or more filter predicates on a same level as the second predicate;

wherein the one or more filter predicates, if evaluated in the first query or in the transformed query, would eliminate, from a set of rows, rows for which the referenced column is null.

40. The one or more non-transitory computer-readable storage media of claim 34, wherein the first join predicate references a column, wherein the first join predicate, if evaluated for the first outer join in the first query, would preserve, from a set of rows, rows for which the referenced column is null;

wherein the first join predicate, if evaluated as a filter predicate for the first outer join in the first query, would eliminate, from a set of rows, rows for which the referenced column is null; and

wherein the second predicate, if evaluated in the transformed query, would preserve, from a set of rows, rows for which the referenced column is null.

41. The one or more non-transitory computer-readable storage media of claim 34, wherein two or more predicates in the first query, including the first join predicate, reference the first database object but not the second database object, and wherein two or more corresponding predicates in the transformed query reference the first database object and the second database object.

42. The one or more non-transitory computer-readable storage media of claim 34, wherein the second condition is based on whether a value of the second database object is null or not null.

43. The one or more non-transitory computer-readable storage media of claim 34, wherein the first query includes a third predicate that references the first database object and the second database object, wherein the first predicate is in an ON clause of the first query, wherein the transformed query includes a fourth predicate in a WHERE clause of the transformed query, and wherein the fourth predicate is for a left outer join of the transformed query.

44. The one or more non-transitory computer-readable storage media of claim 34, wherein the second predicate is for a left outer join of the first database object and the second database object, wherein the transformed query, if executed, would cause a temporary organization of data from the first database object and the second database object and utilization of the temporary organization of data for evaluation of multiple rows of the left outer join.