Database Index Candidate Identification via Combinatorial Search
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manual selection of physical database design elements, such as indexes and materialized views, is complex and resource-intensive due to the large number of candidate views, making it impractical to search and analyze the entire solution space effectively.
Innovation Solution
A system and method for identifying index candidates for a database that includes an interface for receiving workload data from queries and an analyzing processor to identify suitable index candidates through combinatorial search and cost-based analysis, recommending optimal index structures such as Join Indexes with primary and covering columns.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If the entire solution space of candidate materialized views is searched and analyzed, then the completeness of index candidate identification is improved, but the CPU resources and time required become excessively large
Solution Approach 1:
The patent segments the large solution space of candidate materialized views into smaller, manageable subsets based on query patterns and data characteristics. Instead of analyzing all possible candidates, the system divides them into groups that can be evaluated separately, reducing the overall computational burden while maintaining identification completeness within each segment.
Solution Approach 2:
The patent applies partial action by analyzing only the most promising subset of candidate materialized views rather than the entire solution space. The system identifies and evaluates candidates that are most likely to benefit the specific workload, performing sufficient analysis to achieve practical optimality without exhaustively searching all possibilities.
2Manufacturing precision
If the entire solution space of candidate materialized views is searched and analyzed, then the completeness of index candidate identification is improved, but the time required becomes excessively large
Solution Approach 1:
The patent segments the analysis process into distinct phases: initial candidate generation based on query patterns, filtering of promising candidates using statistical criteria, and detailed evaluation of selected subsets. This segmentation reduces the time required by processing only relevant portions of the solution space in detail.
Solution Approach 2:
The patent performs preliminary filtering and ranking of candidate materialized views before conducting full analysis. By pre-identifying and prioritizing the most promising candidates based on query frequency and data characteristics, the system reduces the time required for comprehensive evaluation while maintaining identification quality.
3Adaptability or versatility
If manual selection of physical database design elements is performed, then user control over design decisions is improved, but the complexity and resource intensity of the process increases
Solution Approach 1:
The patent enables the database system to automatically generate and evaluate materialized view candidates based on workload analysis, reducing the need for manual intervention. The system performs self-service by identifying optimal indexing strategies autonomously, while still allowing users to review and adjust recommendations if desired.
Solution Approach 2:
The patent performs preliminary analysis and generates index recommendations automatically before user review. By pre-computing candidate materialized views and their expected benefits, the system reduces the complexity of manual selection while preserving user control over final design decisions.
Data Source
AI summary
A system and method for identifying a selection of index candidates for a relational database. The system includes a Predicate Analyzer for examining database queries in workload and recording column and predicate information; a Search Engine for performing a combinatorial search of index candidates; an Index Emulator for emulating the existence of a set of candidate indexes by making necessary “fake” entries into the relational database management system data dictionary; and a Query Optimizer for estimating the cost of a given query within a given index configuration. In accordance with the method, data indicative of a workload comprising one or more queries is received, and predicates are then analysed and organized into column groups for each basic predicate type. Statistics are collected on each of these groups. Non-predicate portions of each query are scanned to obtain additional covering columns. A combinational search is performed on the column groups to identify covering and non-covering candidates. These are then used in conjunction with collected statistics and baseline costs are calculated to perform a cost bases analysis of the candidates and generate index candidate recommendations.