Manipulating Partitions in Oracle Database 11g

Purpose

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

Time to Complete

Approximately 50 minutes

Topics

This tutorial covers the following topics:

bullet Overview
bullet Prerequisites
bullet Using Reference Partitioning
bullet Using Interval Partitioning
bullet Using Extended Composite Partitioning
bullet Using Virtual Column-Based Partitioning
bullet 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 partitionwise join, set the _parallel_broadcast_enabled parameter to FALSE. Execute the following command:

ALTER SESSION SET "_parallel_broadcast_enabled"=FALSE;

 
7.

Execute the show_plan.sql script to view information about the partitionwise 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.

@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

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:

1.

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

@create_newsales

 
2.

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

@query_dict_3

 
3.

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

@insert_newsales

 
4.

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

@query_dict_4

 
5.

Execute the merge_partition.sql script to merge two partitions.

@merge_partition

 
6.

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

@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 merged 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.

@query_dict_7

 

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_return.sql script to create the PRODUCT_RETURNS table with a virtual column, which is used to partition the table.

@create_prod_return

 
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:

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

Back to Topic List

Place the cursor over this icon to hide all screenshots.