oracle 11g partitioning

7/31/2019 Oracle 11g Partitioning

1/11

PARTITIONING

Business applications are growing at a faster rate than salaries, and so is the data supporting

them, especially e-business applications where the data growth has been around 20-30 percent or

more annually. Oracle came up with the idea of PARTITIONING the Tables.

Yes! The name sounds right Partitioning, the literal meaning of Partitioning is The Act of

dividing something into parts and in our case that something is nothing but Data.Oracle introduced partitioning with 8.0. With this version only, RANGE-Partitioning was

supported. Then with Oracle 8i HASH and COMPOSITE Partitioning was also introduced and with

9i LIST Partitioning. Each method of partitioning has its own advantages and disadvantages and the

decision which one to use will depend on the data and type of application. Also you can MODIFY,

RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions.

Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces,

where each piece of such a database object is called a partition. Each partition has its own name, and

may optionally have its own storage characteristics.

Above image shows both partitioned and non-partitioned tables, each can have partitioned or non-

partitioned indexes.

Note: All partitions of a partitioned object must reside in tablespaces of a single block size.

Note: To reduce disk usage and memory usage (specifically, the buffer cache), you can store tablesand partitions of a partitioned table in a compressed format inside the database. This often leads to a

better scale up for read-only operations. Table compression can also speed up query execution. There

is, however, a slight cost in CPU overhead.

Note: LONG or LONG RAW datatypes must not used as a column datatype for partition. You can,

however, use columns with CLOB or BLOB datatypes.


2/11

Partitioning Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is

comprised of one or more columns that determine the partition where each row will be stored. Oracle

automatically directs DML operations to the appropriate partition through the use of the partitioningkey. The partition key is a set of from 1 to 16 columns that determines the partition for each row.

Advantages of using Partitions in Table

1. Partition increases the manageability.2. Partition Reduced recovery time3. Failure impact is less4. Import / Export can be done at the Partition Level".5. Partition enhances the performance6. Partition increases the availability.7. Partitions work independent of the other partitions.8. Partitions reduce the total cost of ownership for storing large amounts of data

When to Partition a Table

1. Tables greater than 2 GB should always be considered as candidates for partitioning2. When the contents of a table need to be distributed across different types of storage devices3. Tables containing historical data, in which new data is added into the newest partition. A

typical example is a historical table where only the current month's data is updatable and the

other 11 months are read only.

When to Partition an Index

1. Avoid rebuilding the entire index when data is removed.2. Perform maintenance on parts of the data without invalidating the entire index.3. Reduce the impact of index skew caused by an index on a column with a monotonically

increasing value.

Partitioned Index-Organized Tables

1. Partition columns must be a subset of the primary key columns2. Secondary indexes can be partitioned (both locally and globally)3. OVERFLOW data segments are always equi-partitioned with the table partitions

Partitioning and LOB Data

Unstructured data (such as images and documents) which is stored in a LOB column in the database

can also be partitioned. When a table is partitioned, all the columns will reside in the tablespace for

that partition, with the exception of LOB columns, which can be stored in their own tablespace.


3/11

This technique is very useful when a table is comprised of large LOBs because they can be stored

separately from the main data. This can be beneficial if the main data is being frequently updated but

the LOB data isn't. For example, an employee record may contain a photo which is unlikely to change

frequently. However, the employee personnel details (such as address, department, manager, and so

on) could change. This approach also means that cheaper storage can be used for storing the LOB

data and more expensive, faster storage used for the employee record.

Single-Level Partitioning

1. Range PartitioningRange partitioning maps data to partitions based on ranges of values of the partitioning key that you

establish for each partition. It is the most common type of partitioning and is often used with dates.

For a table with a date column as the partitioning key, the January-2005 partition would contain rowswith partitioning key values from 01-Jan-2005 to 31-Jan-2005.

Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound for

the partitions. Any values of the partitioning key equal to or higher than this literal are added to the

next higher partition. All partitions, except the first, have an implicit lower bound specified by the

VALUES LESS THAN clause of the previous partition.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual

infinite value that sorts higher than any other possible value for the partitioning key, including the

NULL value.

When to Use Range Partitioning: Use range partitioning to map rows to partitions based on ranges of

column values. This type of partitioning is useful when dealing with data that has logical ranges into

which it can be distributed; for example, months of the year. Performance is best when the data

evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in

size because of unequal distribution, you may want to consider one of the other methods of

partitioning.


4/11

When creating range partitions, you must specify:

Partitioning method: range Partitioning column(s) Partition descriptions identifying partition bounds

