• KSII Transactions on Internet and Information Systems
    Monthly Online Journal (eISSN: 1976-7277)

MLPPI Wizard: An Automated Multi-level Partitioning Tool on Analytical Workloads


Abstract

An important technique used by database administrators (DBAs) is to improve performance in decision-support workloads associated with a Star schema is multi-level partitioning. Queries will then benefit from performance improvements via partition elimination, due to constraints on queries expressed on the dimension tables. As the task of multi-level partitioning can be overwhelming for a DBA we are proposing a wizard that facilitates the task by calculating a partitioning scheme for a particular workload. The system resides completely on a client and interacts with the costing estimation subsystem of the query optimizer via an API over the network, thereby eliminating any need to make changes to the optimizer. In addition, since only cost estimates are needed the wizard overhead is very low. By using a greedy algorithm for search space enumeration over the query predicates in the workload the wizard is efficient with worst-case polynomial complexity. The technology proposed can be applied to any clustering or partitioning scheme in any database management system that provides an interface to the query optimizer. Applied to the Teradata database the technology provides recommendations that outperform a human expert’s solution as measured by the total execution time of the workload. We also demonstrate the scalability of our approach when the fact table (and workload) size increases.


Statistics

Show / Hide Statistics

Statistics (Cumulative Counts from December 1st, 2015)
Multiple requests among the same browser session are counted as one view.
If you mouse over a chart, the values of data points will be shown.


Cite this article

[IEEE Style]
Young-Kyoon Suh, Alain Crolotte and Pekka Kostamaa, "MLPPI Wizard: An Automated Multi-level Partitioning Tool on Analytical Workloads," KSII Transactions on Internet and Information Systems, vol. 12, no. 4, pp. 1693-1713, 2018. DOI: 10.3837/tiis.2018.04.016

[ACM Style]
Suh, Y., Crolotte, A., and Kostamaa, P. 2018. MLPPI Wizard: An Automated Multi-level Partitioning Tool on Analytical Workloads. KSII Transactions on Internet and Information Systems, 12, 4, (2018), 1693-1713. DOI: 10.3837/tiis.2018.04.016