Range Partitioning Techniques - Parallel Database

Range Partitioning Techniques

Rows in a range-partitioned table or index are distributed among partitions according to values in the partitioning key columns. The partitioning column values of each row are compared with a set of upper and lower bounds to determine the partition to which the row belongs: 

i) Every partition has an inclusive upper bound, which is specified by the values <= clause when the partition is created. 

ii) Every partition except the first has a non-inclusive lower bound, which is specified implicitly by the values <= clause on the next-lower partition. 

Range partitioning is particularly useful for high-performance applications in both OLTP (Online Transaction Processing) and decision-support environments. Select ranges carefully so that rows are assigned equally to all partitions — knowledge of the data distribution of the partition key columns is crucial to balancing the load evenly among the partitions. Range partitions are ordered; that is, each succeeding partition must have a higher bound than the previous partition. 

In the technique of range partitioning an administrator specifies that attribute values within a certain range are to be placed on a certain disk. In other words, range partitioning distributes contiguous attribute-value ranges to each disk. 

Advantage Rouge Partitioning 

i) Range partitioning involves placing tuples containing attribute values that fall within a certain range on a disk. This offers good performance for range-based queries and also Provides reasonable performance for exact-match (point) queries involving the partitioning attribute. 

ii) For point queries, the partitioning vector can be used to locate the disk where the tuples reside. 

iii) For range queries, the partitioning vector is used to find the range of disks on which the tuples may reside. In both cases, the search narrows to exact disks that might have any tuples of interest.