Genetic Algorithm for Database Access Structure Selection Under Storage Constraints
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The selection of optimal database access structures is complex and intractable, particularly when considering constraints like storage limits and maximum indexes per table, making it difficult to find a configuration that balances performance improvement and cost effectively.
Innovation Solution
A modified Simple Genetic Algorithm (SGA) incorporating constraint handling, Optimal-Storage-Profiling, and elitism to generate feasible configurations that minimize cost while considering storage consumption levels and favoring strong candidates, ensuring efficient and scalable results.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional Access Advisor tools are used for access structure selection, then comprehensive analysis of query workloads is performed, but the computational complexity becomes intractable and execution time becomes excessively long
Solution Approach 1:
The patent segments the intractable access structure selection problem into manageable generations of a genetic algorithm. Each generation evaluates a subset of candidate configurations, progressively evolving toward optimal solutions without requiring exhaustive search of the entire solution space, thus reducing execution time while maintaining selection quality.
Solution Approach 2:
The patent performs preliminary actions by pre-evaluating candidate access structures and pre-computing cost metrics before full optimization. The genetic algorithm pre-generates candidate configurations and pre-assesses their fitness, allowing the system to avoid re-evaluating all possibilities from scratch, thereby reducing overall execution time.
2Speed
If more access structures are created to improve query performance, then query execution speed improves, but storage space consumption increases
Solution Approach 1:
The patent changes the parameter of storage allocation by using genetic algorithms to optimize the number and type of access structures created. Instead of creating all possible access structures, the system evolves configurations that achieve optimal query performance within constrained storage budgets, balancing speed improvement with storage consumption.
Solution Approach 2:
The patent applies partial action by creating only the necessary subset of access structures needed to achieve satisfactory query performance. Rather than implementing all possible indexes and materialized views, the genetic algorithm identifies and creates only those structures that provide the most benefit within available storage, avoiding excessive storage consumption.
3Measurement precision
If the number of candidate configurations is increased to improve solution quality, then the likelihood of finding optimal solutions increases, but the computational resources and time required increase
Solution Approach 1:
The patent introduces dynamics by making the population size and evaluation depth adaptive rather than static. The genetic algorithm dynamically adjusts the number of candidate configurations evaluated based on computational resources available and convergence criteria, allowing the system to maintain high solution quality while adapting computational efficiency to available resources.
Data Source
AI summary
A configuration that minimizes cost subject to the constraints is selected. A Simple Genetic Algorithm (SGA) is modified to incorporate the handling of constraints. The SGA is further modified to incorporate Optimal-Storage-Profiling to produce an increased number of fit individuals in each generation, developing a policy which will consider configurations of all storage-consumption levels and favor those configurations whose storage-consumption indicate they more likely to be strong candidates. An ideal-distribution of configurations, based on their storage-consumption, for each generation is developed. Different elitist policies are also incorporated to achieve greater scalability without sacrificing the quality of the solution.