The example below creates a table of four partitions, one for each quarter of sales. The columns

sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute

the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition

bound: rows with partitioning key values that compare less than the ordered list of values specified

by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and

each partition is contained in a separate tablespace (tsa, tsb, ...).

CREATE TABLE sales( invoice_no NUMBER,sale_year NUMBER NOT NULL,sale_month NUMBER NOT NULL,sale_day NUMBER NOT NULL)

PARTITION BY RANGE (sale_year, sale_month, sale_day)( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa,PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb,PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc,PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd);

A row with sale_year=1999, sale_month=8, and sale_day=1 has a partitioning key of(1999, 8,

1) and would be stored in partition sales_q3.

2. Hash Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to thepartitioning key that you identify. The hashing algorithm evenly distributes rows among partitions,

giving partitions approximately the same size.

Hash partitioning is the ideal method for distributing data evenly across devices. Hash partitioning is

also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not

historical or has no obvious partitioning key.

When to Use Hash Partitioning: Use hash partitioning if your data does not easily lend itself to range

partitioning, but you would like to partition for performance and manageability reasons. Hash


5/11

partitioning provides a method of evenly distributing data across a specified number of partitions.

Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using

hash partitions gives you a highly tunable method of data placement, because you can influence

availability and performance by spreading these evenly sized partitions across I/O devices (striping).

To create hash partitions you specify the following:

Partitioning method: hash Partitioning column(s) Number of partitions or individual partition descriptions

The following example creates a hash-partitioned table. The partitioning column is id, four partitions

are created and assigned system generated names, and they are placed in four named tablespaces(gear1, gear2, ...).

CREATE TABLE scubagear(id NUMBER,name VARCHAR2 (60))

PARTITION BY HASH (id)PARTITIONS 4STORE IN (gear1, gear2, gear3, gear4);

3. List PartitioningList partitioning enables you to explicitly control how rows map to partitions by specifying a list of

discrete values for the partitioning key in the description for each partition. The advantage of list

partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.For a table with a region column as the partitioning key, the North America partition might contain

values Canada, USA, and Mexico.

The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned

table by using a default partition, so that all rows that do not map to any other partition do not

generate an error.


6/11

When to Use List Partitioning: Use list partitioning when you require explicit control over how rows

map to partitions. You can specify a list of discrete values for the partitioning column in the

description for each partition. This is different from range partitioning, where a range of values is

associated with a partition, and from hash partitioning, where the user has no control of the row to

partition mapping.

The list partitioning method is specifically designed for modeling data distributions that followdiscrete values. This cannot be easily done by range or hash partitioning because:

Range partitioning assumes a natural range of values for the partitioning column. It is notpossible to group together out-of-range values partitions.

Hash partitioning allows no control over the distribution of data because the data isdistributed over the various partitions using the system hash function. Again, this makes it

impossible to logically group together discrete values for the partitioning columns into

partitions.

Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized

together very naturally.

Unlike the range and hash partitioning methods, multicolumn partitioning is not supported for list

partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of

the table. Otherwise all columns that can be partitioned by the range or hash methods can bepartitioned by the list partitioning method.

When creating list partitions, you must specify:

Partitioning method: list Partitioning column Partition descriptions, each specifying a list of literal values (a value list), which are the

discrete values of the partitioning column that qualify a row to be included in the partition

The following example creates a list-partitioned table. It creates table q1_sales_by_region which is

partitioned by regions consisting of groups of states.

CREATE TABLE q1_sales_by_region(deptno number,deptname varchar2(20),quarterly_sales number(10, 2),state varchar2(2))

PARTITION BY LIST (state)(PARTITION q1_northwest VALUES ('OR', 'WA'),PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),

PARTITION q1_southeast VALUES ('FL', 'GA'),PARTITION q1_northcentral VALUES ('SD', 'WI'),PARTITION q1_southcentral VALUES ('OK', 'TX'));

A row is mapped to a partition by checking whether the value of the partitioning column for a rowmatches a value in the value list that describes the partition.

For example, some sample rows are inserted as follows:


7/11

