Database, SQL and PL/SQL

Open for Exchange

FOR EXCHANGE in Oracle Database 12c Release 2 takes the detective work out of partition exchanges.

By Connor McDonald

November/December 2016

In the next few columns, I’ll spend some time looking at new features in Oracle Database 12c Release 2. These features come from the “12 Things About Oracle Database 12c” presentation series that Chris Saxon and I—the Ask Tom team—gave at Oracle OpenWorld 2016 in San Francisco. In this article, I’ll take a look at improvements to the EXCHANGE PARTITION functionality in Oracle Database 12c Release 2.

Background

Long before big data and the “four Vs”—volume, velocity, veracity, and variety—became the terminology du jour, there was a different kind of big data for database administrators: data that is “just plain big.” Any database object that grows continuously ultimately becomes more difficult to manage, due to its size.

Since Oracle Database Release 7.3, there have been facilities for “carving up” a large database table into smaller, more manageable portions. This commenced with partition views, which were superseded in Oracle8 Database with the partitioning option. A core principle of partitioning is that the less data you need to “digest”—whether loading it, querying it, or reorganizing it—the more efficient the task at hand will be.

Consider a scenario in which thousands or millions of rows located within a single partition require some data changes. The changes could be updates to the existing data or the addition of a new day’s or week’s worth of data. Whatever the case, given that, within the partitioned table, the data might be compressed or be heavily indexed for analytic queries, large-scale data operations might impose a heavy burden on server resources or cause disruption to users of that data. The ability to exchange partitions enables you to perform those activities on a database segment isolated away from the table and then perform a quick data dictionary operation to switch that segment to be within the table definition.

A typical exchange partition task progresses as follows: suppose you have an existing table T consisting of four partitions, each of those partitions is a database segment, and each occupies space within the database storage as shown in Figure 1. Note that although the table might have four partitions defined in the sequence P1, P2, P3, P4, I’ve deliberately not shown the segments in that sequence in Figure 1, because they are just allocated space like any standard database segments. It is the data dictionary definition (the arrows in Figure 1) that instructs the database where the contents of table T are located.

o66mcdonald-f1

Figure 1: Segments (partitions) of table T located in storage

Let’s assume that the current task is to update most of the data in partition P3. To simply perform a bulk UPDATE statement against table T could be very resource-intensive or affect queries against that data. An alternative strategy is to first copy the data from partition P3 into a new table, T_EXCH, making the necessary changes as the copy progresses.

For example:

SQL> create table T_EXCH as
  2  select upper(forename),
     salary * 1.1, ...
  3  from T partition ( P3 );
Table created.

The database storage segments now look like Figure 2, with table T being unchanged and still accessible for queries by users and with the new table T_EXCH also being a segment populated with data.o66mcdonald-f2
Figure 2: Segments after the creation of table T_EXCH

To complete the update, and as the name T_EXCH suggests, it’s time to exchange the contents of partition P3 with those of table T_EXCH. But recall that the only information that table T requires to locate its data is the data dictionary definition for the segments that constitute its partitions. So rather than moving any data, all that is required is updating the data dictionary definitions to indicate that table T_EXCH is, in fact, partition P3, as follows and illustrated in Figure 3:o66mcdonald-f3
Figure 3: Definition of table T after the partition exchange

SQL> alter table T exchange partition P3
  2  with table T_EXCH;
Table altered.

No data has been moved; only the data dictionary definition (the arrows in Figure 3) has been changed. What was partition P3 will now be called T_EXCH, and the T_EXCH table is now partition P3. Also, the only period of time that table T was locked was during the few milliseconds while the data dictionary definitions were being updated. Using EXCHANGE PARTITION is a very efficient means of performing data maintenance on partitioned tables.

Not as Simple as It Seems

The definitions of the source partitioned table and the table to be exchanged with a partition must be comparable. It clearly makes no sense to swap a partition of a sales table with that of a table of street addresses. The database checks to ensure that an exchange operation is valid:

SQL> alter table EMPLOYEES exchange partition E1
  2    with table DEPARTMENTS;
alter table EMPLOYEES exchange partition E1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in
ALTER TABLE EXCHANGE PARTITION

To ensure that the definitions between the source partitioned table and the exchange table are identical, a common technique is to first clone an empty copy of the source table:

SQL> desc T
 Name                          Null?    Type
—————————————————————————————— ———————— ———————————
 X                                      NUMBER(10)
 Y                                      NUMBER(10)
SQL> create table T_EXCH
  2  as select * from T
  3  where 1=0;
Table created.
SQL> desc T_EXCH
 Name                          Null?    Type
—————————————————————————————— ———————— ———————————
 X                                      NUMBER(10)
 Y                                      NUMBER(10)

