Data Partitioning in Data Warehouses: Hardness Study, Heuristics and ORACLE Validation

Abstract

Horizontal data partitioning is a non redundant optimization technique used in designing data warehouses. Most of today’s commercial database systems offer native data definition language support for defining horizontal partitions of a table. Two types of horizontal partitioning are available: primary and derived horizontal fragmentations. In the first type, a table is decomposed into a set of fragments based on its own attributes, whereas in the second type, a table is fragmented based on partitioning schemes of other tables. In this paper, we first show hardness to select an optimal partitioning schema of a relational data warehouse. Due to its high complexity, we develop a hill climbing algorithm to select a near optimal solution. Finally, we conduct extensive experimental studies to compare the proposed algorithm with the existing ones using a mathematical cost model. The generated fragmentation schemes by these algorithms are validated on Oracle 10g using data set of APB1 benchmark.