Database LIKE Predicate Planning With Dynamic Index Paths
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional LIKE pecking approaches in database systems are deficient due to their inability to handle arrays of patterns in LIKE predicates, support typecasting, and provide an accurate cost model for query planning.
Innovation Solution
The proposed solution involves generating a query plan that includes both an index-friendly path and an index-unfriendly path for LIKE predicates. The cost of the LIKE pecking path is based on the cost of the index-friendly path, allowing for more efficient query execution. Additionally, the system supports multiple pattern values and typecasting in LIKE predicates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If conventional LIKE pecking approaches are used, then simple pattern matching can be performed, but the system cannot handle arrays of patterns or typecasting
Solution Approach 1:
The query plan is segmented into multiple distinct paths: an index-friendly path for efficient index-based searches and an index-unfriendly path for full table scans. This segmentation allows the system to handle diverse query types including arrays of patterns and typecasting by selecting the appropriate path, thereby improving adaptability without requiring a completely complex unified structure.
Solution Approach 2:
The query plan becomes dynamic by allowing runtime selection between index-friendly and index-unfriendly paths based on the actual parameters provided. This dynamic approach enables the system to adapt to different query scenarios (simple patterns, arrays of patterns, typecasting) by evaluating which path is more efficient at execution time, thus improving versatility while maintaining manageable complexity.
2Productivity
If both index-friendly path and index-unfriendly path are included in query plan, then query execution efficiency is improved, but cost estimation accuracy becomes challenging
Solution Approach 1:
The system introduces an intermediary cost estimation mechanism that calculates separate costs for both the index-friendly path and index-unfriendly path, then selects the path with the lower estimated cost. This intermediary cost model acts as a mediator between the two paths, enabling efficient query execution by choosing the optimal path while maintaining accurate cost estimation through comparative evaluation rather than attempting to estimate a single ambiguous cost.
3Speed
If index is used for LIKE predicates, then query execution speed is improved, but queries with arrays of patterns or typecasting cannot be handled
Solution Approach 1:
The query execution system becomes dynamic by allowing runtime selection between index-friendly and index-unfriendly paths based on the actual parameters provided. When simple patterns are detected, the index-friendly path provides fast execution. When arrays of patterns or typecasting are detected, the system dynamically switches to the index-unfriendly path, thereby maintaining both speed for simple queries and versatility for complex queries.
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.