Subscribe

Share

DBA

Automatic and Easy

Oracle Database 12c Release 2 delivers better automatic, multicolumn, and read-only partitions.

By Arup Nanda

September/October 2017

Partitioning has been a powerful tool for efficient management of large Oracle databases since its introduction in Oracle8 Database. Oracle Database 12c Release 2 delivers some powerful new partitioning features. This article explores some of these new features and how to use them.

Automatic List Partitioning

Do you remember interval partitioning, which was introduced in Oracle Database 11g? It allowed you to create a partitioned table in which new partitions were automatically created on the fly when new data came in, freeing you from the mundane task of constantly adding new partitions. But interval partitioning applied to range partitions only. With new partition support in Oracle Database 12c Release 2, you can now create list partitions on the fly.

Consider the case of a banking database in which a table named ACCOUNTS includes customer account records. You want to partition the table on the STATUS_CODE column, which contains two-letter abbreviations for the status of customers. Because status codes are discrete values, this is a perfect candidate for list partitioning. However, you don’t know all the possible status code values. The code values are created by the bank managers and assigned immediately.

Prior to Oracle Database 12c Release 2, if you hadn’t created the partition for a new STATUS_CODE value, the insert would fail. You could create a default partition to hold the values and, in that case, the insert would not fail. However, all the new rows would go into the default partition, defeating the purpose of partitioning.

That’s not the case anymore. In Oracle Database 12c Release 2, you can now create a list-partitioned table using automatic, on-the-fly list partitioning. All you have to do is know one STATUS_CODE column value and define one partition on it. Listing 1 shows how to create the ACCOUNTS table.

Code Listing 1: Automatic list-partitioned table

create table accounts
(
      acc_no          number,
      acc_type        varchar2(1),
      status_code     varchar2(2),
      acc_balance     number
)
partition by list (status_code) automatic
(
      partition p1 values ('VG')
)
/

Notice the AUTOMATIC keyword in the PARTITION clause. This instructs Oracle Database to create the partitions automatically. (Note that this table can’t have a default partition.)

Now, let’s insert a new row into ACCOUNTS:

insert into accounts values (1,'S','VG',2000);

The insert is successful because the partition for that value has already been created. Next, insert another row with the STATUS_CODE value SG, for which no partition has been defined.

insert into accounts values (2,'S','SG',3000);
There is no partition for the SG value and there is no default partition, so how does the insert succeed?

It succeeds because the partition for that value was created instantly at runtime by Oracle Database, and the inserted row found the new partition location. Let’s confirm that by checking the partitions of the ACCOUNTS table:

select partition_name, high_value
from user_tab_partitions 
where table_name = 'ACCOUNTS'
/
PARTITION_NAME  HIGH_VALUE
——————————————  ——————————
P1              'VG'
SYS_P1095       'SG'

Note how Oracle Database created a new partition—SYS_P1095—for the SG value. However, you might wonder, did the new row actually go into that partition? Let’s check:

select * from accounts partition (SYS_P1095)    

    ACC_NO ACC_TYPE STATUS_CODE ACC_BALANCE
—————————— ———————— ——————————— ———————————
         2        S          SG        3000

Yes, the partition was created at runtime to accommodate a new row for which no partition existed, and the row was inserted into that partition.

With automatic list partitioning in Oracle Database 12c Release 2, you don’t have to know all the possible values of the partitioning key to create the list-partitioned table. Oracle Database handles that for you automatically because of the AUTOMATIC keyword in the PARTITION BY LIST clause.

Oracle Database creates this partition automatically via an autonomous transaction, separate from the INSERT transaction. So, the partition stays, even if you roll back the operation.

The automatic list-partitioning strategy is only for partitions; it cannot be used for a subpartitioning clause. If you use list subpartitioning, you should use the DEFAULT list as a catch-all subpartition for the values you haven’t defined subpartitions for.

