Nonstop Partition Operations

Oracle Partitioning delivers online partition moves, selective indexes on partitions, and asynchronously maintained global indexes to Oracle Database 12c.

By Arup Nanda

May/June 2014

The lead DBA at Acme Bank, Jane, is listening to questions from her developer and DBA visitors. As covered in the September/October 2006 Oracle Magazine article “Partition Decisions,” Acme uses the Oracle Partitioning option of Oracle Database 12c extensively. When data needs to be purged, Acme DBAs drop the partition—instead of deleting tables—to reduce database overhead. To move older data to archive tables, Acme exchanges partitions instead of physically moving them, and when rebuilding indexes, Acme rebuilds the indexes partition by partition. In general, Acme is very happy with Oracle Partitioning features, but Jane’s visitors want to know if new partition features in Oracle Database 12c can address some routine pain points they’ve been experiencing.

Jane nods and asks her visitors where they want to start.

Scott, the development lead, responds by describing the first problem. Acme has a database table named TRANS for storing account transactions; it is partitioned on the transaction date (TRANS_DT) column and adds one new partition per month. The table has a global index on the transaction ID (TRANS_ID) column and a local index on the account number (ACC_NO) column. (Listing 1 includes a script that creates the table and indexes.)

Code Listing 1: Creation of the TRANS table and its indexes

create table trans (
   trans_id        number,
   trans_dt        date,
   acc_no          number,
   trans_amt       number
)
partition by range (trans_dt)
(
   partition y13m11 values less than (to_date('2013-12-01','yyyy-mm-dd')) 
   tablespace tsslow,
   partition y13m12 values less than (to_date('2014-01-01','yyyy-mm-dd')) 
   tablespace tsslow,
   partition y14m01 values less than (to_date('2014-02-01','yyyy-mm-dd')) 
   tablespace tsfast
);
-- Indexes: PK_TRANS – global IN_TRANS_ACC_NO - local
create unique index pk_trans on trans (trans_id) global tablespace tsglobal
/
alter table trans add constraint pk_trans primary key (trans_id) using index
/
create index in_trans_acc_no on trans (acc_no) local
(
   partition y03m11 tablespace tsslow,
   partition y03m12 tablespace tsslow,
   partition y04m01 tablespace tsfast
);

To reduce costs, Acme uses different types of storage. The newer partitions—accessed most often—are stored in the TSFAST tablespace, which is on very fast, expensive storage, whereas the older partitions—accessed less frequently—are stored in the TSSLOW tablespace on slower, less expensive storage. At the end of each month, the DBAs move the previous month’s partition to the TSSLOW tablespace to make room for the new month on the TSFAST tablespace. The problem, Scott explains, is that when the partition is moved to the TSSLOW tablespace, the partition being moved stays in read-only mode, so no data manipulation language (DML) can execute against that partition until the move command finishes executing. Because Acme’s applications perform DML against the TRANS table—including the moving partition—Scott has to bring the applications down until the partition move is completed, to the displeasure of the application users.

Scott wants to know if there is a better way to move the partitions without having an impact on the application users. Yes, there is, assures Jane. In Oracle Database 12c, partition movement does not require the partition being moved to be in read-only mode, so it can be 100 percent available for DML during the move. To demonstrate that availability, she steps through an example.

First, because the new month is February, she creates the partition for February 2014 (named Y14M02) on the TSFAST tablespace, using the following SQL statement:

alter table trans add partition y14m02 values 
less than (to_date('2014-03-01','yyyy-mm-dd')) tablespace tsfast;

Next she moves the partition for January 2014 to the TSSLOW tablespace with the following SQL statement:

alter table trans 
move partition y14m01 tablespace tsslow update indexes;

Jane reminds her visitors that the UPDATE INDEXES clause in the SQL statement ensures proper updating of global as well as local indexes to avoid making them unusable. Scott quickly points out that the same UPDATE INDEXES clause is what makes the Y14M01 partition of the TRANS table read-only. Only some queries against this partition will “work” during the move, whereas other DML operations will hang, waiting for the DML lock on the partition to be released. Scott wants to know if it’s possible to allow all DML operations on the TRANS table during the partition move.

Jane nods and explains that in Oracle Database 12c, a new clause enables this move to be completely online, with all DML operations available. After asking Scott not to stop the Acme applications that use the TRANS table, she performs the partition move operation, using the following SQL statement:

alter table trans 
move partition y14m01 tablespace tsslow online update indexes;

What keeps the partition online during the move, Jane points out, is the ONLINE keyword in the statement. This keyword enables the move partition operation to occur online without requiring the applications to shut down or limiting operations to read-only queries. Scott is pleased, but he wants to confirm the status of the global index and the partitions of the local index on this table. First Jane checks the status of the global index, using the following query:

select status
from user_indexes
where index_name = 'PK_TRANS';
STATUS
—————————
VALID

The status shows VALID, meaning exactly that: the global index is still valid and does not need rebuilding. Next, she checks the status of the partitions of the local index, using the following query:

