Virtual Column Expression Replacement in Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies in processing queries due to the need for manual rewriting of query programs to utilize virtual columns, which is costly in terms of human labor and time, especially when dealing with expressions that can be computed on demand.
Innovation Solution
Automatically replacing equivalent expressions in query statements with virtual columns, allowing for column-based optimizations without requiring human intervention, by defining virtual columns based on existing regular columns and leveraging their computation for improved query execution efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If manual rewriting of query programs is performed to utilize virtual columns, then query processing efficiency is improved, but human labor cost and time increase
Solution Approach 1:
The system automatically rewrites query programs to utilize virtual columns without requiring manual human intervention. The query rewriting mechanism detects expressions in queries that match virtual column definitions and automatically substitutes them, allowing the database system to serve itself rather than requiring human analysts to manually rewrite queries.
Solution Approach 2:
Virtual columns are pre-defined in the database schema with their computational logic established beforehand. This preliminary setup allows the automatic query rewriting mechanism to efficiently identify and substitute matching expressions during query execution, rather than requiring ad-hoc manual rewriting for each query.
2Quantity of substance
If expressions are computed on demand without virtual columns, then storage resources are reduced, but query processing efficiency decreases
Solution Approach 1:
Virtual columns provide a dynamic solution where computed values can be materialized when beneficial for query performance but are not permanently stored. The system can compute virtual column values on demand, materialize them temporarily in memory during query execution, and then discard them, providing flexibility between storage efficiency and processing speed.
Solution Approach 2:
Virtual columns act as an intermediary layer between the base table columns and the query processing logic. They provide pre-computed or on-demand computed values that can be efficiently used in queries without requiring permanent storage of all computed results, thus mediating between storage constraints and processing requirements.
Data Source
AI summary
Techniques are described herein for replacing a part of an expression in a query to a database with a virtual column. A virtual column is defined by the database to contain results of a defining expression. A part of an expression in a query is replaced with a virtual column whose defining expression is equal to the part of the expression in the query. The part of an expression in a query can be the entire expression. When an expression in a query can be replaced with more than one replacement expression involving virtual columns, the replacement expression with the least cost of query processing is selected.