Database Query Execution Plan Table Ordering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in query execution, particularly in JOIN processes, due to the lack of consideration for user access privileges when ordering tables, leading to suboptimal performance and increased latency.
Innovation Solution
The system generates an execution plan for database queries by establishing an ordering of tables based on user access privileges, using adjusted table sizes that account for differing access rights, ensuring that smaller tables are processed before larger ones in JOIN operations, thereby optimizing query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If tables are ordered by their actual size in JOIN processes, then the JOIN operation can proceed efficiently, but user access privileges are not considered leading to suboptimal performance
Solution Approach 1:
The patent applies local quality by creating user-specific virtual table sizes that differ from the actual table sizes. Each user sees a customized table size based on their access privileges, allowing the query optimizer to generate personalized execution plans that account for individual user permissions while maintaining efficient JOIN operations.
Solution Approach 2:
The patent changes the parameter used for table ordering from actual table size to adjusted table size that incorporates user access privileges. By modifying this key parameter in the query optimization process, the system can adapt table ordering to individual user contexts, improving both productivity and adaptability simultaneously.
2Loss of time
If the system generates execution plans without considering user access privileges, then the planning process is simpler and faster, but query processing time increases due to suboptimal table ordering
Solution Approach 1:
The patent applies preliminary action by pre-calculating adjusted table sizes and storing them in system catalogs before query execution. This allows the query optimizer to directly use these pre-computed values during execution plan generation, avoiding complex real-time calculations and reducing query processing time while maintaining the capability to consider user access privileges.
Solution Approach 2:
The patent introduces an intermediary component (the row security manager and system catalog) that mediates between user access privileges and query execution planning. This intermediary pre-processes privilege information and presents it to the query optimizer in a usable format, reducing the complexity of the planning process while still incorporating privilege considerations.
3Productivity
If the system uses actual table sizes for JOIN ordering, then the execution plan generation is straightforward, but performance suffers when users have limited access to certain rows
Solution Approach 1:
The patent applies local quality by creating user-specific virtual table sizes that reflect the actual data visible to each user based on their privileges. This allows the query optimizer to generate execution plans that are both efficient and accurate for each user's specific context, rather than using a single generic table size that may not reflect what the user actually accesses.
Solution Approach 2:
The patent changes the table size parameter from a fixed actual size to a dynamic adjusted size that varies by user. This parameter change enables the system to maintain both high productivity and reliability by adapting the table size parameter to match the user's actual data access patterns.
Data Source
AI summary
Methods, computer program products, and systems are presented. The method computer program products, and systems can include, for instance: identifying an invoked database query for execution on a database, the invoked database query being associated to a user; generating an execution plan for executing the database query on the database; wherein the generating the execution plan for execution of the database query on the database includes establishing an ordering of first and second tables, the ordering of the first and second tables being in dependence on an access privilege attribute of the user in respect to the first table; and executing the database query according to the execution plan.