Database Server Automatic Functional Index Filtering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems cannot effectively utilize functional indexes unless the query explicitly references the index, limiting their utility, especially when dealing with large or abstract data types that violate key size constraints.
Innovation Solution
The database server automatically modifies database queries to include references to functional indexes, allowing their use as primary filters even if the query does not initially reference them, by adding conditions that ensure the functional index is used for optimization without requiring explicit index references in the query.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If a functional index is created on a column, then query evaluation speed is improved, but the index can only be used if the query explicitly references it, reducing its utility
Solution Approach 1:
The database server automatically detects when a functional index can be applied to a query and modifies the query to reference the index without requiring explicit user specification. The system serves itself by autonomously optimizing queries using available functional indexes, making the index creation process self-sufficient and eliminating the need for manual query rewriting.
Solution Approach 2:
The system changes the query parameters by automatically adding functional index references to the query execution plan. The database server modifies the internal representation of the query to include functional index operations, transforming the query execution parameters to leverage the pre-computed functional index data structures for faster evaluation.
2Speed
If a traditional index is created on a column, then data retrieval is faster, but indexes cannot be created on columns with large or abstract data types
Solution Approach 1:
The functional index approach segments the data processing by creating an index on a derived value (the result of applying a function to the column) rather than the raw column data itself. This allows the index to store only the essential indexed values while the full data remains in the original column, enabling indexing of large or abstract data types that would otherwise be incompatible with traditional indexes.
Solution Approach 2:
The function application acts as an intermediary between the raw column data and the index structure. By transforming the original data through a function (such as extracting a prefix from a long string or converting an abstract type to a comparable form), the system creates an intermediate representation that can be efficiently indexed while preserving the ability to retrieve and verify results against the original data.
Data Source
AI summary
An approach for using functional indexes as a primary filter is provided. A database query that contains a reference to a column of a table, but does not contain any reference to a functional index based on that column, is received by a database server. The database server adds to the database query a reference to the functional index on the column. The database server determines if the database query should be executed using the functional index. If the database server determines that the database query should be executed using the functional index, then the database query is executed with the functional index. If the database server determines that the database query should not be executed using the functional index, then data that indicates that the functional index is not to be evaluated when the database query is executed is stored.