Value-Based LOB Access in RDBMS SQL
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database management systems (RDBMS) face inefficiencies when accessing Medium Object (MOB) data stored in Large Object (LOB) data type columns, particularly due to the need for resource-intensive lob locators and potential memory leaks, which are cumbersome and lead to performance issues similar to those experienced with NoSQL applications.
Innovation Solution
The approach involves allowing clients to specify Value based semantics in SQL statements or DDL statements to retrieve LOB data directly, rather than through lob locators, enabling efficient access and garbage collection of temporary LOB data, thus optimizing performance and preventing memory leaks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If Reference based access is used for LOB data, then the system can handle large data sizes (megabytes to terabytes), but the access becomes resource-intensive and cumbersome due to lob locators
Solution Approach 1:
The patent introduces a new parameter 'value lob flag' that can be set through SQL operator tree propagation to change the access mode from Reference based to Value based. This flag propagation mechanism allows the system to switch between different access semantics without changing the underlying LOB storage structure, thereby maintaining large data capacity while simplifying access for medium-sized objects.
2Quantity of substance
If lob locators are used for accessing LOB columns, then large data can be accessed, but memory leaks occur when clients forget to close locators properly
Solution Approach 1:
The patent implements automatic garbage collection of temporary LOB data through the Value based access mode. When value lob flag is set, the system automatically manages the lifecycle of temporary LOB data in temporary memory, eliminating the need for manual locator closing by clients. This self-service mechanism prevents memory leaks while maintaining the ability to handle large data sizes.
3Productivity
If Value based access is used for LOB data, then fetch performance is comparable to VARCHAR/raw types, but the system must determine whether to return value or reference based on multiple conditions
Solution Approach 1:
The patent performs preliminary determination of access mode during query optimization by propagating the value lob flag through the SQL operator tree. The system evaluates conditions (data dictionary properties, SQL functions, flags) in advance and sets the appropriate access mode before execution. This preliminary action eliminates runtime complexity and enables high-performance Value based access when conditions are met.
4Ease of operation
If clients use Reference based semantics for LOBs, then they can access LOB data, but the process is expensive and cumbersome compared to NoSQL applications
Solution Approach 1:
The patent introduces dynamic access semantics that can switch between Reference based and Value based modes depending on the MOB use case requirements. The value lob flag enables the system to adaptively choose the most efficient access method, providing NoSQL-like simplicity for medium-sized objects while maintaining traditional LOB capabilities for larger data, thereby reducing access overhead and improving ease of operation.
Data Source
AI summary
Described are improved systems, computer program products, and methods for an improved approach to access small to medium size objects (MOBs) stored in LOB data type columns of a RDBMS. The approach includes receiving a SQL statement comprising a retrieval of a large object (LOB). The approach also includes determining whether to return a value of the LOB or a reference to a storage location storing the value of the LOB based on: a data dictionary property of the LOB to return the value of the LOB, a function included in the SQL statement to return the value of the LOB, or a flag derived from a SQL operator tree propagation to return the value of the LOB.