Supporting table partitioning by reference in oracle

Abstract

Partitioning is typically employed on large-scale data to improve manageability, availability, and performance. However, for tables connected by a referential constraint (capturing a parent-child relationship), the current approaches require individually partitioning each table thereby burdening the user with the task of maintaining the tables equi-partitioned, which not only is cumbersome but also error prone. This paper proposes a new partitioning method (partition by reference) that allows tables with a parent-child relationship to be logically equi-partitioned by inheriting the partition key from the parent table without duplicating the key columns. The partitioning key is resolved through an existing parent-child relationship, enforced by an active referential constraint. This logical dependency is used to automatically i) cascade partition maintenance operations performed on parent table to child tables, and ii) handle migration of child rows when partition key or parent key in parent table is updated, as a single atomic operation. This method has been introduced in Oracle Database 11gR1 with support for tables with both single level and composite partitioning methods. The paper describes the key concepts of table partitioning by reference method, discusses the design and implementation challenges, and presents an experimental study covering a usage scenario common in Information Life Cycle Management (ILM) applications.

Low overhead concurrency control for partitioned main memory databases

“…Data partitioning is a well-studied problem in database systems (e.g., [18,24,8,17,10], amongst others). Past work has noted that partitioning can effectively increase the scalability of database systems, by parallelizing I/O [17] or by assigning each partition to separate workers in a cluster [18].…”

A framework for preservation of cloud users’ data privacy using dynamic reconstruction of metadata

“…This research work involves the extraction of useful notions and ideas from related approaches based on the concepts of anonymous access control (Jensen et al, 2010), privacy preserving data publishing (Fung et al, 2010), provable data possession (Ateniese et al, 2007(Ateniese et al, , 2008, microaggregation , data classification (Han et al, 1991), database encryption (Davida et al, 1981), database table partitioning (Eadon et al, 2008) and referential integrity (Markowitz, 1991).…”

Efficient processing of data warehousing queries in a split execution environment

“…HadoopDB was, therefore, extended to support referential partitioning. Although a similarly named technique was recently made available in Oracle 11g [23], it served a different purpose than in our project where this partitioning scheme facilitates joins across a shared-nothing network.…”