Context-Sensitive Index Visibility for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant/multi-application environments, the proliferation of database indexes increases the computational cost and risk of selecting suboptimal access paths during query optimization, leading to inefficient data retrieval and resource consumption.
Innovation Solution
Implementing context-sensitive index visibility, where database indexes are selectively made visible or invisible based on query context attributes such as database user, module, and action attributes, using Data Definition Language (DDL) statements to define visibility attributes, thereby reducing the number of indexes evaluated during query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If the number of database indexes is increased to improve data retrieval speed, then the likelihood of finding optimal access paths increases, but the computational cost and complexity of query optimization increases
Solution Approach 1:
The patent extracts and removes irrelevant database indexes from the query optimization evaluation set by determining index visibility based on query context attributes. This extraction principle directly reduces the number of indexes that need to be evaluated, lowering query optimization complexity while preserving access to relevant indexes for optimal data retrieval speed.
Solution Approach 2:
The patent applies local quality by making indexes selectively visible or invisible based on specific query context attributes (user, module, action). Instead of uniformly evaluating all indexes for all queries, the system tailors the visible index set to match the specific query context, thereby reducing evaluation complexity for each query while maintaining optimal retrieval performance.
2Reliability
If all possible alternative access paths are evaluated during query optimization, then the optimal access path can be selected, but the computational time and resources required increase significantly
Solution Approach 1:
The patent extracts irrelevant access paths from the evaluation set by determining which database indexes are visible based on query context attributes. This removes unnecessary alternatives from consideration, reducing query optimization time while maintaining reliable selection of the optimal access path among the remaining relevant alternatives.
Solution Approach 2:
The patent applies partial action by evaluating only a subset of relevant access paths rather than all possible alternatives. By using query context attributes to determine index visibility, the system evaluates just enough alternatives to ensure optimal path selection for the given context, avoiding the excessive computation of irrelevant paths.
3Adaptability or versatility
If the number of tables and indexes per table increases in N-tier architectures, then more data can be accessed and retrieved, but the number of alternative execution ways and optimization cost increase manifold
Solution Approach 1:
The patent extracts and removes irrelevant indexes from the evaluation set by applying visibility rules based on query context attributes. This extraction principle directly addresses the complexity issue in N-tier architectures by filtering out indexes that are not relevant to the current query context, thereby reducing evaluation complexity while preserving access to all necessary data across multiple tables.
Solution Approach 2:
The patent applies local quality by making indexes selectively visible based on query context attributes (user, module, action). This creates a tailored evaluation set for each query context, reducing the complexity of evaluating indexes across multiple tables while maintaining the versatility to access any required data when appropriate indexes are visible.
Data Source
AI summary
Context-sensitive index visibility is used to reduce the number databases indexes that are considered and evaluated as an access path by query optimization. For a database statement that references or otherwise requires access to a set of database tables for which a DBMS defines a set of database indexes, context-sensitive index visibility limits the selection of database indexes. A query context comprises query context attributes, each of which is associated with a requested execution of a database statement. Query context attributes dictate whether a database index can be considered for query optimization. An example of a query context attribute is database user of a database session in which a database statement is issued. According to an embodiment of the present invention, a query context attribute is a session attribute stored as part of a session state of a database session.