The WHERE 1=0 condition ensures that no rows will be contained in the T_EXCH table, which can then be populated with the appropriate data and exchanged into a partition of table T when required. But in this example, although the column definitions are seemingly identical, look at what happens when I attempt to perform an exchange:

SQL> alter table T exchange partition P1
  2    with table T_EXCH;
alter table T exchange partition P1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in
ALTER TABLE EXCHANGE PARTITION

A common mistake administrators make when they see this error message is to change the command to attempt to relax the validation the database is performing. From the documentation, they will see the WITHOUT VALIDATION clause, which is described as follows: “Specify WITHOUT VALIDATION if you do not want Oracle Database to check the proper mapping of rows in the exchanged table. If you omit this clause, then the default is WITH VALIDATION.”

But this clause is for conveying information to the database about the validity of the rows in the table to be exchanged, not the definition of the columns. Hence, adding the WITHOUT VALIDATION clause yields no benefit:

SQL> alter table T exchange partition P1
  2    with table T_EXCH without validation;
alter table T exchange partition P1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in
ALTER TABLE EXCHANGE PARTITION

And similarly, hypotheses from administrators that the issue might be related to indexes on the partitioned table will also not help:

SQL> alter table T exchange partition P1
  2    with table T_EXCH excluding indexes without validation;
alter table T exchange partition P1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in
ALTER TABLE EXCHANGE PARTITION

Even delving into the data dictionary to check on the column definitions does not reveal any obvious differences. Both tables have the same columns, the same column ordering, and the same datatypes:

SQL> select column_id, column_name, data_type
  2  from   user_tab_columns
  3  where  table_name = 'T';
 COLUMN_ID COLUMN_NAME                    DATA_TYPE
—————————— —————————————————————————————— ———————————
         1 X                              NUMBER
         2 Y                              NUMBER
2 rows selected.
SQL> select column_id, column_name
  2  from   user_tab_columns
  3  where  table_name = 'T_EXCH';
 COLUMN_ID COLUMN_NAME                    DATA_TYPE
—————————— —————————————————————————————— ———————————
         1 X                              NUMBER
         2 Y                              NUMBER
2 rows selected.

I have arrived at a frustrating impasse. My examination of the data dictionary tells me that the column definitions are the same, but the exchange operation insists that they are not.

The Evolution of Columns

In early versions of Oracle Database, once columns were defined in a table, they were there to stay! Their names could not be changed, and if you wanted to remove one, it meant backing up the data, re-creating the table, and reloading the data without the dropped column. But there have been advances in Oracle Database: columns can be renamed, dropped, marked as unused, invisible to applications, or virtual. This flexibility means that the definition of the table’s columns is more than just what columns are currently visible from a simple DESC command or a query to USER_TAB_COLUMNS.

I’ll return to the example, but rather than looking at USER_TAB_COLUMNS, I will explore a similar data dictionary view, USER_TAB_COLS.

SQL> select column_id, column_name, data_type, hidden_column
  2  from   user_tab_cols
  3  where  table_name = 'T';
 COLUMN_ID COLUMN_NAME                    DATA_TYPE    HID
—————————— —————————————————————————————— ———————————— ———
         1 X                              NUMBER       NOSYS_C00002_16090207:56:29$     DATE         YES
         2 Y                              NUMBER       NO
SQL> select column_id, column_name, data_type, hidden_column
  2  from   user_tab_cols
  3  where  table_name = 'T_EXCH';
 COLUMN_ID COLUMN_NAME                    DATA_TYPE    HID
—————————— —————————————————————————————— ———————————— ———
         1 X                              NUMBER       NO
         2 Y                              NUMBER       NO

The disparity between the columns for table T and table T_EXCH is now apparent. There is a hidden column with a system-assigned name SYS_C00002_16090207:56:29$. The question is how this column got here and what it represents. I can make some inferences from its name: it is perhaps the second column (C00002), and “something” happened to that column on September 2 at 7:56 a.m. (16090207:56:29). But that does not help with my partition exchange.

As I said at the start of this article, one of the motivations for partitioning a table is to avoid having to deal with large-scale data operations. One such large-scale operation that might be required on a table is to drop one of its columns. In this operation, every row in the table needs to be rewritten without the dropped column’s data. And, of course, if I have partitioned a table, I probably partitioned it to avoid having to do large operations that change every single row in every partition. So rather than issuing an ALTER TABLE DROP COLUMN statement, I would probably just remove the column from the table definition but leave the column data in place, using the ALTER TABLE SET UNUSED COLUMN statement.

And that is what has happened here. When I created the table T, the definition I used was

