Database Column Data Type Conversion Without Replacement Tables

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database conversion methods, such as offline and online replacement-table conversion, face significant downtime and resource constraints when converting data types in large database tables, especially when multiple tables need to be converted simultaneously, due to the need for creating and synchronizing replacement tables and logs.

Innovation Solution

The method involves creating a replacement column in the table with the new data type and using a background process to translate values from the old data type to the new data type, allowing user transactions to update values in both columns concurrently, eliminating the need for a replacement table and reducing resource usage by avoiding synchronization and CPU-intensive copying processes.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If offline conversion is used to convert data type of a table column, then data integrity and consistency are ensured, but significant downtime occurs and user access is blocked

Engineering Contradiction:

Improvedata integrityVSAvoiddowntime

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The conversion process is segmented into two independent parts: (1) creating a new column with the target data type while keeping the original column intact, and (2) copying and converting data from the original column to the new column. This segmentation allows the original column to remain accessible for user transactions while the conversion happens in the background, thus maintaining data integrity without causing significant downtime.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The new column is created in advance with the correct data type definition before any data conversion occurs. This preliminary setup allows the system to prepare the target structure while the original data remains unchanged and accessible, enabling concurrent user access during the subsequent data copying and conversion phase.

Inventive Principle:

Principle #10Preliminary action

2Ease of operation

If online replacement-table conversion is used to convert data type of a table column, then user access is maintained during conversion, but significant CPU and storage resources are consumed

Engineering Contradiction:

Improveuser access continuityVSAvoidCPU resource usage

Core Design Contradiction:

Ease of operationVSUse of energy by moving object

Solution Approach 1:

The invention extracts only the necessary data from the original table - specifically, only the values from the column being converted are copied to the new column. This is in contrast to replacement-table conversion which copies entire rows including all other columns. By extracting only the essential data for conversion, CPU overhead is significantly reduced while user access to other columns remains unaffected.

Inventive Principle:

Principle #2Taking out (Extraction)

3Ease of operation

If online replacement-table conversion is used to convert data type of a table column, then user access is maintained during conversion, but substantial storage space is required for replacement tables and logs

Engineering Contradiction:

Improveuser access continuityVSAvoidstorage space

Core Design Contradiction:

Ease of operationVSQuantity of substance

Solution Approach 1:

The invention extracts and stores only the converted values in the new column within the same table structure, rather than creating a complete replacement table that would require duplicating all other columns and their data. This selective extraction approach minimizes storage overhead to only the necessary converted data plus minimal transaction logging, compared to the substantial storage requirements of full replacement tables.

Inventive Principle:

Principle #2Taking out (Extraction)

4Productivity

If multiple tables are converted simultaneously using replacement-table conversion, then conversion parallelism is achieved, but resource consumption increases significantly

Engineering Contradiction:

Improveconversion parallelismVSAvoidCPU and storage resource usage

Core Design Contradiction:

ProductivityVSUse of energy by moving object

Solution Approach 1:

The conversion process for multiple tables is segmented at the column level rather than requiring full table replacement. Each column conversion is an independent operation that creates only a new column in the target table, not a complete replacement table. This segmentation allows multiple column conversions across multiple tables to proceed in parallel with minimal resource contention, as each operation only touches its specific column data rather than entire tables.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS8935294B2Minimizing computer resource usage when converting data types of a table column

Publication Date: 2015.01.13 ORACLE INT CORP

AI summary

Online conversion procedures avoid the need to generate a copy of an entire table in order to convert the data type of a column in the table. To convert a table's column to a new data type, a replacement column is created in the table, the replacement column having the new data type. A background process dedicated to converting the column translates values in the column of the old data type to the new data type and stores the converted values in the replacement column. In addition, as transactions of users are applied to rows in the table, the value in the column of the old data type in the rows is converted and stored in the replacement column. Once the background process completes, the replacement column is used in lieu of the converted column.