有效率的處理在資料倉儲上連續的聚合查詢

Efficient Computation of Continuous Aggregation Queries on Data Warehouse

林景堂(Jing-Tang Lin)

指導教授 ： 蔡孟峰

國立中央大學/資訊電機學院/資訊工程學系/碩士(2007年)

摘要

資料倉儲通常儲存大量歷史性的資料，而使用者們所下的聚合查詢是為了分析這些在資料倉儲裡大量的資料，這些操作通常需要耗費大量的時間跟系統資源，而且所耗費的時間通常是一般線上處理資料庫系統的好幾倍，如何縮短這些聚合查詢的回應時間就變的相當重要。在資料倉儲的環境下很適合使用實體化視域來縮短這些聚合查詢的時間，我們提出一個可以根據這些聚合查詢間所互相衍生的情形建構有向無迴圈圖的方法，並修改深度優先搜尋演算法去走訪這個有向無迴圈圖，然後在系統所限制的空間限制下我們將找出一個可以有良好改善效能的執行序列，可以讓每一個查詢得到最合適的實體化視域，縮短這些聚合查詢所需要的回應時間。

關鍵字

資料倉儲 ； 線上處理資料庫系統 ； 實體化視域 ； 深度優先搜尋演算法

並列摘要

Data Warehouse usually stores a large amount of historical data. User’s aggregate queries usually have to consume a large amount of time and system resources in order to analyze a large amount of data in data warehouse. The response time of these aggregate queries is typically several orders of magnitude higher than the response time of OLTP (Online Transaction Processing) queries. Because that, how to reduce their response time is becoming increasingly important. The concept of materialized view is well suited to the data warehouse environment. We offer a method to construct DAG (Directed Acyclic Graph) base on the derived situation between these aggregate queries. And then, we modify the depth-first search algorithm to travel this DAG. Finally, we will find out a queries execution order has well improve performance under the space constraint restricted by the data warehouse system.

並列關鍵字

Data Warehouse ； OLTP ； materialized view ； depth-firs

參考文獻

[1] S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala “Database Tuning Advisor for Microsoft SQL Server 2005.” In Proceedings of VLDB (2004). 1110-1121.

[2] D. Zilio, J. Rao, S. Lightstone, G. Lohman, A. Storm, C. Garcia-Arellano, and S. Fadden “DB2 Design Advisor. Integrated Automatic Physical Database Design.” In Proceedings of VLDB (2004). 1087-1097.

[3] B. Dageville, D. Das, K. Dias, K. Yagoub, M. Zait, and M. Ziauddin “Automatic SQL Tuning in Oracle 10g.” In Proceedings of VLDB (2004).1098-1109.

[6] S. Agrawal, S. Chaudhuri and V.R. Narasayya. “Automated selection of materialized views and indexes in SQL databases.” In Proceedings of VLDB, pages 496–505, 2000.

[7] C.D.de Aguiar Ciferri and F.F.de Souza, “Materialized Views in Data Warehousing Environments,” in Proceedings of XXI International Conference of The Chilean Computer Science Society (SCCC), pp. 3-12, 2001.