Virtual View Indexing on Base Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Non-materialized views in database management systems are less performant due to the need to re-execute query results every time they are queried, as there is no stored data to index, making it difficult to improve query performance.
Innovation Solution
Creating indexes on virtual views by identifying relationships between columns of the virtual view and the underlying table, generating an index that references the underlying table columns to enhance query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If indexes are created on materialized views, then query performance is improved, but storage space is consumed and materialization overhead is required
Solution Approach 1:
The patent introduces a virtual view index as an intermediary structure that mediates between the query and the underlying base table. Instead of creating indexes directly on materialized views (which would require storage and materialization), the virtual view index serves as a mediator that translates queries into efficient access patterns on the base table, providing indexing benefits without the overhead of actual materialization
Solution Approach 2:
The patent creates a virtual copy of the index structure that references the base table columns rather than storing actual view data. This virtual index copy provides the benefits of indexing (fast query access) without requiring physical storage of the view results, effectively decoupling the indexing mechanism from the materialization requirement
2Quantity of substance
If non-materialized views are used, then storage space is saved, but query performance deteriorates due to repeated query execution
Solution Approach 1:
The patent performs preliminary action by pre-defining the index structure on the virtual view before queries are executed. This pre-established index structure guides the query optimizer to generate efficient execution plans, eliminating the need to scan and re-execute the entire base table for each query while maintaining the space efficiency of non-materialized views
Solution Approach 2:
The virtual view index acts as an intermediary that bridges the gap between non-materialized views and efficient query execution. It provides indexing capabilities without requiring view materialization, allowing the system to enjoy both the storage efficiency of non-materialized views and the performance benefits of indexed access
Data Source
AI summary
A database management system receives a request to create an index for a virtual view of a database table. In response to the request, the database management system identifies relationships between the columns of the virtual view and columns of the database table. The database management system generates an index on the database table, where columns are included in the generated index based on the identified relationships and the definition of the virtual view. Queries of the virtual view are assisted by the generated index of the underlying table.