Partitioning - Oracle 11g

Oracle partitioning provides the ideal environment for implementing infor mation lifecycle management (ILM) solutions.By maintaining and implementing partitions on separate tablespaces,DBAs can place older data on less expensive storage tiers.New data continues to be stored in tier 1 storage, while the older data can be migrated to less expensive tier 2,3,and 4 storage.In addition,the older data can be compressed, thus further reducing storage costs.

Partitioning also provides to DBAs the flexibility to make tablespaces read-only. By implementing the skip readonly tablespace Syntax to the RMAN backup architecture, significantly less space will be required for backups performed to disk and tape.

Many companies do not implement partitioning because of the additional licensing cost associated with it.If DBAs, backup administrators, developers, and senior management carefully analyze their application to look for ways to implement partitioning with read-only tablespaces,the Oracle partitioning option pays for itself.For large companies that have storage in the terabyte or even petabyte range,the Oracle partitioning option can save them money in storage and backups.

Oracle offers four main partitioning mechanisms:

  • Composite partitioning
  • Hash partitioning
  • List partitioning
  • Range partitioning

Based on the business requirement,each of the partitioning methods has its appropriate justified need in today’s corporations.Oracle Database 11g improves partitioning options and provides new options to meet today’s demanding requirements.

New to Oracle Database 11g are numerous techniques for partitioning table data to increase the performance and organization of your corporate data.These partitioning techniques include the following:

  • Reference that allows tables with a parent-child relationship to be logically equipartitioned by inheriting the partition key from the parent table without duplicating the key columns
  • Interval that automatically creates maintenance partitions for range partitions
  • Extended composite that allows data to be partitioned along two dimensions
  • Virtual columns that allow virtual columns to be defined as partition key columns

Partition Advisor

Partitioning advice is available within the SQL Access Advisor as part of Enterprise Manager or the command-line interface.In Oracle Database 11g,the SQL Access Advisor will also recommend partitions.

In addition to the normal recommendations to create or drop indexes, materialized views,and materialized view logs,Oracle will recommend to partition existing tables and indexes to improve performance.

The Partition Advisor provides the appropriate SQL syntax to create atemporary partitioned table,copy data,and rename the table to the original table name. Furthermore,the Partition Advisor will show the potential performance improvements from implementing the recommended partitions. The Partition Advisor is integrated with the SQL Access Advisor and is licensed under Oracle’s Tuning Pack.

In the initial SQL Access Advisor screen,available as an option on the Advisor Central screen, you can click the option to recommend new access structures, as shown in Figure.

Figure SQL Access Advisor Initial Options screen

SQL Access Advisor Initial Options screen

Click the Next button,and you will be routed to the Workload Source screen.Here, you can select the source of the workload to analyze and select any filtering criteria.For demonstration purposes, keep the defaults on this screen.Click the Next button,and you will continue with the SQL Access Advisor to the Recommendation Options screen. On this screen, you can choose to select advice about indexes, materialized views,and partitions,as shown in Figure.

Figure SQL Access Advisor Recommendation Options screen

SQL Access Advisor Recommendation Options screen

Click the Next button, and you will be routed to the Scheduler screen.Click the Next button one more time to be routed to the Review Screen. Finally, click the Submit button to create a Scheduler job.

Once the job completes successfully, you can view the results of the SQL Access Advisor. Once again from the Advisor Central screen, locate the completed SQL Access Advisor result, as shown in Figure.

Figure SQL Access Advisor Results screen

SQL Access Advisor Results screen

Click the respective row of the SQL Access Advisor, and click the View Result button.You will be redirected to the Advisor Central Summary page, as shown in Figure.

Figure SQL Access Advisor Summary screen

SQL Access Advisor Summary screen

The two graphs on this screen show incredible potential in performance improvements. The workload IO cost is reduced from 6473 to 138.The query improvement is expected to improve by a factor of ten times.

Click the Recommendations tab and notice the ID column on the bottom of the screen regarding recommendations for implementation.Please notice that there are four action items and action types with the color-coded squares shown in Figure.

Figure Select Recommendations for Implementation screen

Select Recommendations for Implementation screen
Click the ID URL,and you will drill down to the detailed recommendation screen for the specific SQL Access Advisor result,as shown in Figure.

Figure SQL Access Advisor recommendation details

SQL Access Advisor recommendation details

