Index Merging for SQL Workload Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional methods fail to determine the ideal combination of access structures, including indexes and materialized views, for a given SQL workload within storage limitations, leading to inefficient query execution and resource consumption.
Innovation Solution
An integrated method that uses a cost-based query optimizer to analyze and recommend the best combination of indexes and materialized views, incorporating optimizer heuristics and user-defined priorities, while consolidating index candidates to reduce resource consumption and maintenance costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If multiple indexes are created to improve query performance, then query execution speed is improved, but storage space consumption and maintenance costs increase
Solution Approach 1:
The patent merges multiple index candidates into a consolidated index structure that satisfies multiple query requirements simultaneously. The index merger analyzes overlapping index candidates from different queries and combines them into a single index that serves multiple purposes, reducing the total number of indexes needed while maintaining query performance.
Solution Approach 2:
The consolidated index structure is designed to be multi-functional, serving multiple query types and access patterns simultaneously. A single merged index can satisfy equality queries, range queries, and join operations for different tables, making the index structure universal rather than specialized for a single query type.
2Productivity
If more indexes are created to satisfy multiple queries, then query performance is improved, but index maintenance overhead increases
Solution Approach 1:
By merging index candidates into a single consolidated index, the system reduces the number of separate index maintenance operations. Instead of maintaining multiple separate indexes that may overlap in functionality, the merged index structure requires only one maintenance operation, reducing the time and resources spent on index updates during data modifications.
3Ease of operation
If conventional methods are used to select indexes, then implementation is simple, but the ideal combination of access structures cannot be determined
Solution Approach 1:
The system performs self-service by automatically analyzing query workloads, generating index candidates, evaluating their effectiveness, and merging them into optimal consolidated indexes without requiring manual database administrator intervention. The index merger autonomously evaluates index candidates based on actual query patterns and performance metrics, selecting the ideal combination of access structures automatically.
4Speed
If all possible indexes are created to ensure optimal query performance, then query execution is optimized, but resource consumption and system complexity increase
Solution Approach 1:
The index merger extracts only the essential and overlapping portions of multiple index candidates, creating a consolidated index that contains only the necessary columns and structure to satisfy multiple query requirements. Rather than creating all possible indexes, the system extracts the common denominators and merges them into a minimal sufficient index structure.
Data Source
AI summary
An access structure analysis method is interspersed with the query optimization process. The method can determine the ideal combination of access structures, including both materialized views and indexes, for a given database workload. The interspersed structure analysis method can include advanced transformations like view merging, star transformation, bitmap access plans, query rewrite using materialized views, for example. The method may be performed using the query optimizer's rules as heuristics to guide the index candidate generation process.