Synthesized predicate driven index selection for partitioned table
A system and method for receiving, using one or more processors, a query including an index predicate; determining, using the one or more processors, that the query includes a default to table scan issue; and responsive to determining that the query includes a default to table scan issue, generating, using the one or more processors, 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 including an index predicate;
determining, using the one or more processors, that the query includes a default to table scan issue; and
responsive to determining that the query includes a default to table scan issue, generating, using the one or more processors, one or more synthesized predicates used to process the query using an index scan.
2. The method of claim 1 , the method further comprising:
performing an index scan on a table using the one or more synthesized predicates.
3. The method of claim 2 , wherein the table is partitioned.
4. The method of claim 1 , wherein the default to table scan issue includes the query missing a prefix index predicate, wherein the prefix index predicate precedes the index predicate present in the query.
5. The method of claim 4 , wherein the prefix index predicate includes a leading predicate component of the index.
6. The method of claim 1 , wherein the default to table scan issue includes the index predicate being unusable for an index probing without the one or more synthesized predicates.
7. The method of claim 6 , wherein the index predicate is associated with an operator that makes the index predicate unusable for index probing.
8. The method of claim 7 , wherein the operator includes one or more of < >, NOT IN, NOT LIKE, and NOT BETWEEN.
9. The method of claim 6 , wherein the index predicate is associated with an operator that makes a subsequent index predicate unusable for index probing.
10. The method of claim 9 , wherein the index predicate is a leading or middle index predicate and the index predicate is associated with a range operator.
11. A system comprising:
one or more processors; and
a memory storing instructions that, when executed by the one or more processors, cause the system to:
receive a query including an index predicate;
determine that the query includes a default to table scan issue; and
responsive to determining that the query includes a default to table scan issue, generate one or more synthesized predicates used to process the query using an index scan.
12. The system of claim 11 , wherein the instructions, when executed by the one or more processors, further cause the system to:
perform an index scan on a table using the one or more synthesized predicates.
13. The system of claim 12 , wherein the table is partitioned.
14. The system of claim 11 , wherein the default to table scan issue includes the query missing a prefix index predicate, wherein the prefix index predicate precedes the index predicate present in the query.
15. The system of claim 14 , wherein the prefix index predicate includes a leading predicate component of the index.
16. The system of claim 11 , wherein the default to table scan issue includes the index predicate being unusable for an index probing without the one or more synthesized predicates.
17. The system of claim 16 , wherein the index predicate is associated with an operator that makes the index predicate unusable for index probing.
18. The system of claim 17 , wherein the operator includes one or more of, < >, NOT IN, NOT LIKE, and NOT BETWEEN.
19. The system of claim 16 , wherein the index predicate is associated with an operator that makes a subsequent index predicate unusable for index probing.
20. The system of claim 19 , wherein the index predicate is a leading or middle index predicate and the index predicate is associated with a range operator.