User-Defined Index Auxiliary Properties for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional user-defined indexes in database systems are inefficient as they require two-stage data processing, leading to increased resource usage due to their inability to evaluate additional query predicates and ORDER BY clauses, resulting in poor query performance.
Innovation Solution
Extending user-defined indexes with auxiliary properties such as filtering and ordering capabilities, allowing them to evaluate query predicates and sort results directly, reducing the need for additional processing by the database server.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a conventional user-defined index is used to evaluate a user-defined operator, then the index can return row identifiers to the database server, but the database server must perform additional two-stage data processing which increases CPU and memory usage
Solution Approach 1:
The patent merges the filtering and sorting operations into the index evaluation process itself. The extended user-defined index now performs not only the user-defined operator evaluation but also applies additional query predicates and performs sorting within the index structure, eliminating the need for separate two-stage processing by the database server.
Solution Approach 2:
The index is extended with auxiliary properties that enable it to pre-process data by applying filtering predicates and sorting operations before returning results to the database server. This preliminary action within the index structure reduces the workload on the database server and improves overall query processing efficiency.
2Productivity
If a user-defined index is used with additional selective predicates, then the index can evaluate the user-defined operator, but it returns many extra rows that require additional filtering which wastes CPU and I/O cycles
Solution Approach 1:
The user-defined index is extended with multi-functionality through auxiliary properties. It can now evaluate the user-defined operator, apply additional filtering predicates, and perform sorting operations. This universal capability allows the index to handle multiple query requirements in a single operation, reducing unnecessary row returns and wasted processing cycles.
3Productivity
If a user-defined index is used for queries with ORDER BY clauses, then the index can evaluate the user-defined operator, but it returns unsorted row identifiers requiring additional sorting processing which increases resource usage
Solution Approach 1:
The index is extended with an ordering auxiliary property that enables it to perform sorting operations within the index structure before returning row identifiers to the database server. This preliminary sorting action eliminates the need for additional sorting processing and reduces overall system resource usage.
Data Source
AI summary
In one embodiment, a database server registers one or more functions included in a user-defined index that includes one or more auxiliary properties. The one or more functions are operable to generate index entries of the user-defined index for the one or more auxiliary properties. The one or more auxiliary properties are different than, and in addition to, a primary property of the user-defined index that provides for evaluating a query operator. The database server receives a first statement that defines the user-defined index, where the first statement includes one or more parameters that define the one or more auxiliary properties. The database server invokes the one or more functions in response to the first statement, where the one or more functions when invoked generate and store the index entries of the user-defined index according to the one or more parameters that define the one or more auxiliary properties.