Database Pivot Query Execution via BLOB Serialization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database management systems, performing pivot calculations is inefficient due to the need for complex SQL modifications and slow data retrieval times, especially when dealing with large datasets stored as BLOBs, where metadata is stored in relational tables but the actual data is stored separately, making it difficult to manage and query pivot columns effectively.
Innovation Solution
A method that serializes the execution result of pivot operations into BLOB type data and stores it in a pivot table, allowing for efficient storage, querying, and output of pivot calculations by generating BLOB data from the execution result and storing it in a single pivot column, thereby simplifying the management of pivot columns.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If pivot calculation is performed in traditional DBMS, then schema extension is achieved, but SQL statement becomes complicated and operation becomes difficult
Solution Approach 1:
The patent extracts the pivot calculation result as a separate BLOB column from the main table structure. By taking out the pivot operation result into a distinct storage location, the original SQL statement remains simple while the complex pivot calculation is performed separately and stored as binary large object data, thus resolving the contradiction between schema extension capability and SQL statement simplicity
Solution Approach 2:
The patent introduces a BLOB (Binary Large Object) as an intermediary storage mechanism between the pivot calculation process and the final result presentation. This intermediary allows the database system to perform complex pivot operations internally while presenting simplified access methods to users, thereby reducing SQL statement complexity while maintaining schema extension versatility
2Quantity of substance
If LOB or BLOB data is stored outside the table as separate file, then data capacity limit is overcome, but data management and querying becomes difficult
Solution Approach 1:
The patent merges the pivot calculation result with the main table structure by storing the BLOB data in a column within the same table. This combining approach allows the system to overcome data capacity limits while maintaining easy data management and querying, as the BLOB data is accessed through standard SQL operations on the same table rather than requiring separate file operations
Solution Approach 2:
The patent makes the table structure universal by allowing it to handle both regular data and pivot calculation results through a unified schema. The table can store both original data and BLOB pivot results in the same structure, providing multi-functionality that simplifies data management while supporting large data capacities
3Adaptability or versatility
If multiple pivot columns are generated, then aggregation query capability is enhanced, but SQL modification becomes necessary and operation becomes complicated
Solution Approach 1:
The patent extracts the pivot operation from the standard SQL query process and implements it as a separate database operation. By taking out the pivot calculation as a distinct operation that returns BLOB data, the system enhances aggregation query capability while avoiding the need for complex SQL modifications, as the pivot logic is handled at the database level rather than requiring user-written SQL complexity
Data Source
AI summary
Provided is a method for executing a query in a DBMS. The method may include: receiving a query statement including a pivot operator--the query statement being associated with an original table; executing a pivot operation for the original table in response to reception of the query statement; generating BLOB data by serializing at least a part of an execution result of the pivot operation to a BLOB type; and storing the generated BLOB data in a pivot table.