Synthesized predicate driven index selection for partitioned table
A system and method for receiving a query of a partitioned table, the query including a first index predicate associated with a first partition key column; determining that one or more of the query is missing a second index predicate and the first index predicate is unusable for index probing; responsive to determining that one or more of the query is missing the second index predicate and the first index predicate is unusable for index probing, generating one or more synthesized predicates used to process the query using an index scan.
1. A method comprising:
receiving, using one or more processors, a query querying a partitioned table;
determining, using the one or more processors, that the query includes a first index predicate comprising an operator unusable for index probing; and
responsive to determining that the query includes the first index predicate comprising the operator unusable for index probing, generating, using the one or more processors, a synthesized predicate corresponding to the first index predicate for the query, the synthesized predicate being used to process the query using an index scan.
2. The method of claim 1 , wherein:
the operator unusable for index probing in the first index predicate is one or more of a NOT EQUAL operator, a NOT IN operator, a NOT LIKE operator, and a NOT BETWEEN operator.
3. The method of claim 1 , wherein:
the synthesized predicate includes an operator usable for index probing.
4. The method of claim 1 , wherein:
the first index predicate is associated with a first partition key column of a list type in the partitioned table, the first partition key column containing a set of defined values in a partition of the partitioned table.
5. The method of claim 1 , wherein:
the first index predicate is associated with a first partition key column in the partitioned table; and
generating the synthesized predicate corresponding to the first index predicate includes:
obtaining one or more values in the first partition key column; and
generating the synthesized predicate corresponding to the first index predicate using the one or more values in the first partition key column.
6. The method of claim 5 , wherein:
the synthesized predicate corresponding to the first index predicate includes an IN operator and a set of the one or more values in the first partition key column.
7. The method of claim 1 , further comprising:
generating a query plan based on the synthesized predicate corresponding to the first index predicate; and
executing the query plan to process the query using the index scan.
8. The method of claim 7 , wherein:
the index scan includes one or more index probes; and
executing the query plan includes responsive to an index probe, evaluating retrieved data using the first index predicate.
9. The method of claim 7 , wherein:
the query includes a join condition associated with the partitioned table and another table; and
executing the query plan includes performing the index scan on the partitioned table to evaluate the join condition.
10. The method of claim 1 , further comprising:
determining that the query includes a second index predicate associated with a range and a third index predicate following the second index predicate; and
responsive to determining that the query includes the second index predicate associated with the range and the third index predicate following the second index predicate, generating another synthesized predicate corresponding to the second index predicate for the query, the another synthesized predicate being used to process the query using the index scan.
11. A system comprising:
a processor; and
a memory storing instructions that, when executed, cause the system to:
receive a query querying a partitioned table;
determine that the query includes a first index predicate comprising an operator unusable for index probing; and
responsive to determining that the query includes the first index predicate comprising the operator unusable for index probing, generate a synthesized predicate corresponding to the first index predicate for the query, the synthesized predicate being used to process the query using an index scan.
12. The system of claim 11 , wherein:
the operator unusable for index probing in the first index predicate is one or more of a NOT EQUAL operator, a NOT IN operator, a NOT LIKE operator, and a NOT BETWEEN operator.
13. The system of claim 11 , wherein:
the synthesized predicate includes an operator usable for index probing.
14. The system of claim 11 , wherein:
the first index predicate is associated with a first partition key column of a list type in the partitioned table, the first partition key column containing a set of defined values in a partition of the partitioned table.
15. The system of claim 11 , wherein:
the first index predicate is associated with a first partition key column in the partitioned table; and
generating the synthesized predicate corresponding to the first index predicate includes:
obtaining one or more values in the first partition key column; and
generating the synthesized predicate corresponding to the first index predicate using the one or more values in the first partition key column.
16. The system of claim 15 , wherein:
the synthesized predicate corresponding to the first index predicate includes an IN operator and a set of the one or more values in the first partition key column.
17. The system of claim 11 , wherein the instructions, when executed, cause the system to:
generate a query plan based on the synthesized predicate corresponding to the first index predicate; and
execute the query plan to process the query using the index scan.
18. The system of claim 17 , wherein:
the index scan includes one or more index probes; and
executing the query plan includes responsive to an index probe, evaluating retrieved data using the first index predicate.
19. The system of claim 17 , wherein:
the query includes a join condition associated with the partitioned table and another table; and
executing the query plan includes performing the index scan on the partitioned table to evaluate the join condition.
20. The system of claim 11 , wherein the instructions, when executed, cause the system to:
determine that the query includes a second index predicate associated with a range and a third index predicate following the second index predicate; and
responsive to determining that the query includes the second index predicate associated with the range and the third index predicate following the second index predicate, generate another synthesized predicate corresponding to the second index predicate for the query, the another synthesized predicate being used to process the query using the index scan.