Now let’s look at a potential irritant. The partition name—SYS_P1095—is system-generated and is not intuitive enough to identify it easily as the partition for the SG value. So, how do you know which partition name to use without constantly checking the USER_TAB_PARTITIONS view, as you did earlier?

The extended partition naming convention comes in handy here. You don’t need to know the name of the partition. You can refer to the partition by the value it contains. For example, to select rows from the partition containing the SG value, use the following SQL statement:

select * from accounts partition for ('SG');     

    ACC_NO ACC_TYPE STATUS_CODE ACC_BALANCE
—————————— ———————— ——————————— ———————————
         2        S          SG        3000

The result shows the rows from the partition containing the SG value, without the partition being named explicitly in the SELECT statement. You can use this extended naming technique to refer to partitions instead of specific names in any kind of partition management operation—not just in SELECT statements. For example, you can even use extended partition naming to rename partitions. If you don’t want to call a partition by the extended reference syntax, you can rename it to a more appropriate name, such as P_SG. Here is how you can rename the partition:

alter table accounts 
rename partition for ('SG') to P_SG;

Multicolumn List Partitioning

Now consider another list-partition case. Suppose the bank wants a different strategy for partitioning the ACCOUNTS table on not one but two columns: STATE_CODE, which contains the two-letter abbreviation for US states, and ACC_TYPE, which shows the type of the account, such as savings, checking, money market, and so on. Because possible values in both columns are discrete, list partitioning is the only choice. But how can you use both columns as partition keys? Remember, you have to use both columns as partition keys, not use one column for a partition and the other for a subpartition. The partition and subpartition combination would make a composite-partitioned table—not just a partitioned table—and eliminate the possibility of composite partitioning later.

In Oracle Database 12c Release 2, you can create a list-partitioned table with two or more columns as partition keys. Listing 2 shows the SQL statements to create the table. Note the two columns in the PARTITION BY clause.

Code Listing 2: List partitioning with two columns

create table accounts
(
      acc_no          number,
      acc_type        varchar2(1),
      state_code      varchar2(2),
      acc_balance     number
)
partition by list (state_code,acc_type)
(
      partition ct_s values (('CT','S')),
      partition ny_s values (('NY','S')),
      partition ct_c values (('CT','C')),
      partition ny_c values (('NY','C'))
)
/

After the table is created, you can check the partitions in the data dictionary using the SQL statement shown in Listing 3. The HIGH_VALUE column shows the two partition values.

Code Listing 3: Checking partitions for two-column list-partitioned table

select partition_name, high_value
from user_tab_partitions
where table_name = 'ACCOUNTS'
/
PARTITION_NAME  HIGH_VALUE
——————————————  —————————————————
CT_C            ( 'CT', 'C' )
CT_S            ( 'CT', 'S' )
NY_C            ( 'NY', 'C' )
NY_S            ( 'NY', 'S' )

How does Oracle Database decide which partition to insert a row into in this case? Let’s find out with an example. First, insert a row:

insert into accounts values (1,'S','CT',2000);

Because the values of the STATE_CODE and ACC_TYPE columns are CT and S, respectively, the inserted row should be located in the CT_S partition. Let’s confirm it by selecting rows from that partition:

select * from accounts partition (ct_s);    

    ACC_NO ACC_TYPE STATE_CODE ACC_BALANCE
—————————— ———————— —————————— ———————————
         1        S         CT        2000

Perfect! The row went to the right partition, as expected. This is an example with only two columns. You can define list partitioning on multiple columns to suit your specific business needs.

So far, you’ve learned about two very powerful features: automatic list partitioning and list partitioning on multiple columns. You might now be wondering if it’s possible to combine these two features to create an automatic list-partitioned table on multiple columns.

The answer is, yes, of course. Listing 4 shows how to create the same ACCOUNTS table with automatic list partitioning on two columns: STATE_CODE and ACC_TYPE.

Code Listing 4: Automatic list partitioning on multiple columns

