Partitioned Index Asynchronous Maintenance for Bulk Loading
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Bulk loading data into a table with an existing index, especially complex XML indexes, often results in prolonged update times and inefficient query execution plans due to synchronous index updates, which can render the index unusable until synchronization, leading to stale results and increased overhead from flashback queries.
Innovation Solution
Marking only the new index partition as asynchronously maintained during bulk loading allows other partitions to remain synchronously updated, enabling continuous access to current data and efficient query optimization without rewriting queries as flashback queries, thus avoiding index unavailability and reducing overhead.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If synchronous index updates are performed during bulk loading, then data consistency is maintained, but loading throughput is reduced and index update time is prolonged
Solution Approach 1:
The index is divided into multiple partitions that correspond to table partitions. When bulk loading data into a specific table partition, only the corresponding index partition is marked as asynchronously maintained, while other index partitions continue to be synchronously updated. This segmentation allows parallel processing of index updates across different partitions, resolving the contradiction between maintaining data consistency and improving loading throughput.
Solution Approach 2:
Different index partitions are treated differently based on their relationship with the table partition being loaded. The index partition corresponding to the loaded table partition is marked for asynchronous maintenance, while other index partitions maintain synchronous updates. This local differentiation allows the system to optimize throughput for the specific loading operation without sacrificing consistency for other data regions.
2Reliability
If the entire index is marked as unusable during bulk loading, then index update conflicts are avoided, but query execution efficiency deteriorates due to flashback queries
Solution Approach 1:
The index is segmented into multiple partitions, and only the specific partition corresponding to the bulk-loaded table partition is marked as asynchronously maintained. Other index partitions remain usable for query execution. This segmentation prevents the need to mark the entire index as unusable, thereby avoiding the performance penalty of flashback queries while still ensuring consistency for the loaded data.
Solution Approach 2:
The asynchronous maintenance status is applied locally to only the index partition corresponding to the bulk-loaded table partition, rather than globally to the entire index. This allows queries to continue using current index data for other partitions without requiring flashback operations, thus reducing query execution time while maintaining update consistency.
3Productivity
If table partitioning is used for bulk loading, then loading performance is improved, but index maintenance complexity increases
Solution Approach 1:
The index is partitioned to match the table partitions, creating a one-to-one correspondence between table partitions and index partitions. This segmentation allows the database system to manage bulk loading operations at the partition level, improving loading performance by enabling parallel processing while keeping index maintenance manageable through structured organization.
Solution Approach 2:
The database system automatically maintains the correspondence between table partitions and index partitions, and automatically manages the asynchronous maintenance status of index partitions during bulk loading operations. This self-service mechanism reduces the operational complexity of managing partitioned indexes while preserving the performance benefits of partitioning.
Data Source
AI summary
A database server provides a partitioned table with a corresponding locally partitioned index. When a request is made to load data into the table by an application, a table partition, along with a corresponding index partition, is created for the application. The corresponding index partition in marked as asynchronously maintained, allowing other index partitions to be used by the query optimizer in formulating query plans for other table partitions. Once the loading of data is complete, a synchronization operation is called with the result of conforming the data in the asynchronously maintained index partition with its corresponding table in which the data was loaded. Once the data in the corresponding table partition and the data in the asynchronously maintained index partition are synchronized, the asynchronously maintained index partition is marked as synchronously maintained.