HVS (Part 2) Data Partitioning

Before we delve into Data Partitioning, here’s a review of some fundamental database concepts. In terms of this article the focus will be on Oracle as the database.

Fundamental Database (ORACLE) concepts:
A Table:
(i) Stores structured data
(ii) is a database object housed in a segment.
A normal table (i.e. non-partitioned) is exactly one segment. A partitioned table will be made up of as many segments as it has partitions.

A Tablespace:
(i) is a logical container for segments
(ii) may be empty but it will most likely hold one or more segments
(iii) may be made up of one or more data files.

A Data file:
(i) is the physical operating system (OS) disk file that stores data. All data in an Oracle database ends up in a data file that is part of a file system configured by the OS or as a raw device managed by Oracle

A Data Block: is Oracle manages the storage space in the data files of a database in units called data blocks.

A Segment: is made up of extents and is the logical container for an object in an Oracle database

An Extent: is a set of contiguous data blocks

Data Partitioning (as it relates to the HVS)
Data Partitioning, in a nutshell, decomposes a database object (indexes, tables) into smaller more manageable pieces called partitions. The goal of data partitioning is to reduce the amount of data read for a particular SQL operation so that the overall response time is reduced. The data is organized using a partition key which is essentially a set of columns that determines in which partition a given row of objects will reside. It is important to note that the underlying database must also be partition enabled (which Oracle has been for quite a while). HVS uses i_partition as the partition key and HVS does Range partitioning, i.e., each partition is specified by the value of its partition key (i_partition). When using HVS, a SysObject, all its associated content objects, any local ACLs being referenced by the SysObject, and so on, will be assigned to the partition designation of the SysObject. For a LWSO, if the i_partition is NOT explicitly set, it will by default, be in the same partition as the parent, thus sharing the same i_partition id. Partition pruning is essentially directing a query to a subset of partitions rather than the entire table.

Data Partitioning leads to improved manageability (storage of data files on different physical drives), improved availability (partition independence) and optimized queries (using partition pruning).