Manipulating Partitions in Oracle Database 11g

This tutorial demonstrates how to use various partitioning techniques in Oracle Database 11g.

Note: This OBE was tested on production software however some of the screenshots may still refer to the beta software.

Approximately 50 minutes


This tutorial covers the following topics:

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.


In Oracle Database 11g, there are a number of new techniques for partitioning your table data to increase the retrieval performance and organization of your data. These techniques include:

Extended Composite
Virtual Column

Back to Topic List

Before you perform this tutorial, you should:


Install Oracle Database 11g.


Download and unzip the file into your working directory.


Open a terminal window and execute the following commands:

sqlplus / as sysdba

Back to Topic List

Using Reference Partitioning

Reference partitioning enables tables with a parent-child relationship to be logically equi-partitioned by inheriting the partition key from the parent table without duplicating the key columns. The partitioning key is resolved through an existing parent-child relationship, enforced by active primary key or foreign key constraints. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

Perform the following steps to further understand the use of reference partitioning:


Open a terminal window and log in to SQL*Plus as the SH user. Execute the create_orders.sql script to create the range-parititioned ORDERS table.




Execute the create_order_items.sql script to create a reference-partitioned ORDER_ITEMS table.




Execute the query_dict_1.sql script to view information about the reference-partitioned ORDER_ITEMS table.




Execute the insert_orders.sql script to insert data into the ORDERS table.




Execute the show_data_placement.sql script to observe how the data is colocated in the partitions.




To show the partition-wise join, set the _parallel_broadcast_enabled parameter to FALSE. Execute the following command:

ALTER SESSION SET "_parallel_broadcast_enabled"=FALSE;

Note: A parallel partition-wise join is very efficient when joining two large partition tables in parallel. This tutorial does not use a very large data set so in order to illustrate what an execution plan with a parallel partition-wise join looks like, the parameter _parallel_broadcast_enabled is set to false and a hash join hint is used in the query. In your environment, you do not need to set the parameter or use hints to see the parallel partition-wise join.



Execute the show_plan.sql script to view information about the partition-wise join.




Execute the drop_partition.sql script to drop the p_before_jan_2006 partition from the ORDERS table.




Execute the query_dict_2.sql script to view information about the tables.




Execute the add_partition.sql script to add a p2007_01 partition. Note: Two partitions are added to the parent table, and the second one uses a different tablespace allocation for the child table partition using the "dependent tables" clause.




Execute the cleanup_1.sql script to drop the partitions and tablespaces created in this scenario.



Back to Topic List

Interval partitioning fully automates the creation of range partitions. Managing the creation of new partitions can be a cumbersome and highly repetitive task. This is especially true for predictable additions of partitions covering small ranges, such as adding new daily partitions. Interval partitioning automates this operation by creating partitions on demand.

Perform the following steps to understand the use of interval partitioning:


Execute the create_newsales.sql script to create the NEWSALES interval-partitioned table.




Execute the query_dict_3.sql script to query the USER_TAB_PARTITIONSNEWSALES dictionary view for information about the NEWSALES table.




Execute the insert_newsales.sql script to insert new data into the NEWSALES table that forces the creation of a new partition (segment).




Execute the query_dict_4.sql script to see information about the new partition.




Execute the merge_partition.sql script to merge two partitions.




Execute the query_dict_5.sql script to view information about the merged partitions.




Execute the create_hist_newsales.sql script to create a range-partitioned table.




Execute the query_dict_6.sql script to view information about the merged partitions.




Execute the insert_histnewsales_row_1.sql script to insert a row into the HISTORICAL_NEWSALES table. The insert fails because the partitioned table is not currently an interval-partitioned table.




Execute the alter_hist_newsales.sql script to change the partitioned table to an interval-partitioned table.




Execute the insert_histnewsales_row_2.sql script to try to insert a row into the table again.




Execute the query_dict_7.sql script to view information about the partitions.



Back to Topic List

Using Extended Composite Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions; —for example, partition by ORDER_DATE and range subpartition by SHIPPING_DATE. Composite range-range partitioning provides an additional modeling strategy to map a business need to the partitioning of an object.

In this example, the service-level agreement with the customer states that an order will be delivered in the calendar month after the order was placed. The following types of orders are identified:


Perform the following steps to create a range-range composite partitioned table to satisfy the business needs:


Execute the create_shipments.sql script to create the SHIPMENTS table as a range-range composite partitioned table by ORDER_DATE and SHIPDATE.




Use the insert_shipments.sql script to insert rows into the SHIPMENTS table.




Execute the count_shipments.sql script to view how data was distributed in the SHIPMENTS table.



Back to Topic List

Using Virtual Column-Based Partitioning

You can use virtual column partitioning to partition key columns defined on virtual columns of a table. Virtual columns are defined by evaluating an expression. Virtual columns can be defined at table creation or during modification time. Frequently, business requirements to logically partition objects do not match existing columns in a one-to-one manner. Oracle partitioning has been enhanced to allow a partitioning strategy being defined on virtual columns, thus enabling a more comprehensive match of the business requirements.

In this example, employees categories are:

Not doing too bad
On target
Filthy rich

Perform the following steps to understand the use of virtual column-based partitioning:


Execute the create_employees.sql script to create the EMPLOYEES table with a virtual column.




Execute the insert_employees.sql script to INSERT rows into the EMPLOYEES table.




Execute the select_employees.sql script to query the EMPLOYEES table.




Execute the create_prod_ret.sql script to create the PRODUCT_RETURNS table with a virtual column, which is used to partition the table.




Execute the insert_prod_ret.sql script to insert rows into the PRODUCT_RETURNS table.




Execute the select_prod_return.sql script to query the PRODUCT_RETURNS table.




Execute the xplan_prod_ret.sql script to show partition pruning for queries that use the expression that defines the virtual column.



Back to Topic List

In this tutorial, you learned how to:

Use reference partitioning
Use interval partitioning
Use extended composite partitioning
Use virtual column partitioning

Back to Topic List

Place the cursor over this icon to hide all screenshots.