Selecting from OR-expansion states of a query

• US 9,870,390 B2
• Filed: 02/18/2014
• Issued: 01/16/2018
• Est. Priority Date: 02/18/2014
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ahmed, Rafi, Vyas, Nirav, Zait, Mohamed
• Primary Examiner(s): Meng, Jau-Shya
• Application Number:
  US14/183,409
• Publication Number:
  US 20150234888A1
• Time in Patent Office: 1,428 Days
• Field of Search: None
• CPC Class Codes: G06F 16/2425 Iterative querying; Query formulation based on the results of a preceding query
• CPC Class Codes: G06F 16/24535 of sub-queries or views

Abstract

Methods, stored instructions, and computer systems are described for selecting an OR-expansion state of a query. A query processor accesses a certain query and a control parameter. The certain query comprises disjunctive predicates, and the control parameter indicates a certain procedure of a plurality of alternative stored procedures for expanding queries comprising disjunctive predicates into semantically equivalent candidate queries comprising combinations of subqueries. Based at least in part on the control parameter, the query processor selects the certain procedure to expand the certain query into one or more certain semantically equivalent candidate queries, each comprising a different combination of two or more subqueries. The query processor causes cost-based evaluation of subject queries, including the certain query and the one or more certain semantically equivalent candidate queries. The query processor then selects a query of the subject queries for execution or for further evaluation or transformation.

Claims

1. A method comprising:

accessing a query comprising a plurality of predicates that includes two or more predicates in disjunction;

selecting, based on a number of predicates in said two or more predicates in disjunction, from a plurality of alternative stored procedures, a certain procedure to expand the query into one or more semantically equivalent candidate queries;

wherein the plurality of alternative stored procedures each generate a different set of semantically equivalent candidate queries from an expansion state space of the query that includes all possible semantically equivalent queries comprising two or more conjunctive subqueries combined by one or more UNION ALL operators, each conjunctive subquery of the two or more conjunctive subqueries including one or more predicates of the plurality of predicates, in conjunction, said two or more conjunctive subqueries include a first conjunctive query that includes a particular predicate of said two or more predicates and a second conjunctive subquery that includes another of said two or more predicate but not said particular predicate;

based on the certain procedure selected, generating one or more semantically equivalent candidate queries;

performing a cost evaluation of the query and each of the one or more semantically equivalent candidate queries;

based at least in part on the cost evaluation, selecting a particular query of the one or more semantically equivalent candidate queries or the query;

causing execution of a transformed query that is based at least in part on the particular query;

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

2. The method of claim 1, wherein the plurality of alternative stored procedures include two or more of an exhaustive search procedure, a two-pass search procedure, a linear search procedure, or a greedy search procedure.

3. The method of claim 1, wherein the certain procedure comprises an exhaustive search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating all possible semantically equivalent queries comprising two or more conjunctive subqueries combined by one or more UNION ALL operators.

4. The method of claim 1, wherein the certain procedure comprises a two-pass search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating a single query that expands all of the two or more predicates in disjunction into subqueries combined by one or more UNION ALL operators.

5. The method of claim 1, wherein the certain procedure comprises a linear search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises expanding consecutive disjunctive predicates into conjunctive subqueries combined by one or more UNION ALL operators.

6. The method of claim 1, wherein the certain procedure comprises a linear search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating:

a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators, and

one or more other queries that are based at least in part on fused consecutive conjunctive subqueries of the first query combined by one or more UNION ALL operators.

7. The method of claim 1,

wherein the certain procedure comprises a greedy search procedure,

wherein generating the one or more semantically equivalent candidate queries comprises:

generating a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators,

generating a second query that is based at least in part on fused consecutive conjunctive subqueries of the first query combined by one or more UNION ALL operators, and

based at least in part on determining that the second query has an estimated cost that is less than the first query, generating one or more other queries that are based at least in part on fused consecutive conjunctive subqueries of the second query.

8. The method of claim 1,

wherein the certain procedure comprises a greedy search procedure,

wherein generating the one or more semantically equivalent candidate queries comprises:

generating a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators, and

based at least in part on determining that the first query has an estimated cost that is less than an estimated cost of the query, generating no other queries.

9. The method of claim 1, further comprising converting the query to disjunctive normal form before applying the certain procedure.

10. The method of claim 1,

wherein performing the cost evaluation of the query and each of the one or more semantically equivalent candidate queries comprises estimating a cost of the query and estimating a cost for each of the one or more semantically equivalent candidate queries;

wherein the particular query selected is a lowest cost query of the query and the one or more semantically equivalent candidate queries.

