Inverse Function Replacement for Indexed Query Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Data type conversions between software applications and relational databases are computationally expensive, leading to inefficient query performance due to the inability to use indexes and inaccurate cost estimation by query optimizers, especially when dealing with large result sets.
Innovation Solution
Register and utilize inverse functions associated with specific functions to replace them in query execution plans, allowing direct value comparisons and enabling the use of existing indexes, thereby improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data type conversion functions are used to interconvert between application data types and database data types, then data compatibility is achieved, but query performance deteriorates due to computational expense and inability to use indexes
Solution Approach 1:
The patent inverts the conventional approach by replacing forward conversion functions (application format → database format) with inverse conversion functions (database format → application format) in query execution plans. This allows the database to perform conversions in the opposite direction, enabling index usage and significantly improving query performance while maintaining data compatibility between different data types
Solution Approach 2:
The patent changes the conversion direction parameter from forward conversion to inverse conversion. By registering inverse functions that convert database-stored values to application-format values during query execution, the system maintains data compatibility while enabling efficient index-based query processing instead of expensive row-by-row conversion
2Adaptability or versatility
If conversion functions are performed row-by-row for large result sets, then data type interconversion is achieved, but processing time increases substantially
Solution Approach 1:
The patent inverts the conversion approach to allow the database engine to perform inverse conversions using registered functions during query execution. This enables batch processing and index utilization, reducing processing time from row-by-row conversion to efficient set-based operations that leverage database optimization capabilities
3Measurement precision
If query optimizers perform accurate cost estimation for function-based queries, then query optimization is improved, but computational overhead increases
Solution Approach 1:
The patent performs preliminary registration of inverse conversion functions and their cost characteristics in the database system. This allows query optimizers to use pre-computed cost estimates for inverse function operations, improving cost estimation accuracy without requiring complex real-time analysis during query optimization, thus reducing computational overhead
Data Source
AI summary
Techniques and solutions are provided for improving query performance using inverse functions. Often a function is used to perform operations such as data type conversions. The use of these functions can be resource intensive, such as if a conversion needs to be performed for all rows of a particular relational database table. The present disclosure allows for the registration of inverse functions that can be used, or at least be considered for use, in place of a function. A given inverse function can be associated with its function using techniques such as maintaining mapping information or using a particular naming convention. A particular syntax is provided for designating and creating an inverse function.