Data loads on INTERVAL partitioned tables

Why interval partitioning is cool

Before 11g, partitioning a table immediately stepped up the responsibilities of the database administrator, because if you forgot to add sufficient partitions to accommodate your data, you could easily break your application. Many a DBA can recall a fateful New Year’s Eve phone call because everyone is suddenly seeing the following error on their screen

SQL> insert into SALES
  2  values ( trunc(sysdate), .... );

insert into SALES
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Interval partitioning solved this by having the database automatically create partitions as required and since the inception of interval partitioning, most of us have converted our existing range partitioned tables into interval ones. But does that fully abdicate the responsibilities of the DBA? Perhaps not. We partition tables typically when the sizes of those tables grows to such an extent as they become unwieldy to manage as a single segment.  (There are other reasons for partitioning tables and indexes, but typically its a size issue that drives the decision). Large tables are often loaded in bulk fashion, and to load large volumes of rows efficiently, we need to tackle the issue of index maintenance

Unusable indexes

Bulk data loads can be dramatically slowed by indexes on the target table, and unusable indexes are a useful technique to mitigate against this slow down. I generally prefer (where possible) to set an index to unusable over dropping and recreating it because recreating an index might lose some of the nuanced characteristics the index might have previously had (compression, pctfree, etc etc).

Let’s look at unusable indexes before interval partitioning came along. Here’s a table with two partitions, which I’m going to bulk load, so I’m creating the index as unusable. Note this is no different to creating the index and then setting the partitions to unusable explicitly.

SQL> create table t ( x int, y int )
  2      partition by range ( x )
  3      (
  4        partition p1 values less than ( 100000 ),
  5        partition p2 values less than ( 200000 )
  6      );

Table created.

SQL> create index t_idx on t ( x ) local unusable;

Index created.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
P2         UNUSABLE

Now I’ll load my data and its nice and efficient because the index partitions are unusable. Then I’ll rebuild the partitions at the end of the load and the job is done.

SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum
  3  from dual
  4  connect by level < 200000;

199999 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
P2         UNUSABLE

SQL> alter index t_idx rebuild partition p1;

Index altered.

SQL> alter index t_idx rebuild partition p2;

Index altered.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         USABLE
P2         USABLE

INTERVAL changes the process

Here is where many a DBA has been caught out when they convert their tables to be interval partitioned. In fact, we’ve had the occasional AskTom question asking “are intervals slower?” (they’re not), but here the is cause of that misinterpretation.

I’ll recreate my table, this time as an interval partitioned table, and create the index in the same way as before

SQL> create table t ( x int, y int )
  2      partition by range ( x )
  3      interval ( 100000 )
  4      (
  5        partition p1 values less than ( 100000 )
  6      );

Table created.

SQL>
SQL> create index t_idx on t ( x ) local unusable;

Index created.

SQL>
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE

Now I’ll run my bulk load again.

SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum
  3  from dual
  4  connect by level < 200000;

199999 rows created.

SQL> commit;

Commit complete.

Lets now look at the index partitions.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
SYS_P18505 USABLE

SQL>

The dynamically created interval partitions are USABLE on creation. There is no mechanism to “intercept” the creation of an interval partition (It’s a common enhancement request we see), but its important to know that when you create a partitioned index as UNUSABLE, then it applies to the existing partitions, not future ones. Even tinkering with the segment creation semantics wont give you any joy here.

SQL> create table t ( x int, y int ) segment creation immediate
  2      partition by range ( x )
  3      interval ( 100000 )
  4      (
  5        partition p1 values less than ( 100000 )
  6      )  ;

Table created.

SQL> create index t_idx on t ( x ) local unusable;

Index created.

SQL>
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum
  3  from dual
  4  connect by level < 200000;

199999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
SYS_P18506 USABLE

Solutions

I could workaround this by pre-creating the interval partitions before I run the data load. There are easy tricks to do this, eg, create a single row in each partition range and roll it back, or attempt to lock a non-existent partition (which will result in a partition being created). But each of these are the opposite of what interval partitioning is meant to address, namely, managing the partition creation yourself!

However, partial indexes are a mechanism you can exploit to solve this issue. Although partial indexes are a property of the table, you can nominate that an index should respect this setting when you create the index. I’ll recreate the table, this time with partial indexing specified

SQL> create table t ( x int, y int )
  2      partition by range ( x )
  3      interval ( 100000 )
  4      (
  5        partition p1 values less than ( 100000 )
  6      ) indexing off ;

Table created.

Now I’ll create my index adding the “partial” clause. The “indexing off” for a partial index is implemented by making the index partitions unusable.

SQL> create index t_idx on t ( x ) local unusable indexing partial;

Index created.

SQL>
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE

Now I can load my table, and each new index partition takes on the default partial index property and thus is set to unusable.

SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum
  3  from dual
  4  connect by level < 200000;

199999 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
SYS_P18507 UNUSABLE

Finally I can rebuild the index partitions as per normal and the problem is solved.

SQL> alter index t_idx rebuild partition p1;

Index altered.

SQL> alter index t_idx rebuild partition SYS_P18507;

Index altered.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         USABLE
SYS_P18507 USABLE

Care with unusable indexes

Because partial indexes are implemented using the UNUSABLE attribute, the same gotchas that apply to unusable indexes apply here as well. For example, with any standard index (or index partition) if you truncate the associated table segment then the index/index partition will silently switch from UNUSABLE to USABLE.

SQL> alter index t_idx modify partition p1 unusable;

Index altered.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         UNUSABLE
SYS_P18507 USABLE

SQL>
SQL> alter table t truncate partition p1;

Table truncated.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         USABLE
SYS_P18507 USABLE

Even if that index is defined as a partial index, the same applies

SQL> alter table t modify partition SYS_P18507 indexing off;

Table altered.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         USABLE
SYS_P18507 UNUSABLE

SQL> alter table t truncate partition SYS_P18507;

Table truncated.

SQL> select partition_name,status
  2      from user_ind_partitions
  3      where index_name ='T_IDX';

PARTITION_ STATUS
---------- --------
P1         USABLE
SYS_P18507 USABLE

Final thoughts

When dealing with index partitions, my recommendation is always: Be a hard cynic.

The only way to be sure that they are UNUSABLE when you want them to be, and USABLE when you want them to be, is to query the data dictionary. If I am building data maintenance or data load jobs, then part of those processes should be regular queries to the dictionary to make sure the index status is what you are expecting (and to take appropriate action if not).

That protects you not just from boundary cases like truncates etc, but also protects your code from outside influences – who knows what other code in your application is doing to those partitions that is unknown to you.

HT to Mike Hallas for his suggestion to look at partial indexes as a workaround