Partial indexing – get the dictionary definitions right

Just a quick post to clear up some confusion that can be seen on the partial indexing capabilities in Oracle Database 12c and above. I was at an event in the Oracle Perth office last week, and an attendee mentioned that they thought the feature was not working or was not enabled on their database, and presented the following demonstration to prove their case:



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

Table created.

SQL> insert into t
  2  select rownum, rownum from dual connect by level < 2000;

1999 rows created.

SQL> create index ix on t ( x ) local;

Index created.

SQL> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'IX';

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             P2

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

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
P2                             USABLE

At first glance, this looks counter-intuitive. I have explicitly specified that I do not want indexing on partition P2, yet after creating a local index, I still have 2 segments, one for each partition, and double-checking USER_IND_PARTITIONS tells me that they are both “fully-fledged” usable index partitions.

As per the documentation linked above, nominating the INDEXING ON / OFF at the partition level on the table definition is not the whole story. When you create the index, you need to inform the database that you wish a particular index to respect that intent. This is because you might want some indexes to be partial and others not to be.

So by slightly changing my CREATE INDEX statement, I can get the desired outcome.


SQL> drop index ix;

Index dropped.

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

Index created.

SQL> select segment_name, partition_name
  2  from user_segments
  3  where segment_name = 'IX';

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
IX                             P1

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

PARTITION_NAME                 STATUS
------------------------------ --------
P1                             USABLE
P2                             UNUSABLE

5 Comments on “Partial indexing – get the dictionary definitions right

  1. wanted to know if, I need to use this feature in an existing table, where indexing on/off is not defined at the partitioned level, is that possible to alter?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: