Database Table Exchange Physical Property Replication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database exchange processes face errors due to mismatches between the schema used to create a new table and the actual storage organization of the source table, leading to incorrect data access and system failures.
Innovation Solution
The implementation of a 'CREATE TABLE FOR EXCHANGE' (CTFE) statement that identifies and replicates both logical and extra-logical properties of a source table, ensuring that the new table is created with the same physical properties as the source table, including column order and offset positions, to maintain accurate data access and storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If a CREATE TABLE AS SELECT (CTAS) statement is used to create a new table from source table schema, then a new table can be created with the same logical schema, but the new table may have incorrect physical properties (column order, offset positions) that mismatch the source table's storage organization
Solution Approach 1:
The patent uses a CREATE TABLE FOR EXCHANGE statement that copies not only the logical schema but also the physical properties (column order, offset positions, storage organization) from the source table to the new table. This is achieved by exchanging partition data structures between tables, which preserves the exact physical layout including hidden columns and their positions, thereby resolving the contradiction between easy table creation and physical property accuracy.
2Ease of operation
If the schema exposed by CTAS statement is different from how the source table organizes data internally, then table creation is simplified, but pointer data for the new table and source partition end up referencing wrong column positions causing system failure
Solution Approach 1:
The patent segments the table structure into visible columns (exposed to users) and hidden columns (used for internal storage organization). The CREATE TABLE FOR EXCHANGE statement separately handles these segments, ensuring that the new table receives the correct physical organization of hidden columns while maintaining the expected logical schema for visible columns. This segmentation resolves the contradiction between ease of operation and reliability by decoupling schema exposure from physical storage organization.
3Loss of time
If metadata operation is used to separate records from source partition, then data transfer can be performed at later time with lesser activity, but the new table must be created with exact physical properties to avoid data access errors
Solution Approach 1:
The patent performs preliminary action by creating the new table with the correct physical properties (column order, offset positions, hidden column structure) before the actual data exchange operation. The CREATE TABLE FOR EXCHANGE statement prepares the new table structure in advance, matching the source table's physical organization, so that when the metadata exchange occurs, the data can be transferred without timing constraints. This preliminary table creation with correct physical properties resolves the contradiction between time flexibility and property accuracy.
Data Source
AI summary
Techniques are described herein for creating a new database table for an exchange operation with a table partition of another table. A database management system (DBMS) is configured to receive a query such as “CREATE TABLE new_table FOR EXCHANGE FROM partition_P1”. The DBMS then identifies properties of the source partition (e.g., partition P1) and creates a new database table based on the properties identified. Some of these properties (e.g., hidden columns, data modeling frameworks, and table statistics) may not otherwise be directly accessible by the external entity that sent the query. The newly created table then has all of the necessary properties to be part of the other table if swapped with the source partition in an exchange operation.