Column-Based Database Table Segmentation for Unsupported Data Types
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Column-oriented databases often fail to support all data types supported by row-oriented databases, limiting the use of unsupported data types like spatial data, XML data, and JSON data in online analytical processing (OLAP) systems, which hampers efficient data management and resource utilization.
Innovation Solution
A method and system that generates a base table in a secondary column-oriented database as a copy of the primary database without unsupported data types and creates an additional table linked via a row-identifier relationship to store and access unsupported data types, enabling seamless integration and query access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is copied from row-oriented primary database to column-oriented secondary database, then OLAP query performance is improved, but unsupported data types (spatial, XML, JSON) cannot be stored in the secondary database
Solution Approach 1:
The patent segments the storage of data types by creating separate tables: a base table for supported data types and additional tables for unsupported data types (spatial, XML, JSON). This segmentation allows the column-oriented database to maintain its performance benefits for analytical queries while preserving support for specialized data types through dedicated storage structures.
Solution Approach 2:
The patent introduces an intermediary mechanism (additional tables with row-identifier relationships) that mediates between the column-oriented storage system and unsupported data types. These intermediary tables act as a bridge, allowing the secondary database to access and manage spatial, XML, and JSON data without compromising the core column-oriented architecture's efficiency.
2Adaptability or versatility
If all data types are stored in the primary row-oriented database, then data type versatility is maintained, but OLAP analytical processing efficiency is reduced
Solution Approach 1:
The patent segments data storage into two distinct pathways: hot data (frequently accessed analytical data) is stored in the column-oriented secondary database for efficient OLAP processing, while unsupported data types are segregated into additional tables. This segmentation enables the system to optimize for analytical performance while maintaining comprehensive data type support.
Solution Approach 2:
The patent adds a new dimension to data storage by creating a multi-table structure with row-identifier relationships. This dimensional approach allows the system to organize data not just by type, but by access patterns and storage requirements, enabling efficient OLAP queries on supported data types while preserving access to unsupported types through the relational linkage.
3Device complexity
If a unified database structure is used for both OLTP and OLAP, then system simplicity is maintained, but performance optimization for analytical processing is limited
Solution Approach 1:
The patent segments the database into a primary row-oriented database for OLTP operations and a secondary column-oriented database for OLAP operations, with automated data copying between them. This segmentation allows each database type to be optimized for its specific workload while maintaining a unified interface for users, thus improving analytical processing speed without significantly increasing operational complexity.
Solution Approach 2:
The patent implements a copying mechanism where data is automatically replicated from the primary database to the secondary database in column-oriented format. This copying approach allows the system to maintain a simple unified structure from the user perspective while creating an optimized analytical copy in the background, thereby improving query performance without requiring users to manage complex multi-database configurations.
Data Source
AI summary
A method for processing an unsupported data type in a database is disclosed. The method for processing an unsupported data type in a database comprises detecting, while copying data from a primary to a secondary database, a table for data comprising a data type that is unsupported by the secondary database. Furthermore, the method comprises generating a base table in the secondary database as a copy of the detected table of the primary database without the data of the unsupported data type, generating an additional table in the secondary database for data of the unsupported data type, and linking the additional table to the base table in the secondary database via a row-identifier relationship. Additionally, the method comprises accessing data of the unsupported data type via the additional table while performing queries against the secondary database.