Manipulating partitions in Oracle Database 11g
Manipulating Partitions in Oracle Database 11g
This tutorial demonstrates how to use various partitioning
techniques in Oracle Database 11g.
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:
| 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
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
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 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.
|
| 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
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
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
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
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.
| 
Bookmark
