Synthesized Predicate Index Selection for Partitioned Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data access solutions fail to efficiently handle certain query scenarios, such as queries with missing or unusable predicates, often resorting to less-efficient table scans instead of index scans.
Innovation Solution
A system and method that generate synthesized predicates to enable index scans for queries missing or with unusable predicates, thereby allowing for more efficient data access by processing queries using index scans instead of defaulting to table scans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a table scan is used to access data, then all queries can be executed, but query performance deteriorates significantly on large datasets
Solution Approach 1:
The system changes the parameter of predicate usability by transforming unusable predicates (NOT EQUAL, NOT IN, etc.) into equivalent forms that are index-probable. For example, converting `column != value` into `column IN (sublist of values)` allows the query to utilize index structures effectively, thereby improving query performance while maintaining execution capability.
Solution Approach 2:
The query optimizer acts as an intermediary between the user's original query and the index structure. It analyzes the predicates, determines which are unusable for index probing, and synthesizes alternative predicates that can leverage the index. This intermediary transformation enables efficient index scans even when the original query contains unusable predicates.
2Productivity
If synthesized predicates are generated to enable index scans, then query efficiency improves, but system complexity increases
Solution Approach 1:
The query optimizer performs preliminary analysis of the query predicates before executing the query. It pre-determines which predicates are unusable for index probing and prepares synthesized alternatives in advance. This preliminary action reduces the computational overhead during query execution, as the transformation logic is performed before the actual data access.
Solution Approach 2:
The system serves itself by automatically detecting unusable predicates and generating appropriate synthesized predicates without external intervention. The query optimizer inherently knows which predicates can and cannot be used for index probing, and autonomously transforms them as needed, eliminating the need for manual configuration or complex external processing.
3Productivity
If index scans are used instead of table scans, then data access speed increases, but queries with certain predicate types cannot be executed
Solution Approach 1:
The system changes the parameter of predicate form to make it compatible with index scanning. By transforming predicates like `NOT EQUAL`, `NOT IN`, `LIKE`, and `BETWEEN` into equivalent forms that support index probing, the system maintains versatility in handling various query types while enabling efficient index scan execution.
Solution Approach 2:
The system converts the harmful effect of unusable predicates (which would force table scans) into a benefit by synthesizing alternative predicates that enable index scans. For example, transforming `column != value` into `column IN (sublist)` converts a predicate that would normally prevent index usage into one that enables efficient index-based data access.
Data Source
AI summary
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.