Table Partitioning Schemes-Partitioning
There are currently nine methods by which you can partition tables in Oracle:
•\ Range partitioning: You may specify ranges of data that should be stored together. For example, everything that has a timestamp within the month of Jan-2014 will be stored in partition 1, everything with a timestamp within Feb-2014 in partition 2, and so on. This is probably the most commonly used partitioning mechanism in Oracle.
•\ Hash partitioning: You saw this in the first example in this chapter. A column (or columns) has a hash function applied to it, and the row will be placed into a partition according to the value of this hash.
•\ List partitioning: You specify a discrete set of values, which determines the data that should be stored together. For example, you could specify that rows with a STATUS column value in ( ‘A’, ‘M’, ‘Z’ ) go into partition 1, those with a STATUS value in ( ‘D’, ‘P’, ‘Q’ ) go into partition 2, and so on.
•\ Interval partitioning: This is very similar to range partitioning with the exception that the database itself can create new partitions as data arrives. With traditional range partitioning, the DBA was tasked with pre-creating partitions to hold every possible data value, for now and into the future. This typically meant that a DBA was tasked with creating partitions on a schedule—to hold next months’ or next weeks’ data. With interval partitioning, the database itself will create partitions as new data arrives that doesn’t fit into any existing partition based on a rule specified by the DBA.
•\ Reference partitioning: This allows a child table in a parent/child relationship enforced by a foreign key to inherit the partitioning scheme of the parent table. This makes it possible to equipartition a child table with its parent table without having to denormalize the data model. In the past, a table could only be partitioned based on attributes it physically stored; reference partitioning in effect allows you to partition a table based on attributes from its parent table.
•\ Interval reference partitioning: As the name implies, this is a combination of interval and reference partitioning. It allows for the automatic adding of partitions to parent/child reference partitioned tables.
•\ Virtual column partitioning: This allows partitioning on an expression based on one or more existing columns of the table. The expression is stored as metadata only.
•\ Composite partitioning: This is a combination of range, hash, and list partitioning. It allows you to first apply one partitioning scheme to some data and then, within each resulting partition, have that partition subdivided into subpartitions using some other partitioning scheme.
•\ System partitioning: The application determines which partition a row is explicitly inserted into. This partitioning type has limited uses and won’t be covered in this chapter; we only mention it here to complete the list of partition types that Oracle supports. For more details on system partitioning, see the Oracle Database Cartridge Developer’s Guide.
In the following sections, we’ll look at the benefits of each type of partitioning and at the differences between them. We’ll also look at when to apply which schemes to different application types. This section is not intended to present a comprehensive demonstration of the syntax of partitioning and all of the available options. Rather, the examples are simple and illustrative and designed to give you an overview of how partitioning works and how the different types of partitioning are designed to function.
Note For full details on partitioning syntax, I refer you to either the Oracle Database SQL Language Reference manual or to Oracle Database Administrator’s Guide. Additionally, the Oracle Database VLDB and Partitioning Guide and Oracle Database Data Warehousing Guide are both excellent sources of information on the partitioning options and are must-reads for anyone planning to implement partitioning.