Hybrid Database Table Row Column Store Segmentation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional databases are limited to either row-store or column-store formats, which restrict their ability to handle mixed queries and lead to inefficiencies in memory usage and performance, especially when handling both transactional and analytical queries.

Innovation Solution

A hybrid database table is stored as both a row and a column store, with techniques such as Bloom filters and data movement policies to optimize access and size of processing-intensive column store data, allowing for efficient handling of mixed queries and improved memory management.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If a database uses row-oriented storage model, then transactional query performance is improved, but analytical query performance deteriorates due to memory intensity and cache misses

Engineering Contradiction:

Improvetransactional query performanceVSAvoidmemory consumption

Core Design Contradiction:

ProductivityVSUse of energy by moving object

Solution Approach 1:

The database table is segmented into two separate storage models: a row-store portion for transactional operations and a column-store portion for analytical operations. This segmentation allows each portion to be optimized for its specific workload, resolving the contradiction between transactional performance and memory consumption for analytical queries.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The hybrid table structure provides multi-functionality by supporting both transactional queries (via row-store) and analytical queries (via column-store) within a single database table. This universal design eliminates the need to choose between storage models based on workload type.

Inventive Principle:

Principle #6Universality (Multi-functionality)

2Productivity

If a database uses column-oriented storage model, then analytical query performance is improved, but transactional query performance deteriorates due to inability to efficiently access individual records

Engineering Contradiction:

Improveanalytical query performanceVSAvoidrecord access efficiency

Core Design Contradiction:

ProductivityVSEase of operation

Solution Approach 1:

The table is divided into distinct row-store and column-store portions, allowing efficient record access through the row-store portion for transactional operations while maintaining analytical capabilities through the column-store portion.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The hybrid structure enables the database to handle both transactional operations (requiring fast record access) and analytical operations (requiring efficient aggregation) simultaneously, making the system universally applicable to different query types.

Inventive Principle:

Principle #6Universality (Multi-functionality)

3Adaptability or versatility

If a database stores all data in row-formatted storage model, then scalability for data storage is improved, but memory consumption for analytical queries increases

Engineering Contradiction:

Improvedata storage scalabilityVSAvoidmemory consumption

Core Design Contradiction:

Adaptability or versatilityVSUse of energy by moving object

Solution Approach 1:

The database segments data into row-store and column-store portions, allowing the row-store portion to handle scalability requirements while the column-store portion optimizes memory usage for analytical queries through compression and selective data access.

Inventive Principle:

Principle #1Segmentation

4Adaptability or versatility

If a database stores all data in column-formatted storage model, then flexibility for data manipulation is improved, but readiness for transactional operations deteriorates

Engineering Contradiction:

Improvedata manipulation flexibilityVSAvoidtransactional operation speed

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The table structure segments data to provide both row-store capabilities for fast transactional operations and column-store capabilities for flexible data manipulation and analytical queries, resolving the contradiction between operation speed and manipulation flexibility.

Inventive Principle:

Principle #1Segmentation

Data Source

PatentUS8768927B2Hybrid database table stored as both row and column store

Publication Date: 2014.07.01 SAP SE

AI summary

A hybrid database table is stored as both a row and a column store. One or more techniques may be employed alone or in combination to enhance performance of the hybrid table by regulating access to, and/or the size of, the processing-intensive column store data. For example during an insert operation, the column store data may be searched for a uniqueness violation only after certain filtering and/or boundary conditions have been considered. In another technique, a hybrid table manager may control movement of data to the column store based upon considerations such as frequency of access, or underlying business logic. In still another technique, querying of the hybrid table may result in a search of the column store data only after an initial search of row store data fails to return a result.