select partition_name pname, status
from user_ind_partitions
where index_name = 'IN_TRANS_ACC_NO';
PNAME  STATUS
—————  —————————
Y03M11 USABLE
Y03M12 USABLE
Y04M01 USABLE

The status of all the partitions of the local index is USABLE after the partition move, meaning that they do not need rebuilding.

The partition move completes while the applications are up and running and all the DML activities on the table are going on as usual. Because the operation of moving the partitions to slower tablespaces takes several hours and occurs once every month, Acme used to incur substantial losses during these activities due to the resulting application shutdown. With the ability to move a partition while it stays online, the table can continue to handle DML operations, and applications can stay up during the monthly partition moves. Scott is delighted.

With Scott’s issue resolved, Jane turns to Traci, another DBA, who has a different concern. The users select data from the TRANS table by filtering on several columns, so the DBAs are asked to create multiple indexes on various columns of the table. However, indexes consume precious database space. When the DBAs try to save space by not creating some of the indexes, the queries filtering on those columns perform poorly. Users typically select from more-recent partitions, so DBAs try to save some space by compressing the older partitions of the indexes, but the total space consumption is significant even with the compression. Space limitations force the DBAs to drop indexes on some columns to make room for new indexes on other columns, and there is a constant tug-of-war between the development and DBA teams due to the issue of index creation on columns. Traci wants to know if there is a way to create as many indexes as needed and still save space.

Yes, there is, assures Jane. Indexes, when created, are for all the partitions of the table, whether or not they are useful for all of them. Oracle Database 12c includes a new feature called partial indexes, she explains, that enables the DBA to create indexes on specific partitions only, rather than on all of them. Pointing to the aforementioned TRANS table, Traci advises that the partitions of the current month and the previous month are usually queried a lot. Partitions for the prior months are not queried as much, and when they are, users are relatively patient about the reduced performance if the indexes are not present. With this information, Jane goes on to mark partitions where an index is needed and where it can be eliminated, as shown in the following SQL:

alter table trans modify partition y13m11 indexing off;
alter table trans modify partition y13m12 indexing on;
alter table trans modify partition y14m01 indexing on;

The INDEXING OFF clause tells the database that an index is not needed on that partition and should not be created. Because only the current partition (Y14M01) and the prior month’s partition (Y13M12) are queried often, indexes on those partitions will be useful. Therefore Jane marks those partitions with the INDEXING ON clause and the rest with INDEXING OFF.

Next Jane creates a local index on the ACC_NO column, as shown in the following SQL:

create index in_trans_acc_no on trans (acc_no) local indexing partial;

She points out the very important INDEXING PARTIAL clause in this SQL statement, which instructs Oracle Database not to create indexes on all the partitions of the TRANS table. This clause enables index creation for only those partitions with INDEXING set to ON. No usable index is created on the partitions with INDEXING set to OFF. To confirm the result of the settings, Jane selects the partitions of the newly created IN_TRANS_ACC_NO index, using the following SQL:

select partition_name pname, status
from user_ind_partitions
where index_name = 'IN_TRANS_ACC_NO';
PNAME  STATUS
—————  ——————————
Y13M11  UNUSABLE
Y13M12  USABLE
Y14M01  USABLE

As expected, there are three partitions in this local index, because there are three partitions in the TRANS table. However, Jane reminds everyone, the presence of index partitions does not necessarily prove that the index partitions are actually stored; it is the state of an index partition that is important. Segments are physical objects that take up space, so Jane checks for the presence of segments for the IN_TRANS_ACC_NO index, using the following SQL:

select partition_name pname
from user_segments
where segment_name = 'IN_TRANS_ACC_NO';
PNAME
————————
Y13M12
Y14M01

Drawing everyone’s attention to the output, Jane points out that there are only two segments—not three. Even though there are three index partitions, only two have corresponding stored segments. The third index partition (Y13M11) does not have a stored segment, because Jane marked that table partition with INDEXING OFF, making the index status UNUSABLE and causing it not to consume any space.

Although less frequently, users also run queries that filter on the ACC_NO column, and the queries may access data from the Y13M11 partition. Because that index partition does not have a corresponding segment, Traci wants to know if these queries will return errors. Fair question, says Jane. The answer is simple: the database will act appropriately, depending on the presence of the segment: queries on partitions with index segments will perform index scans, and those on partitions without index segments will perform full table scans automatically. Jane demonstrates this behavior by displaying the access paths of a query, as shown in Listing 2. The first query filters on the TRANS_DT column by

to_date('13-NOV-2013','dd-mon-yyyy')

The query selects data from the Y13M11 partition, where indexing is off (INDEXING OFF) and there is no index segment. Jane shows everyone that the query performed a full table scan, as evidenced by TABLE ACCESS FULL in the access path, right after the query in the output. Next she executes another query; this one selects from the Y13M12 partition, which has indexing on (INDEXING ON) and therefore has an index segment. Here the query performed an index scan, as shown by TABLE ACCESS BY LOCAL INDEX ROWID BATCHED in the output.