create table accounts
(
  acc_no       number,
  acc_type     varchar2(1),
  state_code   varchar2(2),
  acc_balance  number
)
partition by list (state_code,acc_type) automatic
(
  partition ct_s values (('CT','S'))
)
/

Now let’s insert a row into this table:

insert into accounts values (2,'S','NY',8000);

Note that NY is the value in the STATE_CODE column. There is currently no partition for this value, but as you saw earlier, Oracle Database will create new partitions for the values found in new rows and will place the rows in their appropriate partitions. You can confirm that by selecting from the partition. As you learned earlier, there is no need to get and use the exact name of the system-generated partition. You can refer to the partition by the extended partition name syntax:

select * from accounts partition for ('NY','S')
/

    ACC_NO ACC_TYPE STATE_CODE ACC_BALANCE
—————————— ———————— —————————— ———————————
         2        S         NY        8000

Read-Only Partitions

Oracle Database 11g introduced a new feature called read-only tables that allows you to mark a specific table as read-only, preventing any data manipulation language (DML) statements from affecting that table. This is a powerful feature, but in very large databases with partitioned tables, you probably want to make only a few partitions (or subpartitions) of a table—not the entire table—read-only. For example, suppose you have tables that are range-partitioned on a date column, and you want to make the 2015 partitions read-only, not the entire table. In Oracle Database 12c Release 2, you can do this.

Let’s consider the ACCOUNTS table in Listing 5. This table is range-partitioned on the LAST_TRANS_DT column and list-subpartitioned on the STATE_CODE column with one partition per year.

Code Listing 5: Range-list, composite partitioned table

create table accounts
(
        acc_no          number,
        acc_type        varchar2(1),
        state_code      varchar2(2),
        acc_balance     number,
        last_trans_dt   date
)
partition by range (last_trans_dt)
subpartition by list (state_code)
(
      partition p2015 values less than (to_date('01-jan-2016','dd-mon-yyyy'))
      (
            subpartition p2015_ct values ('CT'),
            subpartition p2015_ny values ('NY'),
            subpartition p2015_def values (default)
      ),
      partition p2016 values less than (to_date('01-jan-2017','dd-mon-yyyy'))
      (
            subpartition p2016_ct values ('CT'),
            subpartition p2016_ny values ('NY'),
            subpartition p2016_def values (default)
      ),
      partition p2017 values less than (to_date('01-jan-2018','dd-mon-yyyy'))
      (
            subpartition p2017_ct values ('CT'),
            subpartition p2017_ny values ('NY'),
            subpartition p2017_def values (default)
      )
)

/

Now, suppose you want to make sure no one can modify the data in the 2015 partition for Connecticut (CT). Here is how you can make that subpartition (of partition “p2015” with “CT”) read-only:

alter table accounts 
modify subpartition p2015_ct read only;

After running this statement, if you attempt to update the data in that subpartition, you will get an error:

insert into accounts 
values (3,'C','CT',4000,'1-jul-2015')
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or 
subpartition cannot be modified.

Of course, you can revert the subpartition to the read/write state by using the same ALTER command—with READ WRITE instead of READ ONLY—to allow rows to be inserted into the subpartition.

This ability to make a partition (or subpartition) read-only (or read/write) is not just for the ALTER statement. You can also specify the read attribute of the partition when the table is created, as shown in Listing 6.

Code Listing 6: Creating read-only partitions

create table accounts
(
        acc_no          number,
        acc_type        varchar2(1),
        state_code      varchar2(2),
        acc_balance     number,
        last_trans_dt   date
)
partition by range (last_trans_dt)
subpartition by list (state_code)
(
      partition p2015 values less than (to_date('01-jan-2016','dd-mon-yyyy'))
      read only
      (
            subpartition p2015_ct values ('CT'),
            subpartition p2015_ny values ('NY'),
            subpartition p2015_def values (default)
      ),
      partition p2016 values less than (to_date('01-jan-2017','dd-mon-yyyy'))
      read write
      (
            subpartition p2016_ct values ('CT'),
            subpartition p2016_ny values ('NY'),
            subpartition p2016_def values (default)
      ),
      partition p2017 values less than (to_date('01-jan-2018','dd-mon-yyyy'))
      read only
      (
            subpartition p2017_ct values ('CT') read write,
            subpartition p2017_ny values ('NY'),
            subpartition p2017_def values (default)
      )
) /

