Non-blocking Database Table Alteration via Hidden Clone Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Legacy techniques for creating partitioned data tables and indexes in very large databases often result in unwanted downtime and access blocking, requiring significant processing power and time, as well as explicit synchronization operations that lock out users during the alteration process.
Innovation Solution
The implementation of algorithms that facilitate non-blocking database alterations using hidden database structures, allowing for background table partitioning and index creation, enabling concurrent database transactions and immediate performance improvements without disrupting user access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If legacy techniques are used for creating partitioned data tables and indexes, then database table partitioning and index creation can be accomplished, but downtime and user access blocking occur during the alteration process
Solution Approach 1:
The patent creates a clone of the original table before partitioning operations begin. This preliminary cloning action allows the partitioning to be performed on the clone while the original table remains accessible for read operations, thereby eliminating downtime and maintaining database availability during the alteration process.
Solution Approach 2:
The patent introduces a clone table as an intermediary structure between the original table and the final partitioned state. This intermediary clone allows concurrent access to the original table while partitioning operations are performed on the clone, resolving the contradiction between maintaining availability and performing structural alterations.
2Reliability
If explicit synchronization operations are used during partitioning, then data consistency can be maintained, but user access is locked out during the entire alteration period
Solution Approach 1:
The clone table is created in advance with the same structure as the original table, allowing partitioning operations to be performed on the clone without affecting access to the original. This preliminary cloning enables data consistency to be maintained through controlled synchronization while users continue to access the original table.
Solution Approach 2:
The patent segments the database structure into two independent parts: the original table for user access and the clone table for partitioning operations. This segmentation allows data consistency to be maintained through selective synchronization while user access to the original table remains uninterrupted.
3Productivity
If the entire table is partitioned at once, then the partitioning operation completes in a single step, but significant processing power and time are required causing long blocking periods
Solution Approach 1:
The patent divides the partitioning operation into two independent segments: creating the clone table and then partitioning the clone. This segmentation allows the partitioning to be performed on the clone table without blocking access to the original table, improving operational efficiency while reducing the perceived processing time for users.
Solution Approach 2:
The clone table serves as an intermediary that absorbs the processing burden of partitioning operations. By performing partitioning on the clone rather than the original table, the system maintains high productivity in the partitioning operation while minimizing the impact on user-accessible resources and reducing blocking periods.
4Adaptability or versatility
If legacy DDL statements are used for partitioning, then table structure alteration can be achieved, but DML accesses are locked out during the partitioning process
Solution Approach 1:
The clone table is created beforehand with the desired partitioned structure. This preliminary action allows DDL statements to be executed on the clone without affecting DML operations on the original table, maintaining both structural adaptability and concurrent transaction processing capability.
Solution Approach 2:
The patent segments DDL operations (performed on the clone) from DML operations (performed on the original table). This segmentation enables table structure modification capability to be fully utilized on the clone while concurrent transaction processing continues uninterrupted on the original table, resolving the contradiction between adaptability and productivity.
Data Source
AI summary
Systems for managing large database systems. Storage devices hold a set of one or more subject database tables that hold subject data accessed by one or more users. A data definition statement to perform a table alteration operation (e.g., a table partition operation) over a particular table in the subject database is received, parsed, and the table alteration operation over the particular table is initiated. During the processing of the data definition statement to complete the table alteration operation, a data manipulation statement to perform a table access operation over the same table is received and parsed. The data manipulation statement is not blocked, even while the table alteration operations are ongoing. Rather, during the time that the table alteration operations are ongoing, the data manipulation statement is processed using hidden tables. After the table alteration operations have completed, incoming data manipulation statements are performed over the altered database tables.