Database Reference
In-Depth Information
18.3.6
Creating Partitioned Tables
Partitioned tables are part and parcel of an add-on option called Oracle Par-
titioning. Oracle Partitioning working in concert with parallel processing
and separate disk spindles or RAID arrays can provide fairly substantial per-
formance improvements. Oracle Partitioning and parallelism is an immense
subject all by itself.
2
Additionally, numerous interesting tricks can be done
when using Oracle Partitioning. All we want to do in this section is demon-
strate using partitions from the Oracle SQL perspective, namely syntax and
some examples.
18.3.6.1
What Are the Types of Partitions?
There are five different types of partitions as follows:
Range
. Divides up rows based on ranges of values.
Values List
. Divides up rows based on sets of literal values.
Hash
. Uses a hashing algorithm to divide rows, resulting in the most
consistently sized partitions.
Composite Partitions
. Contains subpartitions within each separate
partition:
Range-Hash
. A range partition containing hash subpartitions
within each range partition.
Range-List
. A range partition containing list value subpartitions
within each range partition.
18.3.6.1.1
Partition Indexing
Partitions can have indexes. How are indexes built for partitions? There are
two types of partitioning indexes:
Local Index
. These indexes have the same structure as their relative
table partitions. Local indexes are preferred because of their more
automated maintenance. A local partition index applies to each parti-
tion.
Global Index
. These indexes are created on partitioned tables but are
not the same structure as the partitioning key. A global index applies
to all partitions in a partitioned table.