When you define the read attribute of an object, the child objects inherit that attribute. For example, if a partition is read-only, all of its subpartitions will be read-only as well. You can override the attribute of the subpartition by explicitly specifying it, as shown in Listing 6 for the p2017_ct subpartition.

You can check the read attribute of partitions and subpartitions from the READ_ONLY column in the USER_TAB_SUBPARTITIONS view:

select partition_name, subpartition_name, read_only
from user_tab_subpartitions
where table_name = 'ACCOUNTS'
/
PARTITION_NAME  SUBPARTITION_NAME  READ_ONLY
——————————————  —————————————————  —————————
P2015           P2015_CT           YES
P2015           P2015_DEF          YES
P2015           P2015_NY           YES
P2016           P2016_CT           NO
P2016           P2016_DEF          NO
P2016           P2016_NY           NO
P2017           P2017_CT           NO
P2017           P2017_DEF          YES
P2017           P2017_NY           YES

What if an application has a bug that might inadvertently insert a row into the wrong subpartition? Let’s attempt to do exactly that with the ACCOUNTS table created in Listing 6:

insert into accounts 
values (5,'S','NY',3000,'1-jul-2017');

Because the subpartition was created as read-only, the insert will fail:

insert into accounts 
values (5,'S','NY',3000,'1-jul-2017')
            *
ERROR at line 1:
ORA-14466: Data in a read-only partition or 
subpartition cannot be modified.

You can change the attribute easily using the following SQL statement:

alter table accounts 
modify partition p2017 read write;

Now let’s insert a row with “2017” in the value:

insert into accounts 
values (5,'S','NY',3000,'1-jul-2017');

1 row created.

The insert went through because the subpartition is now in read/write mode.

Online Conversion

Now that you have learned about powerful new partitioning features, you might want to convert some unpartitioned tables to partitioned ones. And, like most organizations, yours will likely want that to be done with as little downtime as possible. In Oracle Database 12c Release 2, you can do that conversion online. You can convert an unpartitioned table to a partitioned one even when DML statements are being executed against it. And, what’s more, the conversion partitions the indexes of the table appropriately.

Let’s see how that works. First, create an unpartitioned table named ACCOUNTS and insert some rows into it:

create table accounts
(
        acc_no        number,
        acc_type      varchar2(1),
        state_code    varchar2(2),
        acc_balance   number,
        last_trans_dt date
)
/

insert into accounts 
values (1,'S','CT',2000,'1-jul-2014')
/
insert into accounts 
values (2,'S','NY',8000,'01-jul-2016')
/
Commit
/

Now let’s create four indexes on the table on different columns (note that one of the indexes is unique):

create unique index pk_acc on accounts (acc_no);
create index in_acc_balance on accounts(acc_balance);
create index in_last_trans_dt 
on accounts(last_trans_dt);
create index in_acc_type on accounts (acc_type);

Now, let’s convert the table to a list-partitioned table with the STATE_CODE column as the partition key. This conversion operation should be done when the table is subject to DML statements. The conversion’s effect on indexes will be mixed. Some indexes, but not all, will be partitioned based on the type of index and what you ask Oracle Database to do with them, as you will see in the example below.

While the DML statements are going on, convert the table to a list-partitioned table using the SQL statement in Listing 7:

Code Listing 7: Converting a table to a list-partitioned table

alter table accounts
modify
partition by list (state_code) automatic
(
  partition p1 values ('CT')
)
online
update indexes
(
   pk_acc  global,
   in_acc_type local,
   in_acc_balance global
          partition by range (acc_balance)
          (
              partition p5000 values less than (5000),
              partition pmax values less than (maxvalue)
          )
)
/