SQL> create table T (
  2     x number(10),
  3     z date,
  4     y number(10) )
  5  partition by list ( x )
  6  (
  7    partition p1 values (10),
  8    partition p2 values (20)
  9  );
Table created.

Originally my table had three columns, but after creating it, I performed the type of operation that would be done on a large partitioned table when a column is no longer needed. I marked column Z as unused:

SQL> alter table T set unused column z;
Table altered.

Within the data dictionary, this resulted in the renaming of column Z to the system-assigned name observed earlier and the column being set to hidden. The definition of Z cannot be removed totally from the data dictionary, because any data for column Z still remains in the table. The definition must be retained to be used to instruct the database to “skip” that column data when it is encountered during SQL queries. With this information, I can manipulate my definition of T_EXCH to get it to match the definition of T:

SQL> drop table T_EXCH purge;
Table dropped.
SQL> create table T_EXCH ( x number(10), dummy date, y number(10));
Table created.
SQL> alter table T_EXCH set unused column dummy;
Table altered.
SQL> select column_id, column_name, data_type, hidden_column
  2  from   user_tab_cols
  3  where  table_name = 'T_EXCH';
 COLUMN_ID COLUMN_NAME                    DATA_TYPE    HID
—————————— —————————————————————————————— ———————————— ———
         1 X                              NUMBER       NO
           SYS_C00002_16090306:48:33$     DATE         YES
         2 Y                              NUMBER       NO
3 rows selected.

Now when I reattempt the exchange operation, the column definitions are in alignment:

SQL> alter table T exchange partition P1
  2    with table T_EXCH;
Table altered.

I have finally been able to perform the required partition exchange. Now consider a more realistic example in which, throughout the lifetime of an application, there have been numerous column additions and removals. There are also other scenarios this article doesn’t cover that result in hidden columns in the data dictionary as a means of representing the state of the table data with respect to the visible columns in the table. It can become a detailed forensic exercise just to perform a simple partition exchange.

Easier Exchange with Oracle Database 12c Release 2

Recognizing that the most common method for defining a table for partition exchange has been to use the CREATE TABLE AS SELECT syntax, all the complexities of identifying the correct column definition for the exchange candidate have been removed with a simple extension to the CREATE TABLE command in Oracle Database 12c Release 2. If you are creating a table whose intended usage is for an exchange operation, you can now simply inform the database with FOR EXCHANGE:

SQL> create table T_EXCH
  2  for exchange with table T;
Table created.
SQL> alter table T exchange partition P1
  2    with table T_EXCH;
Table altered.

The table is created with no rows and must be populated with data after it is created. You will get an error message if you combine the FOR EXCHANGE operation with a data population.

SQL> create table T_EXCH
  2  for exchange with table T
  3  as select * from T;
as select * from T
*
ERROR at line 3:
ORA-00922: missing or invalid option

Note that the FOR EXCHANGE clause is concerned with creating a viable exchange candidate based on the column definitions, not the structure of the partitions themselves. For example, consider a source table PAR that is compositely partitioned:

SQL> create table PAR (
  2     x number(10),
  3     z date,
  4     y number(10) )
  5  partition by list ( x )
  6  subpartition by hash(y)
  7  (
  8    partition p1 values (10)
  9      ( subpartition h11,
 10        subpartition h12
 11      ),
 12    partition p2 values (20)
 13      ( subpartition h21,
 14        subpartition h22,
 15        subpartition h23,
 16        subpartition h24
 17      )
 18  );
Table created.
SQL> create table EXCH_PAR
  2  for exchange with table PAR;
Table created.
SQL> alter table PAR exchange subpartition h21
  2  with table EXCH_PAR;
Table altered.

This does not prohibit the FOR EXCHANGE clause from being used with more-complex exchange requirements. Using the PAR table, I can create an exchange candidate that will be used to exchange the P1 partition (which is itself a composite of two hash partition segments).

SQL> create table EXCH_PAR_COMPOSITE
  2  partition by hash ( y )
  3  (
  4    partition h1,
  5    partition h2
  6  )
  7  for exchange with table PAR;
Table created.
SQL> alter table PAR
  2  exchange partition P1
  3  with table EXCH_PAR_COMPOSITE;
Table altered.
Summary

An EXCHANGE PARTITION operation might fail after seemingly unrelated table definition changes are carried out. This could then require a potentially complex investigation of the complete history of column changes to produce a viable exchange table candidate. With Oracle Database 12c Release 2, the EXCHANGE PARTITION process becomes simple, thanks to the new FOR EXCHANGE clause.

Next Steps

 LEARN more about Oracle Database 12c Release 2.

 DOWNLOAD Oracle Database 12c.

 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.