Index Selection Subsystem for Multi-System Database Workloads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Selecting optimal indexes in complex, multi-system database management systems is challenging due to large search spaces and computational expenses, often relying on intuition and application experience rather than systematic approaches.
Innovation Solution
An index selection subsystem that merges and sorts query information from multiple database systems into a workload, generating candidate indexes and selecting recommended indexes based on specific criteria, allowing for different index configurations across systems to optimize performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional search algorithms are used for index selection in multi-system database management systems, then index selection can be performed, but the computational cost becomes prohibitively expensive due to the large search space of candidate indexes
Solution Approach 1:
The patent segments the large search space of candidate indexes into multiple smaller search spaces, one for each individual database system. Instead of evaluating all possible indexes across the entire multi-system environment simultaneously, the system divides the index selection problem into subsystem-level problems that can be solved independently and more efficiently.
Solution Approach 2:
The patent introduces a new dimension to the index selection process by considering database system hierarchy and scope. It separates index selection into two dimensions: local index selection at each database system level and global index selection across multiple systems. This dimensional separation allows the system to manage complexity by operating at appropriate levels of abstraction.
2Ease of manufacture
If manual index design decisions are made based on application experience and intuition, then index creation can be performed, but the effectiveness becomes increasingly difficult with complex database applications containing thousands of tables and indexes
Solution Approach 1:
The patent implements self-service by enabling the database system to automatically select optimal indexes without requiring manual intervention from database designers. The system uses automated algorithms to analyze query patterns, evaluate candidate indexes, and make index selection decisions independently, allowing the system to serve itself rather than relying on human expertise.
Solution Approach 2:
The patent incorporates feedback mechanisms where the system continuously monitors query execution patterns and performance metrics to inform index selection decisions. By using actual query workloads and performance data as feedback, the system can adaptively select indexes that are proven to be effective, rather than relying on static manual decisions or theoretical models.
3Stability of the object's composition
If a unified index selection approach is used across multiple database systems, then consistency can be maintained, but the computational complexity and cost increase significantly
Solution Approach 1:
The patent segments the multi-system index selection problem into independent subsystem-level problems. Each database system performs its own index selection independently, maintaining local consistency without requiring coordination across the entire multi-system environment. This segmentation reduces the overall complexity from exponential (across all systems) to polynomial (sum of individual systems).
Solution Approach 2:
The patent merges the results from individual subsystem index selections to achieve global index selection. By combining locally-optimal index selections from multiple database systems, the system achieves a globally consistent index configuration without the computational burden of evaluating all possibilities across the entire multi-system environment simultaneously.
Data Source
AI summary
A system includes a multi-system database management system having a plurality of database systems. An index selection subsystem combines sets of query information from respective ones of the plurality of database systems into a workload. The index selection subsystem then generates candidate indexes from the workload, and selects recommended indexes from the candidate indexes based on one or more criteria.