Partitioning (Oracle)

This module belongs to the commercial version enterprise of Oracle. It is about fact of a paying option, frequently used on tables of important volumetry.

Partitioning makes it possible to cut out a table and/or an index on 1 or several criteria logical. The table behaves then like several tables of smaller size. The principal advantages of partitioning are:

to be able to define different criteria of storage for each partition (tablespace, initial…)
the logical cutting of the table allows a faster access to information (less readings discs to be carried out in particular)

There exist 3 great types of partitionings:

by arranges : one defines the partitions by higher section exclusive (all data < 0)
by list : one defines a value by partition (used when the list of value for the field considered is weak)
by hash : the partition of storage is calculated dynamically by a calculation of the type hash code , this mode imposes a composite number of partitions 2^n
: it is a method of partitioning hybrid. The data are initially partitionnées by arranges . Then, each partition will be under-partitionnée is by hash or by list . It is not necessary to have exactly the same number of under-partitions per partition: for example, a partition can be made up of 4 under-partitions whereas another is made up of 5 under-partitions.

The logical mechanism of cutting can be wide on several fields, like on 2 levels. One speaks in this case about under-partitioning.

There exist 2 ways of partitionner an index under Oracle:

locally partitioned index : for each partition of table created, there will be one (and only one) partition of index. The data in each partition of index point on the whole of the data of one and single partition of table. Logically, if the table has NR partitions, the index will also have NR partitions
globally partitioned index : the index is partitionné independently of the table. A partition of index can point on data in one or more partitions of table. An overall partitionné index cannot be that partitionné by arranges . Index on partitionnées tables are, by defect, of index total on only one partition (indeed, by defect, they are not partitionnés).

Random links: