Search

Top 60 Oracle Blogs

Recent comments

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