Due to vast use of Internet data grows explosively. A query that is fired on table may require a complete table scan which can take a long time as it has to inspect every row in table. Since, there is no way to identify this problem, becomes more sever for historical tables for which many queries concentrate, access on rows that were generated recently. Partition helps to solve this problem. Partition divide table into partitions. A query that only requires rows that correspond to a single partition or range of partitions can be executed using a partition scan rather than a table scan. Here, proposed Partition Algorithm, based on Rank Partition Tree structure, (PARPT) will map rows to partitions for optimizing the query performance of Max/Min type with mass data. We will compare amount of time required to execute queries on existing Range partition method and proposed partitioning method. The experimental result shows that the implemented method to solve the specific type of queries is much more effective than Range partitioning method.

 

Published In : IJCSN Journal Volume 3, Issue 5

Date of Publication : October 2014

Pages : 292 - 297

Figures : 04

Tables : 01

Publication Link : Comparative Approaches of Query Optimization for Partitioned Tables

 

 

 

Dipmala Salunke : Computer Engineering Department, University of Pune, PICT Pune, Maharashtra, India

Girish Potdar : Computer Engineering Department, University of Pune, PICT Pune, Maharashtra, India

 

 

 

 

 

 

 

Rank Decision Tree (RDT)

Join

Range Partition Method

Query processing

Relational databases

Now days, data grows explosively due to electronic commerce and use of internet. Business decisions have relied more and more on big data. To face mass data we need to divide data into partitions to meet the request of timely response. Thus, in this paper we have proposed an effective database partition algorithm to solve max/min type of queries. Here, we proposed RPT structure based on calculated RS for an attribute. Then proposed new partition algorithm based on constructed RPT structure. Finally, we compared partition algorithm with existing Range partition method. Experimental results show that proposed method greatly improved the query efficiency of max/min type. In future, if we run proposed algorithm in distributed environment we may achieve data load balance and also come up with minimum consumption of resource as result of max/min type query is available in one partition. RPT construction can be unstable in some high dynamic environment. So, we can improve algorithm to adapt reconstruction of RPT structure.

 

 

 

 

 

 

 

 

 

[1] D. G. Shin, and K. B. Irani, “Fragmenting relations horizontally using a knowledge based approach,” IEEE Transactions on Software Engineering (TSE), Vol. 17, No. 9, pp. 872–883, 1991.

[2] S. Agrawal, V. Narasayya, and B. Yang, “Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design” ACM SIGMOD, 2004.

[3] Rao, C. Zhang, N. Megiddo, and G. M. Lohman, Automating Physical Database Design in a Parallel Database”, SIGMOD, 2002.

[4] N. Polyzotis, “Selectivity-based Partitioning: A Divide-and-union Paradigm for Effective Query Optimization”, CIKM, 2005.

[5] P. Bizarro, S. Babu, D. J. DeWitt, and J. Widom, “Content-based Routing: Different Plans for Different Data”, VLDB, 2005.

[6] A. Deshpande, C. Guestrin, W. Hong, and S. Madden,“Exploiting Correlated Attributes in Acquisitional Query Processing”, ICDE, 2005.

[7] C. Baldwin, T. Eliassi-Rad, G. Abdulla, and T. Critchlow, “The Evolution of a Hierarchical Partitioning Algorithm for Large- Scale Scientific Data:Three Steps of Increasing Complexity”,SSDB 2003.

[8] Herodotou, N. Borisov, S. Bbu, “Query Optimization Techniques for Partitioned Tables”, SIGMOD’11, Athens, Greece, June 2011

[9] F. Afrati and J. D. Ullman. Optimizing Joins in a MapReduce Environment. In EDBT, 2010.

[10] Sharma, Sakshi, Narendra Kumar Kumawat, and Mukesh Maheshwari. "Automatic Traffic Congestion Detection and Alert System." ijcat.org, pg 296 – 300.

[11] T. . Morales.Oracle(R) Database VLDB and Partitioning Guide 11g Release 1(11.1). Oracle Corporation,2007 http://downloaduk.oracle.com/docs/cd/B28359_01/se rver111/b32024/toc.html

[12] ‘TPC Benchmark H Standard Specification’, 2009 http://www.tpc.org/tpch/spec/tpch2.9.0.pdf

[13] R. Talmage. Partitioned Table and Index Strategies Using SQL , Server 2008. Microsoft, 2009. http://msdn.microsoft.com/enus/ library/dd578580.aspx

[14] Chun-Hung Cheng, Wing-Kin Lee, and Kam-Fai Wong, A Genetic Algorithm-Based Clustering Approach for Database Partitioning, IEEE Transactions On Systems, Man, And Cybernetics Part C: Applications And Reviews,Vol. 32, No. 3, Pp 215- 230,August 2002.

[15] Hadj Mahboubi and J´ er ˆome Darmont Enhancing XML Data Warehouse Query Performance by Fragmentation, SAC’09.ACM, March 2009, Honolulu, Hawaii, U.S.A.

[16] Lisbeth Rodríguez and Xiaoou Li, A Dynamic Vertical Partitioning Approach for Distributed Database System, man and Cybernetics (SMC), 2011 International Conference on 9-12 Oct. 2011, pp-1853-1858, Anchorage, AK.

[17] Ali A. Amer and Hassan I. Abdalla, An Integrated Design Schema for Performance Optimization in Distributed Environments,2012.International Conference on Education and e-Learning Innovations IEEE, 978-1-4673-2225-6/12.

[18] Valentina Ciriani, Sabrina De Capitani di Vimercati, Sara Foresti Fragmentation Design for Efficient Query Execution over Sensitive Distributed Databases, 2009 29th IEEE, International Conference on Distributed Computing Systems,DOI10.1109/ICDCS 2009.

[19] Ajit M. Tamhankar and Sudha Ram, Database Fragmentation and Allocation: An Integrated Methodology and Case Study, IEEE Transactions On Systems, Man, And Cybernetics—Part A:Systems And Humans, Vol. 28, No.3, pp. 288-305 May 1998.

[20] Yon Dohn Chung , Woo Suk Yang , Myoung Ho Kim ,An efficient, robust method for processing of partial top- k/bottom-kqueries using the RD-Tree in OLAP, ACM Decision Support Systems 43 (2007) pp. 313– 321.

[21] T. M. Ozsu and P. Valduriez, “Principles of Distributed Database Systems”, Prentice Hall, 1999.