Notice the PARTITION TABLE action for the CUST table.Click PARTITION TABLE to review the SQL script to convert the CUST table into a partitioned table.You will notice the following sections of the partitioning advice:

  • Create new partitioned table
  • Gather table statistics
  • Copy constraints to the new partitioned table
  • Copy referential constraints to the new partitioned table
  • Populate new partitioned table with data from original table
  • Rename tables to give new partitioned table the original table name

The Partition Advisor provides the following SQL syntax to convert the CUST table into an interval-partitioned table:

You can execute this script manually in SQL*Plus or schedule this script to run as a job in Enterprise Manager.

Reference Partitions

Reference partitions are partitions based on the partitioning method of the parent table referenced by a foreign key.Reference partitioning relies on existing parent-child relation ships and is enforced by an active primary key and foreign key constraint.

The child table automatically inherits the partitioning key from the parent table without duplicating the key columns.The child table also inherits the maintenance operations from the parent table.

This reduces the amount of human error introduced by manually duplicating efforts on the child table.Prior to Oracle Database 11g,you had to allocate redundant storage for the partitioned key for both the master and child tables.In addition,you had to perform double maintenance.Oracle Database 11g saves storage and maintenance by providing the reference partitions.

To create a reference partition, you can use the keywords partition by reference (foreign_key name) as part of your create table statement.Notice that you have to provide the foreign key name.

To show how reference partitions work,let’s create the parent table called ORDER_HEADER partitioned by the ORDER_CATEGORY column.The syntax for the ORDER_HEADER table looks like this:

Now you can create a reference-partitioned table named ORDER_DETAIL based on the parent table, ORDER_HEADER, as shown here:

In this example,the line with partition by reference (fk_order_detail) creates the reference partition against the ORDER_HEADER table. Now,the ORDER_DETAIL table has the same partitions as the ORDER_HEADER with the partitioning key on ORDER_CATEGORY. Notice that the ORDER_DETAIL table does not have the ORDER_CATEGORY column.

You have successfully created the reference partition.Now,you can query the USER_PART_TABLES view to validate that the ORDER_DETAIL table is a reference partition.The following example shows you that the partition type for the ORDER_DETAIL table is a reference partition:

You will notice that the REF_PTN_CONSTRAINT_NAME column lists the foreign key name used to create the reference partition. Furthermore, you can query the USER_TAB_ PARITITIONS view to confirm that the partition name associated with the ORDER_DETAIL table is identical to the partition names associated with the ORDER_HEADER table,as shown here:

Another thing to note is that the high_values of the reference partitions are set to null.The null value indicates that the values are derived from the parent tables.

Reference partitions will colocate the reference partition in the same tablespace of the parent table if the tablespace clause is not specified.The tablespace for the reference partition can be overridden by using the dependent tables clause.Here’s an example of an alter table command using the dependent tables clause:

The new partition for PDAs for the ORDER_HEADER table will reside in the tools tablespace,while the reference partition will reside in the DOCS_D1 tablespace. You can verify the different tablespace locations by querying the USER_TAB_PARTITIONS view again,as shown here:

Reference partitioning works with range partitioning but does not support interval partitioning. Reference partitioning is supported with list,hash, and all combinations of composite partitioning.

By default,the partition name of a reference partition inherits the partition name of the referenced table if a name for the reference partition is not otherwise specified.Optionally, you can manually provide a partition name for the reference partition.

Interval Partitioning

Interval partitioning is an answered prayer for many DBAs who have to add new partitions to their data warehouse environment on a weekly, monthly,or quarterly basis manually.Having to create new partitions at the end of the week/month/quarter is a cumbersome task that can consume weekend time for DBAs.

On the flip side,some DBAs preallocate partitions for the next year or several years to avoid partition maintenance tasks.This luxury is afforded to DBAs who have a lot of storage available to them.Oracle Database 11g introduces the new interval partitioning that fully automates the creation of range partitions based on an interval threshold.Interval partitioning is an extension of range partitions.

Let’s create our first baseline interval partition and see how Oracle maintains partitions for us. Here’s a table called EBDA_ALERT_NOTIFICATIONS that houses all the database alerts for the DBAs:

You can see that CREATION_DATE is the partitioning key for this table using the numtoyminterval function based on the month. Let’s query USER_TAB_PARTITIONS to verify that this table was created accordingly. The following query example shows that high_value for the partition is 2007-01-01 00:00:00:

