Database LIKE Peeking for Runtime Index Path Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in handling LIKE predicates, particularly when they are index-unfriendly, as they do not support arrays of patterns and typecasting, and the cost model inaccurately estimates execution costs, leading to suboptimal query planning.
Innovation Solution
Implement a database system that generates query plans with both index-friendly and index-unfriendly paths for LIKE predicates, allowing for typecasting and evaluating execution costs based on the index-friendly path, enabling efficient execution by determining prefix constants at runtime.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the database system uses conventional cost modeling for LIKE predicates, then the query planning is simple, but the execution cost estimation is inaccurate leading to suboptimal query plans
Solution Approach 1:
The patent segments the query planning process into distinct phases: initial cost estimation using conventional models, then refinement through actual execution time measurement. The cost model is divided into predictive components (schema-based estimation) and empirical components (runtime measurement), allowing accurate cost estimation without overwhelming complexity in the initial planning phase.
Solution Approach 2:
The system performs preliminary cost estimation using schema information and historical data before query execution, then uses the actual execution time to refine future cost estimates. This preliminary action allows the system to prepare accurate cost models in advance without adding complexity to the real-time query planning process.
2Adaptability or versatility
If the database system supports only conventional LIKE predicates, then the implementation is simple, but it cannot handle arrays of patterns and typecasting
Solution Approach 1:
The patent implements a universal LIKE predicate processing framework that handles multiple data types (text, numeric, date) and multiple pattern formats (single patterns, arrays of patterns) through a unified code path. The system uses typecasting mechanisms to convert different input types into a common processing format, allowing the same core logic to serve multiple functions without requiring separate implementations for each case.
Solution Approach 2:
The system changes the parameters of the LIKE predicate processing based on input type and pattern format. When an array of patterns is provided, the system transforms it into multiple individual pattern matches. When typecasting is needed, the system adjusts the data type parameters before processing. These parameter changes allow flexible handling of complex queries while maintaining a relatively simple core processing mechanism.
3Productivity
If the database system chooses index-unfriendly paths for LIKE predicates, then the implementation is straightforward, but the query execution time increases
Solution Approach 1:
The patent implements dynamic query plan selection that adapts to the specific LIKE predicate being executed. The system evaluates whether an index-friendly or index-unfriendly path is more suitable based on the predicate characteristics (pattern format, data type, selectivity) and chooses the optimal path dynamically. This dynamic approach allows the system to achieve high execution efficiency without requiring a fixed complex query plan structure.
Solution Approach 2:
The system uses lightweight, disposable query plan representations that can be quickly generated and evaluated. Instead of maintaining complex persistent query plan structures, the system creates simple plan objects on-demand, evaluates them, and discards them. This approach allows the system to explore multiple execution paths without the overhead of managing complex long-term plan structures.
Data Source
AI summary
Techniques are disclosed relating to query planning and execution. A computer system can receive a database statement that comprises a LIKE predicate that defines a set of pattern parameters. The computer system may generate first and second query paths for a query plan associated with the database statement. The first query path utilizes an index associated with a database table specified by the database statement while the second query path does not utilize the index. The computer system executes the database statement in accordance with the query plan and values that are provided for the set of pattern parameters. As a part of executing the database statement, the computer system may evaluate those values to determine whether they are prefix constants and execute the first query path instead of the second query path if all the values are prefix constants.