Manipulating Partitions in Oracle Database 11g

Purpose

This tutorial demonstrates how to use various partitioning techniques that are new to Oracle Database 11g.

Time to Complete

Approximately 60 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Using Reference Partitioning
 Using Interval Partitioning
 Renaming a Table Partition
 Exchanging a Table Partition
 Using Extended Composite Partitioning
 Using Virtual Column-Based Partitioning
 Summary

Viewing Screenshots

 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.

Overview

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:

Reference Allows 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
Interval Automates the creation of range partitions
Extended Composite Enables logical range partitioning along two dimensions
Virtual Column Allows partition key columns to be defined on virtual columns of a table

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Download and unzip the partition.zip file into your working directory.

3.

Open a terminal window and execute the following commands:

                                 
                                   
sqlplus / as sysdba
@setup
                                
                              


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:

1.

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.

@create_orders

 

2.

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

@create_order_items

 

3.

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

@query_dict_1

 

4.

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

@insert_orders

 

5.

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

@show_data_placement

 

6.

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 _parallel_broadcast_enabled parameter 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.

 

7.

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

@show_plan

 

8.

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

@drop_partition

 

9.

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

@query_dict_2

 

10.

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.

@add_partition

 

11.

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

@cleanup_1

 

Back to Topic List

Using Interval Partitioning

The new Interval partitioning strategy is an extension of range partitioning. Interval partitioning fully automates the partition creation for range. In other words, new partitions will be created when they are needed. By defining the interval criteria, the database knows when to create new partitions when inserted data exceeds all of the 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. Before you can use interval partitioning, you must create at least one range partitioning.

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

1.

Execute the create_newsales.sql script to create the NEWSALES interval-partitioned table. As mentioned earlier, interval partitioning is an extension of range partitioning. For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE data type. In the following example, the partitioning key will be of DATE data type.

@create_newsales

The new INTERVAL clause in the screen capture above defines intervals. Some new date functions are introduced to express the size of an interval in terms of time.

The above example defines a one day interval. The range partition is created at table creation time.

With the Interval clause, there is also a STORE IN optional clause that you can use to specify one or more tablespaces into which the database will store interval partition data using a round-robin algorithm for subsequently created interval partitions. Tablespace is determined by the interval number. The syntax for the STORE IN optional clause is as follows:

...

INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN (tbs1, tbs2, tbs3, tbs4)

...

 

2.

Execute the query_dict_3.sql script to query the USER_TAB_PARTITIONS dictionary view for information about the newly created NEWSALES table. This view displays partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.

@query_dict_3

 

3.

Execute the insert_newsales.sql script to insert new data into the NEWSALES table. The 10 new inserted rows represent dates that are on or after January 1; therefore, six new partitions are automatically created.

@insert_newsales

 

4.

Execute the query_dict_4.sql script to see information about the six new partitions that were created automatically by the system when the 10 rows were inserted.

The high bound of the partition represents the transition point and all partitions below it are in the range section where all future partitions above it (equal to or greater than the January 1, 2005) fall into the interval section. The new partition names are system generated and start with a prefix of sys_ . The newly created interval partitions are as follows in this example:

SYS_P41: The first system created interval partition that contains the newly inserted newsales for 01-jan-2005.

SYS_P42: The second system created interval partition that contains the newly inserted newsales for 02-jan-2005.

SYS_P43:The third system created interval partition that contains the newly inserted newsales for 05-jan-2005.

SYS_P44: The fourth system created interval partition that contains the newly inserted newsales for 06-jan-2005.

SYS_P45: The fifth system created interval partition that contains the newly inserted newsales for 09-jan-2005.

SYS_P46: The sixth system created interval partition that contains the newly inserted newsales for 10-jan-2005.

@query_dict_4

 

5.

Execute the merge_partition.sql script to merge the following two partitions: SYS_P41 and SYS_P42. When you merge two interval partitions, the system automatically converts the complete interval, SYS_P41 and SYS_P42, into a range partition. The transition point is moved to the end of the Range section. Note the new FOR clause that allows you to identify a specific partition without knowing its name.

@merge_partition

Merging interval partitions moves the transition point to the higher upper bound of the two merged partitions. This means that the range section of the interval partitioned table will be extended to the upper bound of the two merged partitions. Any materialized interval partitions with boundaries lower than the newly merged partitions will automatically be converted into the range partitions, with their upper boundaries defined by the upper boundaries of their intervals.

 

6.

Execute the query_dict_5.sql script to view information about the merged partitions. The new partition name is P_BEFORE_3_JAN_2005.

@query_dict_5

 

7.

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

@create_hist_newsales

 

8.

Execute the query_dict_6.sql script to view information about the newly created partitions.

@query_dict_6

 

9.

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.

@insert_histnewsales_row_1

 

10.

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

@alter_hist_newsales

 

11.

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

@insert_histnewsales_row_2

 

12.

Execute the query_dict_7.sql script to view information about the partitions. The newly system created interval partition is named SYS_P<##>.

@query_dict_7

 

