Multi-tenant Database Query Optimization via Tenant-Specific Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant database systems, existing query optimization methods are inefficient due to reliance on global database statistics that fail to accurately characterize tenants with unique data traits, such as complex data, large volumes, or high transaction rates, leading to suboptimal query plans.
Innovation Solution
The method involves retrieving actual statistics from a multi-tenant database system, selecting a subset related to tenants with targeted data traits, calculating synthetic statistics, and determining an optimal query plan based on these statistics to improve query efficiency for tenants with specific data characteristics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If global database statistics are used for query optimization, then the optimization process is simple and fast, but the query plans are suboptimal for tenants with unique data traits
Solution Approach 1:
The patent segments the database statistics into global statistics and tenant-specific statistics. The query optimizer selectively uses tenant-specific statistics when available to generate accurate query plans for tenants with unique data traits, while falling back to global statistics when tenant-specific statistics are not available. This segmentation resolves the contradiction by providing both simplicity (using global stats when needed) and precision (using tenant-specific stats when available).
Solution Approach 2:
The patent applies local quality by maintaining different levels of statistical accuracy for different tenants. Tenants with unique data traits (such as complex data structures, large volumes, or high transaction rates) receive customized query plans based on their specific statistics, while other tenants use the general global statistics. This ensures that each tenant gets the appropriate level of optimization precision matching their data characteristics.
2Measurement precision
If tenant-specific statistics are collected and maintained for all tenants, then query optimization accuracy is improved, but system complexity and resource consumption increase
Solution Approach 1:
The patent implements preliminary action by proactively collecting and maintaining statistics for tenants identified as having unique data traits, rather than waiting for query optimization needs to arise. The system identifies tenants with characteristics such as complex data, large volumes, or high transaction rates and pre-computes their statistics, so when queries need optimization, accurate tenant-specific statistics are already available, reducing system complexity at the moment of query execution.
Solution Approach 2:
The patent changes the parameter of statistical collection from a binary approach (either collect all tenant statistics or none) to a selective approach based on tenant characteristics. By monitoring tenant data traits and only maintaining detailed statistics for tenants who exhibit unique patterns (high transaction volumes, complex data structures), the system achieves high query optimization accuracy while avoiding the complexity overhead of maintaining statistics for all tenants uniformly.
3Speed
If synthetic statistics are calculated from a subset of actual statistics, then query optimization is faster, but the statistics may not accurately represent all tenants
Solution Approach 1:
The patent extracts only the necessary subset of statistics from the full set of actual tenant statistics, focusing on the most relevant metrics for query optimization (such as data volume, transaction frequency, and data trait characteristics). By extracting and using only this critical subset to generate synthetic statistics, the system achieves fast query plan determination while maintaining sufficient reliability for tenants with similar data patterns.
Solution Approach 2:
The patent creates synthetic statistics as simplified copies or representations of the full tenant statistics. These synthetic statistics capture the essential characteristics needed for query optimization without replicating the complete statistical profile. For tenants with similar data traits, the synthetic statistics derived from a subset of actual statistics provide reliable approximations that enable fast query plan generation while maintaining adequate representativeness.
Data Source
AI summary
Techniques and structures for tuning database queries. Actual statistics associated with data stored are retrieved from at least one memory device. The data is associated with one or more tenants of the multi-tenant database system. The actual statistics include at least one type of statistic maintained for the data. A subset of the actual statistics is selected based on a data trait targeted for optimization. A statistical value is calculated for each actual statistic from the subset of actual statistics across one or more tenants having the data trait targeted for optimization. An optimal query plan is determined based on the original query and the calculated statistical values.