Browse > Article
http://dx.doi.org/10.3745/KIPSTD.2007.14-D.2.157

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

Shin, Sung-Hyun (한양대학교 BK21사업단 정보기술분야)
Moon, Yang-Sae (강원대학교 IT특성화학부 컴퓨터학부)
Kim, Jin-Ho (강원대학교 IT특성화학부 컴퓨터학부)
Kang, Gong-Mi (강원대학교 컴퓨터과학과)
Publication Information
The KIPS Transactions:PartD / v.14D, no.2, 2007 , pp. 157-168 More about this Journal
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 have up 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 a horizontal 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 queries when using the PIVOT operator. Finally, we evaluate these methods through extensive experiments and identify the optimal transformation strategy when using the PIVOT operator.
Keywords
Multidimensional Data; Data Warehouse; PIVOT operation; Query Optimization;
Citations & Related Records
연도 인용수 순위
  • Reference
1 L. V. S. Lakshmanan, F. Sadri, and S. N. Subramanian, 'On efficiently implementing SchemaSQL on and SQL database system,' In Proc. of 25th international Conference on Very large Data Bases (VLDB), Edinburgh, Scotland, pp. 471-482, September 7-10, 1999
2 A. Witkowski, S. Bellamkonda, T. Bozkaya, N. Folkert, A. Gupta, L. Sheng, and S. Subramanian, 'Business Modeling Using SQL Spreadsheets,' In Proc. of the 29th Int'l Conf. on Very Large Data Bases (VLDB), Berlin, Germany, pp. 1117-1120, 2003
3 Microsoft SQLServer 2005, http://www.microsofl.com/sql
4 Oracle 9i Database. http://www.oracle.com/databasc
5 C. Cunningham. G. Graefe, and C. A. Galindo legaria, 'PIVOT and UNPIVOT: Optimization and Execution Strategies in and RDBMS,' In Proceedings of VLDB, pp. 998-1009, 2001
6 S. Chen and E. A. Rundensteiner, 'GPIVOT: Efficient Incremental Maintenance of Complex ROLAP View,' In Proc. of the 21st International Conference on Data Engineering (ICDE), pp. 552-563, 2005   DOI
7 A. Witkowski, S. Bellamkonda, T. Bokaya, G. Dorman, N. Folkert. A. Gupta, L. Shen, and S. Subramanian. 'Spreadsheets in RDBMS for OLAP.' In Proc. Int'l Conf. on Management of Data, ACM SIGMOD. San Diego. CAA, pp. 52-63, 2003   DOI
8 M. Mohania, S. Samtani, J. Hoddick and Y. Kambayashi, 'Advances and Hesearch Directions in Data Warehousing Technology,' Australian Journal or Information Systems, Vol.7, No.1, pp. 41-59, 1999
9 J. Gray, S. Chaudhuri, A. Bosworth, A. Layman, D. Ikichart, H. Pirahesh, F. Pellow, and H. Pirahesh, 'Data Cube: A Relationa Aggregation Operator Generalizing Croup By, Cross Tab, and Sub Totals,' Data Mining and knowledge Discovery, Vol.1, No.1, pp. 29-53, 1997   DOI
10 R. Agrawal, A. Somani and Y. Xu, 'Storage and Querying of E-Commerce Data,' In Proc. of the 27th Int'l Conf. on Very large Data Bases (VLDB), Roma, Italy, pp. 149-458, Sept. 2001
11 S. Chaudhuri and Dayal, U., 'An Overview of Data Warehousing and Technology,' ACM SIGMOD Record, VoI.26, No.1, pp. 65-74, Mar. 1997   DOI   ScienceOn