Now, let’s insert 12 rows to simulate 12 months of rolling partitions.These rows are fabricated, but the key point is that the CREATION_DATE column has the first day of each month for the year 2007.

As you insert rows into an internal partition, Oracle will automatically create and maintain the range partitions that exceed the high_value of range partitions.You can query USER_TAB_ PARTITIONS again and see Oracle’s magic at work,as revealed here:

Alternatively,you can convert a noninterval range partition table into an interval partition using the alter table command.If the EDBA_ALERT_NOTIFICATIONS table was not created as an interval partition table(it is a regular range partition),you can use the alter table command to convert it into an interval partition table.The following example creates the EDBA_ALERT_ NOTIFICATIONS table as a noninterval range partition table and converts it to an interval partition table:

The only caveat that DBAs and developers may have is that they lose control over the partition name because it’s system generated.The tablespace placement for each partition can be controlled using the store in clause. Even though you don’t actually have complete control, Oracle provides the mechanism to place partitions in different tablespaces.The following sample script shows you how this can be accomplished:

Notice that the store in clause lists six tablespaces, alert_d1 to alert_d6.Oracle will, in a round-robin fashion,create partitions in the tablespaces listed in the store in clause.You can see from the following query against USER_TAB_PARTITIONS that Oracle allocates the partitions in a round-robin fashion in the alert_d# tablespaces:

At the same time, you have to ask yourself,“Is the price of a naming standard compromise worth the automation?” You can clearly see that interval partitioning is one of the best features in Oracle Database 11g for day-to-day manageability.

There are additional considerations that need to be reviewed for interval partitions.Interval partitioning has these restrictions:

  • You can specify only one partitioning key column,and it must be of the NUMBER or DATE datatype.
  • Interval partitioning does not supported index-organized tables.
  • You cannot create a domain index on an interval

Extended Composite Partitioning

Extended composite partitioning enhancement is a long-awaited feature for many DBAs and developers.Extended composite partitioning capabilities have been increased to address common business problems for companies in the 21stcentury.Prior to Oracle Database 11g,the only composite partitioning schemes were range-list and range-hash partitioning. Oracle Database 11g provides the complete composite partitioning options.

In Oracle Database 11g,you now have the means to partition by two relevant date ranges.For example,you can partition a table by ORDER_DATE and SHIP_DATE or by HIRE_DATE and COMMISSION_DATE.You now have the flexibility to create a partition based on two relevant lists. For example,you can create a composite partition based on state and city or by region and product group.There are four new extensions to the composite partitioning paradigm,discussed next.

Composite List-Hash Partitioning

You can create a list partition subpartitioned by hash partitions.This enables the granularity for partition-wise joins.This new partition mechanism can increase performance and fulfills business requirements for partitioning.

Composite List-List Partitioning

Many multiterabyte partitioning implementations have the requirements to partition by list and subpartition by list.You can see that the composite list-list partitions will be one of the favorites for developers and DBAs who have relatively static data sets for partitioning keys. For this example,let’s take our documents table again.

Let’s assume that all the DBAs and developers groups across the entire organization share a common document repository,and the amount of documentation from each of the groups is enormous.We have a need to partition by the organization and then subpartition by DOCUMENT_CATEGORY.This can easily be accomplished in Oracle Database 11g using the new composite list-list partition, as shown here:

Composite List-Range Partitioning

Composite list-range partitioning will be another favorite partitioningscheme for developers and DBAs.Let’s continue with the documents table again.In the following example,the document table can be partitioned by the organization and then subpartitioned by CREATION_DATE to create the list-range partition:

Composite Range-Range Partitioning

One common implementation of composite range-range partitions is on twodate columns.Let’s create a range-range partitioned table against the ALERT_NOTIFICATIONS table.In this particular instance,the data volume for this table is expected to be enormous because thousands of systems and databases are monitored for thousands of companies.

We will create a composite range-range partition based on when the alert was generated and when the alert was actually closed.The following create table syntax shows you how to create a composite range-range partition partitioned by CREATION_DATE and s
ubpartitioned by CLOSED_DATE:

Once the range-range partition is created,let’s insert two rows into the February subpartition:

By querying directly against the subpartition,you can verify that range-range composite partitioning is working as advertised:

Virtual Column-Based Partitioning

