Query Execution for Pivot BLOB Data in DBMS
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database management systems, performing pivot calculations on large datasets is inefficient due to the need for complex SQL modifications and slow data retrieval times, especially when dealing with binary large object (BLOB) data types, which are stored separately from relational tables.
Innovation Solution
A computer program and apparatus that execute queries by receiving a query statement for BLOB data, determining column name and storage type information from pivot name and meta tables, and generating output information for BLOB data, allowing efficient storage, querying, and output of pivot calculation results in a BLOB type.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If pivot calculation is performed in the database management system, then the schema is extended by performing aggregation query, but multiple pivot columns are additionally automatically generated causing difficulty in SQL modification
Solution Approach 1:
The patent extracts the pivot calculation results from multiple separate pivot columns and consolidates them into a single BLOB column. This extraction of the core data from the complex multi-column structure allows the system to maintain schema extension capability while simplifying the storage structure, thereby reducing SQL modification complexity for users.
Solution Approach 2:
The patent changes the data type parameter of the pivot column from traditional relational columns to BLOB (Binary Large Object) type. This parameter change allows the system to store complex pivot results in a single column with simplified data type, maintaining versatility while reducing the operational complexity of SQL statements.
2Quantity of substance
If large capacity data is stored in the database, then the data storage capacity is increased, but relatively long time is required for performing query
Solution Approach 1:
The patent extracts only the necessary metadata information (column names, data types, pivot configuration) from the complex BLOB data and stores it in separate metadata tables. This extraction allows the system to handle large capacity data efficiently by separating the bulk data storage from the query-critical metadata, thereby improving query execution speed without sacrificing storage capacity.
Solution Approach 2:
The patent introduces metadata tables as an intermediary layer between the BLOB storage and the query processing system. These metadata tables contain essential information about the BLOB data structure, allowing the database management system to quickly locate and interpret pivot data without scanning the entire BLOB content, thus improving query productivity while maintaining large storage capacity.
3Quantity of substance
If LOB or BLOB data type is used to overcome limit for excessively large capacity, then the storage capacity limit is overcome, but LOB or BLOB data cannot be stored in relational table as separate file type
Solution Approach 1:
The patent merges the advantages of both relational tables and BLOB storage by combining them into a unified structure. The pivot results are stored as BLOB data within the relational table framework, allowing the system to overcome storage capacity limits while maintaining the structured organization and query capabilities of relational databases, thereby reducing overall system complexity.
Solution Approach 2:
The patent implements a nested structure where BLOB data is embedded within the relational table schema. The pivot results are nested as BLOB content within table columns, while metadata about the nested BLOB data is stored in separate tables. This nesting allows the system to handle large capacity data within the relational framework without requiring separate file storage, simplifying the overall data storage structure.
Data Source
AI summary
Provided is a method for executing a query in a DBMS. The method may include: receiving a query statement for requesting BLOB data serialized to a binary large object (BLOB) type and stored in a pivot table; determining name information for one or more columns generated as a result of a pivot operation included in respective pivot columns of the pivot table by querying a pivot name table for the pivot table; determining internal storage type information of the pivot table by querying a pivot meta table for the pivot table; and generating output information for the BLOB data corresponding to the query statement based on the determined name information and internal storage type information.