Persistent Table Dependency Analysis for Database Scale-Out
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The SQL Plan Cache in database systems like HANA is volatile, leading to loss of relevant information about table connections when the database restarts, making it challenging to distribute tables meaningfully across nodes in a scale-out system to minimize cross-node communication.
Innovation Solution
A computer-implemented method for analyzing object dependencies, which selects base objects and their dependent objects, groups them based on dependencies, and stores these groups to persist information about connections, allowing for reliable distribution of tables across nodes in a database system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If SQL Plan Cache is used to track table connections, then query execution can be optimized, but the information is lost when database restarts due to volatility
Solution Approach 1:
The system performs preliminary analysis of table dependencies and stores them in persistent storage before database restarts. This allows the optimization information to be preserved and reused after restart, eliminating the need to re-analyze dependencies and ensuring both productivity and reliability.
Solution Approach 2:
The invention creates a copy of the table dependency information from the volatile SQL Plan Cache and stores it in persistent storage. This copy survives database restarts, allowing the system to recover optimization information without relying on the volatile cache, thus resolving the contradiction between execution efficiency and information persistence.
2Productivity
If tables are distributed across multiple nodes in scale-out system, then processing capacity increases, but cross-node communication overhead increases
Solution Approach 1:
The system segments tables into groups based on their dependency relationships, ensuring that highly interconnected tables are kept together on the same node. This segmentation minimizes cross-node communication while maintaining the benefits of distributed processing, as queries accessing related tables can be handled locally within a node.
Solution Approach 2:
The invention applies local quality by keeping tables with strong dependency relationships on the same node, creating localized data groups. This reduces the need for cross-node communication for queries accessing these tables, thereby reducing communication overhead while maintaining distributed processing capacity across the system.
3Ease of manufacture
If statement cache is used for grouping tables, then distribution can be performed, but the volatile nature prevents reliable information about table connections
Solution Approach 1:
The system performs preliminary extraction and storage of table connection information from the statement cache before it becomes volatile. By persisting this information in advance, the system maintains the ability to perform reliable table distribution even after database restarts when the statement cache is cleared.
Solution Approach 2:
The invention creates a persistent copy of the table connection information that would otherwise be stored only in the volatile statement cache. This copy ensures that table distribution can be performed reliably based on accurate connection information, preventing information loss while maintaining distribution capability.
Data Source
AI summary
A method, a system, and a computer program product for performing analysis of object dependencies. A first base object is selected from a plurality of base objects. At least one first dependent object in a plurality of dependent objects depends on the selected first base object. A group of base objects that includes the selected first base object is generated. A second base object in the plurality of base objects is determined. The first dependent object is dependent on the second base object. The determined second base object is grouped with the generated group of base objects. At least one second dependent object in the plurality of dependent objects and its corresponding base object are determined. The corresponding base object is grouped with the generated group of base objects. The generated group of base objects is stored.