As of Oracle Database 11g,the partition key can be based on the virtual column(s) of a table. Oracle Database 11g introduces what is known as virtual columns,which are derived by evaluation of a function or an expression.Values of virtual columns are not stored with the table; instead, virtual columns are evaluated on demand.Tables and indexes can be partitioned on virtual columns.

You can define a virtual column within a create table command or by using the alter table syntax.By allowing virtualization of the partitioning key,Oracle provides another level of enablement to meet ever-fluctuating business requirements.

By looking at the traditional HR_EMPLOYEES table,you will see the effectiveness of virtual column-based partitioning.Let’s take the HR_EMPLOYEES table as an example and create a virtual column–based partition on the SALARY and the BONUS columns:

Now, let’s create some dummy records to pinpoint where the rows are placed within the virtual partition keys:

With virtual columns,you have the same functionality as you would with any range or list partitioning key.Let’s query the HR_EMPLOYEES table and look at the rows specific to employees who make $500,000 to $1 million as their total compensation package.You will see that both Allyson and John are the winners of the super-high salary award:

In the same regard,you can see that the partition pruning for virtual partitions works like any other range or list partitioning,as shown on this explain plan:

System Partitioning

Oracle Database 11g complements Oracle’s partitioning infrastructure with the concept of system partitioning.System partitioning enables you to create a single table that is composed of multiple physical partitions.

The fascinating concept behind system partitioning is that it does not use partition keys. Because there are no partitioning keys,system partitions have no bounds for ranges or lists. At the same time,because there are no partitioning keys,you must explicitly specify the rows to the target table partition using partition-aware syntax.The mapping of a row must be specified to the partition level.

The benefit of system partitioning is that the application controls the partitioning for tables and indexes.The application controls the data placement and how it is retrieved.The database becomes the repository that provides the mechanism to break down an object into partitions without data-partitioning rules.

System partitions are created using the partition by system clause.Let’s look at the syntax to create a system partition:

System partitioning can continue to provide the benefits of partitioning as we know it (scalability,availability,and manageability),with the flexibility of data mapping in the control of the applications.To show the usefulness of system partitions,we’ll show how data is added and deleted.When you add or delete records from system-partitioned tables,you have to provide the partition-aware syntax;otherwise,you will receive an ORA-14701 error,as shown here:

SQL> insert into docs_system_parted
2 (doc_id, name)
3 values
4 (10001, 'Chapter 1: Installation and Upgrade');
insert into docs_system_parted
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used
for DMLs on tables partitioned by the System method

However,if you perform the inserts using partition-aware syntax, the rows get inserted successfully,as you can see here:

insert into docs_system_parted partition (docs_p1)
(1, 'Chapter 1 - Installation and Upgrade',
'Complete documentation for Oracle Database 11g
Installation and Upgrade by Sam and Charles', null)
1 row created.
insert into docs_system_parted partition (docs_p2)
(2, 'Chapter 2 - Diagnosibility',
'Complete documentation for Oracle Database 11g
Diagnosibility', null)
1 row created.

Like the insert command,the merge command must be performed with partition-aware syntax.Here’s an example of alter table with the merge partition command:

SQL> alter table docs_system_parted
2 merge partitions docs_p3,docs_p4
3* into partition docs_p3
SQL> /
Table altered.

Update and delete operations can be performed with or without the partition-aware syntax. In the following script examples,you can see demonstrations of deletions using the partition-aware syntax:

1 delete from docs_system_parted
2 partition (docs_p2)
3* where doc_id=10002
SQL> /
1 row deleted.
SQL> delete from docs_system_parted partition (docs_p2);
0 rows deleted.
SQL> delete from docs_system_parted partition (docs_p1);
1 row deleted.
SQL> rollback;
Rollback complete.

Partition pruning processes are not applicable to system partitions. Please be aware that when you perform updates and deletes without the partition-aware syntax,Oracle scans every partition on the table.For simplicity, you can perform updates and deletes without the partition- aware syntax, as shown here:

SQL> delete from docs_system_parted
2* where doc_id=10001
SQL> /
1 row deleted.
SQL> delete from docs_system_parted;
1 row deleted.

Enhanced Partition Pruning Capabilities

Here’s another feature that you get out of the box.Starting inOracle Database 11g, partition pruning uses the bloom filtering and is auto matically active for all joins with a partitioned object.In the previous release,the subquery pruning method was triggered on the cost-based decision model.The subquery pruning consumed internal recursive resources. he new bloom filtering is activated all the time without consuming additional resources.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics