Database Server Outer Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Databases struggle to efficiently optimize outer joins due to unknown cardinality, leading to unnecessary processing resource consumption and slower query response times, especially in column-oriented databases where joins cannot be completely removed if no columns are requested from the outer table.
Innovation Solution
A database server optimizes calculation scenarios by removing unnecessary join operations and associated branches, utilizing cardinality information for join operations, and filtering or sorting results, with a calculation engine interacting with a physical table pool and logical layer to execute optimized data flows.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If outer join operations are performed in column-oriented databases, then data enrichment from separate tables is achieved, but processing resource consumption increases and query response time slows down
Solution Approach 1:
The patent applies preliminary action by performing join optimization during the query planning phase before actual execution. The system analyzes the calculation scenario, identifies outer join operations, determines their cardinality, and removes unnecessary joins in advance. This pre-processing eliminates redundant operations that would otherwise consume processing resources during query execution, thereby resolving the contradiction between data enrichment capability and query execution efficiency
2Productivity
If cardinality information is used to remove unnecessary join operations, then processing resource consumption decreases, but the complexity of the optimization process increases
Solution Approach 1:
The patent applies self-service by enabling the query optimization system to automatically determine join cardinality and identify removable outer joins without requiring manual intervention. The system self-evaluates the calculation scenario, uses cardinality information to make optimization decisions, and automatically removes unnecessary joins. This automation reduces the apparent complexity for users while maintaining high processing efficiency, resolving the contradiction between productivity improvement and optimization complexity
Data Source
AI summary
A database server receives a query from a remote application server that is associated with a calculation scenario. The calculation scenario defines a data flow model that includes one or more calculation nodes that each define one or more operations to execute on the database server. The database server can be a column-oriented database and at least one of the calculation nodes comprises at least one join operation. Thereafter, the database server instantiates the calculation scenario so that it is optimized by removing one or more join operations and associated branches within the calculation scenario. The database server then executes the operations defined by the calculation nodes of the optimized calculation scenario to result in a responsive data set. The database server then provides the data set to the application server.