Note the ONLINE keyword in this ALTER statement, which results in the table being converted online. Now check the table for partitions:

select partition_name, high_value
from user_tab_partitions
where table_name = 'ACCOUNTS'
/

PARTITION_NAME  HIGH_VALUE
——————————————  ——————————
P1              'CT'
SYS_P1103       'NY'

That’s it. The table has been converted to a partitioned one, all online. The UPDATE INDEXES clause updates the indexes as a part of the operation.

Now let’s see the impact of the different index options on this newly converted partitioned table. First, let’s see if the indexes are partitioned by using the following SQL statement:

select index_name, partitioned
from user_indexes
where table_name = 'ACCOUNTS'

INDEX_NAME           PARTIONED
—————————————————    —————————
IN_ACC_BALANCE       YES
IN_ACC_TYPE          YES
PK_ACC               NO
IN_LAST_TRANS_DT     NO

All the four indexes are present in the newly partitioned table. Oracle Database maintained them automatically, which is a result of the UPDATE INDEXES clause. Because you explicitly specified the IN_ACC_TYPE index to be local, the index was partitioned as well to match the table. For the IN_ACC_BALANCE index, you explicitly asked it to be global partitioned, so it became exactly that. You asked the PK_ACC index to be global but not partitioned, so it is not partitioned. Finally, for the IN_LAST_TRANS_DT index, you didn’t specify anything, so the index was not partitioned. That’s the default behavior for an index of an unpartitioned table where the index didn’t contain the column on which the table was partitioned. In this case, the partition column was STATE_CODE, which was not part of the IN_LAST_TRANS_DT index, so it was not partitioned. When an index contains a partition key column, that index is converted to a local partitioned index. Bitmap indexes are converted to local partitioned indexes regardless of whether they have the partition key column in their definition. Don’t worry if you got dizzy while reading all that. I will summarize it neatly at the end of this section.

Next, for those indexes that became partitioned in this conversion of the table to a list-partitioned table, let’s examine what kind of partitioning scheme the indexes assumed, using the following SQL statement:

select index_name, partitioning_type, locality
from user_part_indexes
where table_name = 'ACCOUNTS'
/
 
INDEX_NAME      PARTITIONING_TYPE  LOCALITY
——————————————  —————————————————  ————————
IN_ACC_BALANCE  RANGE              GLOBAL
IN_ACC_TYPE     LIST               LOCAL

In Listing 7, because you specified LOCAL for the IN_ACC_TYPE index in the conversion operation, it was converted to LOCAL—no surprise there. Similarly, because you specified GLOBAL for the IN_ACC_BALANCE index, that’s what the index was converted to. However, recall that only a local partitioned index is aligned with the partitioning scheme of a table. Global partitioned indexes need to have their own partitioning information, including partition type—range, hash, list, and so on—and the column on which they are partitioned. Because IN_ACC_BALANCE is a global partitioned index, the partitions of the index will not be aligned with the partitions of the table, and you had to explicitly state in Listing 7 how the index should be partitioned during the conversion operation, which was range partitioning on the ACC_BALANCE column. But was it partitioned accordingly? You can check the partitions of this index and the corresponding high values using this SQL statement:

select partition_name , high_value
from user_ind_partitions
where index_name = 'IN_ACC_BALANCE'

PARTITION_NAME  HIGH_VALUE
——————————————  ——————————
P5000           5000
PMAX            MAXVALUE

As you can see in the output, the partitions of the index were created as specified.

In summary, Table 1 shows how the indexes are affected by the UPDATE INDEXES clause when you convert an unpartitioned table to a partitioned one:

If the index is explicitly mentioned in the UPDATE INDEXES clause this way: The resulting index on the newly partitioned table turns into this:
Local Local, which means the index is partitioned just like the new table
Global Global and not partitioned
Global and partitioned Global and partitioned
Not mentioned at all  

