Database Spool Column Partitioning for Query Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Large relational databases with column-partitioned tables face performance degradation during queries involving many columns, as well as inefficiencies in storage and retrieval due to the nature of column-partitioned tables, which are better suited for append operations rather than insert, delete, and update operations.

Innovation Solution

The system partitions at least one column of the spool based on the database query received from the client computer system, optimizing query performance by reducing spool size and input/output costs through column-partitioning, which mitigates the disadvantages of column-store tables.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of energy

If column-partitioned tables are used, then input/output is reduced and compression works well, but query performance degrades when involving many columns

Engineering Contradiction:

Improveinput/output costVSAvoidquery performance

Core Design Contradiction:

Loss of energyVSProductivity

Solution Approach 1:

The patent segments the spool data into multiple column partitions, where each partition contains data for a specific column. This segmentation allows the system to retrieve only the necessary column partitions for a given query, rather than reading all columns, thus reducing I/O costs while maintaining query performance even when many columns are involved.

Inventive Principle:

Principle #1Segmentation

2Quantity of substance

If column-partitioned tables are used, then compression efficiency improves, but the table structure becomes less suitable for insert, delete, and update operations

Engineering Contradiction:

Improvecompression efficiencyVSAvoidinsert, delete, and update operations

Core Design Contradiction:

Quantity of substanceVSEase of operation

Solution Approach 1:

The patent introduces a spool as an intermediary structure between the column-partitioned table and the query processing system. The spool temporarily stores data in a format optimized for query execution, allowing efficient compression and selective column access. This intermediary layer handles the complexity of column-partitioned operations, making insert, delete, and update operations more manageable while preserving compression efficiency.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Reliability

If all rows and columns are scanned in a large table, then complete data access is achieved, but time consumption increases and computing resource load becomes unacceptable

Engineering Contradiction:

Improvedata access completenessVSAvoidscanning time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent applies local quality by organizing spool data into column partitions with specific characteristics optimized for different query types. Each column partition is structured and stored according to its data type and access patterns, allowing the system to efficiently retrieve only the relevant local portions (specific column partitions) needed for a query, rather than scanning the entire table, thus reducing scanning time while ensuring complete access to required data.

Inventive Principle:

Principle #3Local quality

Data Source

PatentUS9177024B2System, method, and computer-readable medium for optimizing database queries which use spools during query execution

Publication Date: 2015.11.03 TERADATA CORP

AI summary

A database system comprises a processing module, and a storage device communicatively coupled with the processing module and allocated thereto. The storage device stores a base table allocated to the processing module. The processing module receives a database query from a client computer system, and generates a spool based upon a number of columns of a table, and partitions at least one column of the spool based upon the database query received from the client computer system.