Database Memory Allocation for Large Data Retrieval
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in storage allocation when retrieving data of indeterminate size, particularly for LOB and XML data types, due to the need for maximum storage allocation and the use of LOB locators, which can lead to performance issues and resource consumption.
Innovation Solution
The introduction of extensions to the SQL FETCH statement, including a 'CONTINUE' operation, allows for dynamic allocation of memory based on actual data length, enabling retrieval of data in smaller increments and avoiding the use of LOB locators, with the database maintaining the position of truncated data for subsequent fetches.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If maximum storage size is allocated for LOB columns to ensure sufficient space for retrieval, then data retrieval capability is improved, but memory consumption increases significantly
Solution Approach 1:
The patent segments the data retrieval process into multiple FETCH operations. Instead of allocating maximum storage upfront, the system performs initial FETCH with a reasonable buffer size, checks for truncation indicators, and subsequently performs additional FETCH operations to retrieve the remaining data in segments, thereby avoiding excessive memory allocation while ensuring complete data retrieval
Solution Approach 2:
The patent implements dynamic buffer allocation by allowing the application to start with an initial buffer size and then allocate additional buffer space based on actual data needs. The system dynamically adjusts memory usage by performing subsequent FETCH operations only when truncation is detected, making the memory consumption adaptive rather than static
2Quantity of substance
If LOB locators are used to manage large object retrieval, then storage allocation efficiency is improved, but system complexity and resource management overhead increase
Solution Approach 1:
The patent extracts the complexity of LOB locator management from the system by eliminating the need for locators entirely. Instead of using locator objects to manage large object retrieval, the system uses direct FETCH operations with truncation indicators, thereby maintaining storage allocation efficiency while removing the associated complexity and resource management overhead
Solution Approach 2:
The patent enables the FETCH operation itself to provide the necessary information about data size and truncation status through indicators returned with each FETCH. This self-service mechanism eliminates the need for separate locator objects to manage retrieval, as the FETCH operation directly provides all necessary control information
3Quantity of substance
If multiple FETCH operations are performed to retrieve truncated data, then memory allocation efficiency is improved, but the number of database trips increases
Solution Approach 1:
The patent performs preliminary action by including truncation indicators with the initial FETCH operation. This allows the application to determine in advance whether additional FETCH operations are needed, avoiding unnecessary database trips. The truncation indicator provides upfront information that guides subsequent retrieval actions
4Ease of manufacture
If a fixed buffer size is used for FETCH operations, then allocation simplicity is improved, but adaptability to varying data lengths deteriorates
Solution Approach 1:
The patent implements dynamic buffer allocation by allowing the application to start with an initial buffer size and then allocate additional buffer space based on actual data needs. The system dynamically adjusts memory usage by performing subsequent FETCH operations only when truncation is detected, making the memory consumption adaptive rather than static
Data Source
AI summary
A method and system for efficient allocation of memory in database retrieval employs a technique which allocates a memory portion of a first size which may or may not be smaller than a data item to be retrieved from the database (such as a column which may be of indeterminate length). In that circumstance, a first FETCH, for example, an SQL FETCH, is followed by a new syntactical instruction, a FETCH CONTINUE, until all of the data item is retrieved. The FETCH CONTINUE may involve allocating memory portions of similar or different sizes to accommodate additional fetched portions, and may involve varying numbers of fetches.