A PIVOT based Query Optimization Technique for Horizontal View Tables in Relational Databases


The KIPS Transactions:PartD, Vol. 14, No. 2, pp. 157-168, Apr. 2007
10.3745/KIPSTD.2007.14.2.157,   PDF Download:

Abstract

For effective analyses in various business applications, OLAP(On-Line Analytical Processing) systems represent the multidimensional data as the horizontal format of tables whose columns are corresponding to values of dimension attributes. Because the traditional RDBMSs have the limitation on the maximum number of attributes in table columns(MS SQLServer and Oracle permit each table to haveup to 1,024 columns), horizontal tables cannot be directly stored into relational database systems. In this paper, we propose various efficient optimization strategies in transforming horizontal queries to equivalent vertical queries. To achieve this goral, we first store ahorizontal table using an equivalent vertical table, and then develop various query transformation rules for horizontal table queries using the PIVOT operator. In particular, we propose various alternative query transformation rules for the basic relational operators, selection, projection, and join. Here, we note that the transformed queries can be executed in several ways, and their execution times will differ from each other. Thus, we propose various optimization strategies that transform the horizontal queries to the equivalent vertical querieswhen using the PIVOT operator. Finally, we evaluate these methods through extensive experiments and identify the optimal transformation strategy when using the PIVOT operator.


Statistics
Show / Hide Statistics

Statistics (Cumulative Counts from September 1st, 2017)
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]
S. H. Shin, Y. S. Moon, J. H. Kim, G. M. Kang, "A PIVOT based Query Optimization Technique for Horizontal View Tables in Relational Databases," The KIPS Transactions:PartD, vol. 14, no. 2, pp. 157-168, 2007. DOI: 10.3745/KIPSTD.2007.14.2.157.

[ACM Style]
Sung Hyun Shin, Yang Sae Moon, Jin Ho Kim, and Gong Mi Kang. 2007. A PIVOT based Query Optimization Technique for Horizontal View Tables in Relational Databases. The KIPS Transactions:PartD, 14, 2, (2007), 157-168. DOI: 10.3745/KIPSTD.2007.14.2.157.