(10, 'accounting', 100, 'WA') maps to partition q1_northwest (20, 'R&D', 150, 'OR') maps to partition q1_northwest (30, 'sales', 100, 'FL') maps to partition q1_southeast (40, 'HR', 10, 'TX') maps to partition q1_southwest (50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an

error

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions.

You can also specify a default partition into which rows that do not map to any other partition are

mapped. If a default partition were specified in the preceding example, the state CA would map to

that partition.

Composite Partitioning

Composite partitioning is a combination of the basic data distribution methods; a table is partitioned

by one data distribution method and then each partition is further subdivided into sub partitions using

a second data distribution method. All sub partitions for a given partition together represent a logical

subset of the data.

Composite partitioning supports historical operations, such as adding new range partitions, but also

provides higher degrees of potential partition pruning and finer granularity of data placement throughsub partitioning. Above figure offers a graphical view of range-hash and range-list composite

partitioning, as an example.

1. Composite Range-Range PartitioningComposite range-range partitioning enables logical range partitioning along two dimensions; for

example, partition by order_date and range sub partition by shipping_date.


8/11

2. Composite Range-Hash PartitioningComposite range-hash partitioning partitions data using the range method, and within each partition,

sub partitions it using the hash method. Composite range-hash partitioning provides the improved

manageability of range partitioning and the data placement, striping, and parallelism advantages of

hash partitioning.

When to Use Composite Range-Hash Partitioning: Range-hash partitioning partitions data using the

range method, and within each partition, sub partitions it using the hash method. These composite

partitions are ideal for both historical data and striping, and provide improved manageability of range

partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating range-hash partitions, you specify the following:

Partitioning method: range Partitioning column(s) Partition descriptions identifying partition bounds Sub partitioning method: hash Sub partitioning column(s) Number of sub partitions for each partition or descriptions of sub partitions

The following statement creates a range-hash partitioned table. In this example, three range partitions

are created, each containing eight sub partitions. Because the sub partitions are not named, system

generated names are assigned, but the STORE IN clause distributes them across the 4 specified

tablespaces (ts1, ...,ts4).

CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)(PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2000),PARTITION p3 VALUES LESS THAN (MAXVALUE));


9/11

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the

segments of their sub partitions. As with partitions, these sub partitions share the same logical

attributes. Unlike range partitions in a range-partitioned table, the sub partitions cannot have different

physical attributes from the owning partition, although they are not required to reside in the same

tablespace.

3. Composite Range-List PartitioningComposite range-list partitioning partitions data using the range method, and within each partition,sub partitions it using the list method. Composite range-list partitioning provides the manageability of

range partitioning and the explicit control of list partitioning for the sub partitions.

When to Use Composite Range-List Partitioning: Like the composite range-hash partitioning method,

the composite range-list partitioning method provides for partitioning based on a two level hierarchy.

The first level of partitioning is based on a range of values, as for range partitioning; the second levelis based on discrete values, as for list partitioning. This form of composite partitioning is well suited

for historical data, but lets you further group the rows of data based on unordered or unrelated column

values.

When creating range-list partitions, you specify the following:

Partitioning method: range Partitioning column(s) Partition descriptions identifying partition bounds Sub partitioning method: list Sub partitioning column Sub partition descriptions, each specifying a list of literal values (a value list), which are the

discrete values of the sub partitioning column that qualify a row to be included in the sub

partition

The following example illustrates how range-list partitioning might be used. The example tracks sales

data of products by quarters and within each quarter, groups it by specified states.

CREATE TABLE quarterly_regional_sales(deptno number, item_no varchar2(20),


10/11

txn_date date, txn_amount number, state varchar2(2))TABLESPACE ts4PARTITION BY RANGE (txn_date)SUBPARTITION BY LIST (state)(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))

(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')),

PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')),

PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')),

PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX'))

);

A row is mapped to a partition by checking whether the value of the partitioning column for a row

falls within a specific partition range. The row is then mapped to a sub partition within that partitionby identifying the sub partition whose descriptor value list contains a value matching the sub partition

column value.

For example, some sample rows are inserted as follows:

(10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition 1_1999_northwest (20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition q2_1999_northwest (30, 9977612, ,'07-Sep-1999', 30987.90, 'FL') maps to subpartition q3_1999_southeast (40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition q4_1999_southcentral (40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and

raises an error

(50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the tableand raises an error

The partitions of a range-list partitioned table are logical structures only, as their data is stored in the

segments of their subpartitions. The list subpartitions have the same characteristics as list partitions.You can specify a default subpartition, just as you specify a default partition for list partitioning.


11/11

4. Composite List-Range PartitioningComposite list-range partitioning enables logical range sub partitioning within a given list partitioning

strategy; for example, list partition by country_id and range sub partition by order_date.

5. Composite List-Hash PartitioningComposite list-hash partitioning enables hash sub partitioning of a list-partitioned object; forexample, to enable partition-wise joins.

6. Composite List-List PartitioningComposite list-list partitioning enables logical list partitioning along two dimensions; for example,

list partition by country_id and list sub partition by sales_channel.

oracle 11g partitioning

Documents