Logical Indexes Abstract Partitioned Database Table Complexity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Partitioned database tables introduce complexity in query processing, making query optimization and execution more challenging compared to non-partitioned tables, which detracts from the benefits of partitioning.
Innovation Solution
The creation and utilization of logical indexes that abstract the complexity of partitioned database tables by leveraging order-preserving properties of table metadata, adding a partition equality predicate to merge-join processing, and selecting execution of a logical skip scan on partitioned tables when a query predicate references a specific partition, thereby abstracting the explicit referencing of table partitions from query writers and processing systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If partitioned database tables are used, then data management and retrieval efficiency are improved, but query processing complexity increases
Solution Approach 1:
The patent introduces a logical index as an intermediary layer between the query processor and the physical partitioned table. This logical index abstracts the partitioning details, presenting a simplified interface that behaves like a non-partitioned table. The logical index maps logical row identifiers to physical partition locations, allowing query processors to operate without knowing about the underlying partitioning structure, thus resolving the complexity while maintaining partitioning benefits.
Solution Approach 2:
The patent creates a virtual copy of the partitioned table structure through the logical index. Instead of working directly with the complex physical partitions, the system operates on this simplified logical representation. The logical index replicates the table's interface and behavior characteristics while hiding the physical partitioning complexity, enabling query processors to work with a simplified model that maintains the benefits of partitioning.
2Productivity
If partitioned database tables are used, then data retrieval efficiency is improved, but query optimization complexity increases
Solution Approach 1:
The logical index serves as a mediator that translates query optimization operations into partition-aware execution plans. It provides the query optimizer with a simplified view of the table structure, allowing optimization algorithms to function as they would on non-partitioned tables, while the logical index handles the mapping to physical partitions during execution.
3Adaptability or versatility
If partitioned database tables are used, then data management flexibility is improved, but query execution complexity increases
Solution Approach 1:
The logical index acts as an intermediary that maintains the flexibility benefits of partitioning while providing a simple execution interface. It handles the complexity of mapping queries to specific partitions and managing the physical data locations, allowing query execution to proceed as if working with a single unified table structure.
Data Source
AI summary
Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.