11. The method of claim 1, further comprising:

generating a matrix based on the query, wherein the matrix associates individual predicates of the plurality of predicates of the query with corresponding conjunctive subqueries in which the individual predicates occur; and

using the matrix when applying the certain procedure to expand the query into the one or more semantically equivalent candidate queries.

12. The method of claim 1, further comprising:

accessing a control parameter that indicates a particular procedure of the plurality of alternative stored procedures;

wherein the selecting the certain procedure is based at least in part on the particular procedure.

13. The method of claim 12, wherein the control parameter is a session parameter for a session in which the query is received.

14. The method of claim 12, wherein the control parameter is a hint that is embedded in an expression comprising the query.

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

accessing a query comprising a plurality of predicates that includes two or more predicates, in disjunction;

selecting, based on a number of predicates in said two or more predicates in disjunction, from a plurality of alternative stored procedures, a certain procedure to expand the query into one or more semantically equivalent candidate queries;

wherein the plurality of alternative stored procedures each generate a different set of semantically equivalent candidate queries from an expansion state space of the query that includes all possible semantically equivalent queries comprising two or more conjunctive subqueries combined by one or more UNION ALL operators, each conjunctive subquery of the two or more conjunctive subqueries including one or more predicates of the plurality of predicates, in conjunction,

said two or more conjunctive subqueries include a first conjunctive query that includes a particular predicate of said two or more predicates and a second conjunctive subquery that includes another of said two or more predicate but not said particular predicate;

based on the certain procedure selected, generating one or more semantically equivalent candidate queries;

performing a cost evaluation of the query and each of the one or more semantically equivalent candidate queries;

based at least in part on the cost evaluation, selecting a particular query of the one or more semantically equivalent candidate queries or the query;

causing execution of a transformed query that is based at least in part on the particular query.

16. The one or more non-transitory computer-readable storage media of claim 15, wherein the plurality of alternative procedures include two or more of an exhaustive search procedure, a two-pass search procedure, a linear search procedure, or a greedy search procedure.

17. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises an exhaustive search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating all possible semantically equivalent queries comprising two or more conjunctive subqueries combined by one or more UNION ALL operators.

18. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises a two-pass search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating a single query that expands all of the two or more predicates in disjunction into subqueries combined by one or more UNION ALL operators.

19. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises a linear search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises expanding consecutive disjunctive predicates into conjunctive subqueries combined by one or more UNION ALL operators.

20. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises a linear search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises generating:

a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators, and

one or more other queries that are based at least in part on fused consecutive conjunctive subqueries of the first query combined by one or more UNION ALL operators.

21. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises a greedy search procedure, wherein generating the one or more semantically equivalent candidate queries comprises:

generating a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators,

generating a second query that is based at least in part on fused consecutive conjunctive subqueries of the first query combined by one or more UNION ALL operators, and

based at least in part on determining that the second query has an estimated cost that is less than the first query, generating one or more other queries that are based at least in part on fused consecutive conjunctive subqueries of the second query.

22. The one or more non-transitory computer-readable storage media of claim 15, wherein the certain procedure comprises a greedy search procedure, and wherein generating the one or more semantically equivalent candidate queries comprises:

generating a first query that expands all of the two or more predicates in disjunction into conjunctive subqueries combined by one or more UNION ALL operators, and

based at least in part on determining that the first query has an estimated cost that is less than an estimated cost of the query, generating no other queries.

23. The one or more non-transitory computer-readable storage media of claim 15, wherein the control parameter is a session parameter for a session in which the query is received.

24. The one or more non-transitory computer-readable storage media of claim 23, wherein the control parameter is a hint that is embedded in an expression comprising the query.

25. The one or more non-transitory computer-readable storage media of claim 15, wherein the instructions, when executed, further cause converting the query to disjunctive normal form before applying the certain procedure.

26. The one or more non-transitory computer-readable storage media of claim 15,

wherein performing the cost evaluation of the query and each of the one or more semantically equivalent candidate queries comprises:

estimating a cost of the query and estimating a cost for each of the one or more semantically equivalent candidate queries;

wherein the particular query selected is a lowest cost query of the query and the one or more semantically equivalent candidate queries.

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

generating a matrix based on the query, wherein the matrix associates individual predicates of the plurality of predicates of the query with corresponding conjunctive subqueries in which the individual predicates occur; and

using the matrix when applying the certain procedure to expand the query into the one or more semantically equivalent candidate queries.

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

accessing a control parameter that indicates a particular procedure of the plurality of alternative stored procedures;

wherein the selecting the certain procedure is based at least in part on the particular procedure.