You can drop interval partitions in an interval-partitioned table. This operation will drop the data for the interval only and leave the interval definition in tact. If data is inserted in the interval that was just dropped, then the database will create an interval partition again. You also drop range partitions in an interval-partitioned table. However, you cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.

You can disable the creation of future interval partitions and effectively revert back to a range-partitioned table by using an empty value in the SET INTERVAL clause. Interval partitions that are already created are transformed into range partitions with their current high values. You can also use the SET INTERVAL clause to migrate an existing range partitioned or range-*composite partitioned table into an interval or interval-*partitioned table.

Back to Topic List

Renaming a Table Partition

You can rename table and index partitions and subpartitions to assign a meaningful name instead of a default system name that was assigned to the partition in another maintenance operation. All partitioning methods support the FOR(value) method to identify a partition. You can use this method to rename a system-generated partition name into a more meaningful name. This is particularly useful in interval or interval-* partitioned tables. You can independently rename partitions and subpartitions for reference-partitioned master and child tables. The rename operation on the master table is not cascaded to descendant tables.

You can rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. In this section, you will rename the system generated partition that was created in the Interval Partitioning section:

1.

To see what partitions you have, re-execute the query_dict_7.sql script that you ran previously

@ query_dict_7

 

2.

You can rename the partition by executing the following command:

                                 
ALTER TABLE historical_newsales RENAME PARTITION sys_p<##> TO P_2006_p<##>;
                              

where sys_p<##> is the system generated partition from the query in step 1.

 

3.

Rerun the query_dict_7.sql script to see that the partition table name has changed.

@ query_dict_7

 

Back to Topic List

Exchanging a Table Partition

You can convert a partition (or subpartition) into a non-partitioned table, and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments. Exchanging table partitions is most useful when you have an application using non-partitioned tables that you want to convert to partitions of a partitioned table. For example, in data warehousing environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an already existing partitioned table. Generically, OLTP as well as data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterwards. When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged ( INCLUDING INDEXES clause), and if rows are to be validated for proper mapping ( WITH VALIDATION clause).

You can exchange interval partitions in an interval-partitioned table. However, you have to make sure the interval partition has been created before you can exchange the partition. You can let the database create the partition by locking the interval partition. The following example shows a partition exchange for the historical_newsales table, interval-partitioned using monthly partitions. This example shows how to add data for January 2007 to the table using partition exchange load. Assume there are only local indexes on the historical_newsales table, and equivalent indexes have been created on the historical_newsales2 table. Perform the following steps:

1.

To create another partition to put the exchanged data into, execute the lock_hist_newsales.sql script.

@ lock_hist_newsales

 

2.

Rerun the query_dict_7.sql script to see the new partition.

@ query_dict_7

 

3.

Now you will create a new table that is not partitioned and insert a row of data. Execute the create_hist_newsales2.sql script.

@ create_hist_newsales2

 

4.

You want to view the data in the historical_newsales data before you do the exchange. Execute the following command:

select * from historical_newsales;

 

5.

Now you are ready to perform the exchange. Execute the exchange_hist_newsales.sql script.

@ exchange_hist_newsales

 

6.

Execute the following command to see what is contained in both the historical_newsales and historical_newsales2 tables.

select * from historical_newsales;
select * from historical_newsales2;

 

7.

Rerun the query_dict_7.sql script one more time. Note that the data for '02-JAN-07' is now located in the system generated partition SYS_P118 in the historical_newsales table because it is before January 1, 2008.

@ query_dict_7

 

You can also exchange partitions in a reference-partitioned table, but you have to make sure that data you reference is available in the respective partition in the parent table.

You can exchange partitions in the presence of virtual columns. In order for a partition exchange on a partitioned table with virtual columns to succeed, you have to create a table that matches the definition of all non-virtual columns in a single partition of the partitioned table. You do not need to include the virtual column definitions, unless constraints or indexes have been defined on the virtual column. In this case, you have to include the virtual column definition in order to match the partitioned table's constraint and index definitions. This scenario also applies to virtual column-based partitioned tables.

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:

EARLY Orders that are delivered before the the middle of the next month after the order was placed. These orders likely exceed customers' expectations.
AGREED Orders that are delivered in the calendar month after the order was placed (but not early orders)
LATE Orders that were only delivered starting the second calendar month after the order was placed

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

1.

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

@create_shipments

 

2.

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

@insert_shipments

 

3.

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

@count_shipments

 

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:

Poor total salary (salary + commission) is less than 1000
Not doing too bad salary is between 1000 and 3000
On target salary of 3000
Filthy rich salary > 3000

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

1.

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

@create_employees

 

2.

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

@insert_employees

 

3.

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

@select_employees

. . .


 

4.

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

@create_prod_ret

 

5.

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

@insert_prod_ret

. . .

 

6.

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

@select_prod_return

 

7.

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

@xplan_prod_ret

 

Back to Topic List

Summary

In this tutorial, you learned how to:

 Use Reference Partitioning
 Use Interval Partitioning
 Renaming a Table Partition
 Exchanging a Table Partition
 Use Extended Composite Partitioning
 Use Virtual Column Partitioning

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 


In-Memory Replay Banner