Table 1

Table 2 shows how an index gets converted if you do not include a partitioning clause for the index:

If the index: After the conversion operation on the table, the index becomes:
Does not contain the column on which the table is being partitioned Unpartitioned
Contains the column on which the table is being partitioned Locally partitioned
Is a bitmap index, regardless of the column it is on Locally partitioned

Table 2

It’s very important to understand how the indexes on the table will change as a result of the conversion from an unpartitioned table to a partitioned one and write the UPDATE INDEXES clause appropriately. Note that while you can influence the physical attributes of an index to some extent, you cannot change its design. For example, you can’t convert a nonunique index to a unique one or vice versa with the UPDATE INDEXES operation.

Online Splitting

Conversion to a partitioned table is a onetime activity for a table, and you probably will do a lot more with a partitioned table, such as splitting partitions. Even partition splitting operations are now available online with Oracle Database 12c Release 2.

To see splitting in action, let’s first create a partitioned table called ACCOUNTS and insert some rows, as shown in Listing 8.

Code Listing 8: Range-partitioned table for splitting

create table accounts
(
   acc_no  number,
   acc_type        varchar2(1),
   state_code      varchar2(2),
   acc_balance     number,
   last_trans_dt   date
)
partition by range (last_trans_dt)
(
   partition p2015 
   values less than (to_date('01-jan-2016')),
   partition p2016 
   values less than (to_date('01-jan-2017')),
   partition p2017 
   values less than (to_date('01-jan-2018'))
)
/
insert into accounts 
values (1,'S','CT',2000,'1-jul-2014')
/
insert into accounts 
values (2,'C','CT',2000,'1-jul-2015')
/
insert into accounts 
values (3,'S','CT',2000,'1-jul-2016')
/
insert into accounts 
values (4,'C','NY',2000,'1-jul-2014')
/
insert into accounts 
values (5,'S','NY',2000,'1-jul-2015')
/
insert into accounts 
values (6,'C','NY',2000,'1-jul-2016')
/
Commit
/

Now create two indexes to show the effect of the split on the indexes:

create index in_acc_balance 
on accounts (acc_balance)  local
/
create index in_acc_type 
on accounts (acc_type)  local
/

Note that the ACCOUNTS table has one partition for each year, with the p2015 partition holding values of 2015 and earlier. Now, suppose you want to split the p2015 partition into two partitions: the original p2015 partition containing only 2015 values and a new one called p2014 containing all 2014 and earlier values. This SQL statement accomplishes that objective:

alter table accounts
split partition p2015
into
(
  partition p2014 values less than 
    (to_date('01-jan-2015','dd-mon-yyyy')),
  partition p2015
);
However, this SQL statement also creates two problems. First, this operation needs an exclusive lock on the table, so DML statements can’t run against the table at the same time. This means you would have to stop your applications for the duration of the operation.

Second, let’s look at the status of the index subpartitions after this partition split operation, as shown in Listing 9.

Code Listing 9: Status of index subpartitions