Traci is not completely convinced. She wonders what happens when a query selects from two partitions—one with an index partition and one without. It’s handled automatically, assures Jane. Oracle Database 12c transparently rewrites the query in two parts, she explains, one using the index for the partition where an index is present and the other one using a full table scan when no index is present. The two parts are connected by UNION ALL.

Traci asks if the Acme DBAs need to drop all the indexes and re-create them with the INDEXING PARTIAL clause. There are a lot of indexes, and rebuilding them would take a long time. No rebuilding is necessary, Jane assures her. An existing index such as IN_TRANS_ACC_NO can be transformed with the following SQL:

alter index in_trans_acc_no indexing partial;

To recap, Jane continues, the INDEXING clause (INDEXING ON or INDEXING OFF) specifies whether the index partition on a table will actually have stored segments. For queries that could use an index, the optimizer will automatically choose the index if it’s appropriate and available for that partition and will use a full table scan otherwise. Not storing the index partitions of the less frequently used table partitions reduces the amount of index storage. This enables Traci to create as many indexes as are needed by users at Acme. The older partitions will not have space-consuming indexes, but the more-recent partitions will have indexes, so queries on that recent information will perform better. This is a win-win for both the DBA and the development teams, because they will get better performance with indexes being in the right places and not incurring a significant space penalty. Traci is ecstatic.

Jonas, another DBA visitor in Jane’s office, brings up a pain point he is experiencing, again in the realm of partition management and indexes. When Acme drops an old partition from tables during data purging, it uses the following SQL:

alter table trans drop partition y13m11;

However, this operation changes the global index status on this table to UNUSABLE. Jonas demonstrates this to Jane and her visitors, using the following query:

select status
from user_indexes
where index_name = 'PK_TRANS';
STATUS
———————————
UNUSABLE

To avoid making the index unusable, Jonas uses the UPDATE INDEXES clause, as shown in the following SQL:

alter table trans drop partition y13m12 update indexes;

This keeps global indexes usable, but the command takes as much time as the index update, which is significant. But when the operation performs the index update as it drops the partition, it puts stress on the database and negatively affects other operations. Jonas wants to know if there is a way to reduce this impact on the database.

Yes, there is, Jane responds. In Oracle Database 12c, when a partition is dropped (or truncated), the global indexes are not automatically invalidated. From the perspective of the database, truncating or dropping a partition is like removing an object from the database’s metadata. Using this principle, Oracle Database 12c records the partitions dropped or truncated and filters out their corresponding index entries at index access time. These filtered index entries are known as orphaned entries. Because no index entry is actually modified during the dropping or truncating of partitions, using the filter process is much faster than updating the global index. A new column—ORPHANED_ENTRIES in the USER_INDEXES view—shows the presence of orphaned entries in the index. Jane uses the following SQL to check for orphans in the only global index on the TRANS table: PK_TRANS.

select status, orphaned_entries
from user_indexes
where index_name = 'PK_TRANS';
STATUS ORP
—————— ————
VALID  YES

Jane shows everyone that the value for the ORPHANED_ENTRIES column is YES, meaning that the index has orphans, due to the dropping of the partition. Jonas is skeptical. He asks if, given that the index was not actually updated and has orphans, user queries will be able to use the index successfully. Absolutely, assures Jane, pointing to the status of the index in the output: VALID. To confirm this status, she checks the access path of a query that uses the global index, as shown in Listing 3. In the output, Jane points to the line that shows the access with the global index PK_TRANS, confirming that the index is used, even with the orphans.

Jonas asks if the orphans are going to be there in that global index forever. No, answers Jane, the orphans are cleaned up by Oracle Database via a scheduler job named PMO_DEFERRED_GIDX_MAINT_JOB, owned by the SYS user. The job runs every day during the maintenance window defined in the database and calls a program named PMO_DEFERRED_GIDX_MAINT, which, in turn, executes a stored procedure named CLEANUP_GIDX_INTERNAL in the DBMS_PART package. This procedure cleans up orphan entries from global indexes. Additionally, a DBA can use the following SQL statement to manually remove the orphans immediately:

alter index pk_trans coalesce cleanup;

So, to recap, Jane concludes, dropping a partition by using the UPDATE INDEX clause does not actually update the global indexes, but it records the dropped partitions, making its corresponding index entries orphans, which are later cleaned up by an automated job. In other words, the UPDATE INDEX clause updates the indexes asynchronously. This enables the partition drop operation to complete its execution immediately—without waiting for a time-consuming index update to complete. It also pushes the resource-intensive index update operation to a later time (when the database is less loaded), reducing the impact of that operation on the performance of the database. Jonas couldn’t be happier.

The Oracle Partitioning option of Oracle Database 12c provides some important and useful enhancements, a few of which are described in this article. Table 1 shows some common issues and how the new features help solve them.

Table 1: Partitioning issues and Oracle Partitioning solutions

Jane’s developer and DBA visitors are satisfied that online partition moves, selective indexes on partitions, and asynchronously maintained global indexes will significantly improve processes, save time, and improve the user experience at Acme.