select index_name, subpartition_name, status
from user_ind_subpartitions
order by index_name, subpartition_name
/
INDEX_NAME            SUBPARTITION_NAME       STATUS
————————————————————  ——————————————————————  ————————
IN_ACC_BALANCE        P2014_CT                UNUSABLE
IN_ACC_BALANCE        P2014_DEF               UNUSABLE
IN_ACC_BALANCE        P2014_NY                UNUSABLE
IN_ACC_BALANCE        P2015_CT                UNUSABLE
IN_ACC_BALANCE        P2015_DEF               UNUSABLE
IN_ACC_BALANCE        P2015_NY                UNUSABLE
IN_ACC_BALANCE        P2016_CT                USABLE
IN_ACC_BALANCE        P2016_DEF               USABLE
IN_ACC_BALANCE        P2016_NY                USABLE
IN_ACC_BALANCE        P2017_CT                USABLE
IN_ACC_BALANCE        P2017_DEF               USABLE
IN_ACC_BALANCE        P2017_NY                USABLE
IN_ACC_TYPE           P2014_CT                UNUSABLE
IN_ACC_TYPE           P2014_DEF               UNUSABLE
IN_ACC_TYPE           P2014_NY                UNUSABLE
IN_ACC_TYPE           P2015_CT                UNUSABLE
IN_ACC_TYPE           P2015_DEF               UNUSABLE
IN_ACC_TYPE           P2015_NY                UNUSABLE
IN_ACC_TYPE           P2016_CT                USABLE
IN_ACC_TYPE           P2016_DEF               USABLE
IN_ACC_TYPE           P2016_NY                USABLE
IN_ACC_TYPE           P2017_CT                USABLE
IN_ACC_TYPE           P2017_DEF               USABLE
IN_ACC_TYPE           P2017_NY                USABLE

Note that the status of the split subpartitions is UNUSABLE. This is expected, and you should rebuild the index subpartitions. Alternatively, you could have used the UPDATE INDEXES clause in the ALTER TABLE…SPLIT PARTITION statement to have Oracle Database take care of this at the time of the split:

alter table accounts
split partition p2015
into
(
  partition p2014 values less than 
    (to_date('01-jan-2015','dd-mon-yyyy')),
  partition p2015
)
update indexes
/

However, the SQL statement that splits the partitions would still have prevented DML operations on the table, making applications unavailable, which is not a desirable option considering today’s nonstop applications.

Stopping DML operations for a partition split is no longer an issue. In Oracle Database 12c Release 2, there is an option to perform the SPLIT PARTITION process completely online—while DML statements are still running against the rows in that partition. Here is the online version of the earlier SPLIT PARTITION operation:

alter table accounts
split partition p2015
into
(
  partition p2014 values less than 
    (to_date('01-jan-2015','dd-mon-yyyy')),
  partition p2015
)
online

/

Note that the ONLINE clause in the ALTER TABLE…SPLIT PARTITION statement does the trick. The operation also updates the indexes, keeping them usable. Also, separate index maintenance is not required, because the online operation updates the indexes as well, even without the UPDATE INDEXES clause.

Exchange Tables

The fastest way to load data into a partition is to exchange a table with it. It’s an operation in the data dictionary with no actual data movement, so not only is it fast, but it also doesn’t cause a data outage. Here is an example SQL statement that exchanges the temp_accounts_2014 table with the 2014 partition of the ACCOUNTS table:

alter table accounts
  exchange partition p2014
  with table temp_accounts_2014;

But you need to create the temp_accounts_2014 table with the utmost care. It has to be a faithful representation of the ACCOUNTS table, but not partitioned as such. For example, if ACCOUNTS is just a partitioned table (with no subpartitions), you have to create the temp_accounts_2014 table as unpartitioned. Similarly, if the ACCOUNTS table is composite partitioned, the temp_accounts_2014 table must be partitioned exactly as in the subpartition specification.

In Oracle Database 12c Release 2, you don’t need to write complex data definition language (DDL) actions to account for the state of partitioning in your exchanges. You can now use a special FOR EXCHANGE clause that creates the table for that very purpose, eliminating all guesswork:

create table temp_accounts_2014
tablespace ts_2014
for exchange with table accounts
/

Created this way, the temp_accounts_2014 table will have the same columns with the same properties in the same order as the ACCOUNTS table.

Summary

While it has always been a very potent tool for managing large databases, partitioning has improved significantly with Oracle Database 12c Release 2. In this article, you learned about a few of the new and enhanced partitioning features, including automatically created partitions, multicolumn partitions, read-only partitions, online partitioning operations, and more.

Next Steps

LEARN more about partitioning.

DOWNLOAD Oracle Database 12c Release 2.

TRY Oracle Database Cloud Service.

Photograph by Danny Choo/flickr