Home > Oracle, Performance > Local Indexes – one at a time

Local Indexes – one at a time


One of my friend put a question to me – how can I create a local index partition by partition?  When he try to create a single index on a single partiton, is failing with

SQL> create index ptab1 on ptab (object_id) local (partition p1);SQL>
create index ptab1 on ptab (object_id) local (partition p1)
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

But, there is a way.  When you create the index you need to specify UNUSABLE in the CREATE INDEX sql, so that Oracle will not re-build the SQL rather just create the index object – without populating the data. And definitely the index will be unusable.

SQL> create index ptab1 on ptab (object_id) local unusable;

Index created.

SQL> select INDEX_NAME,PARTITIONED,STATUS from user_indexes where TABLE_NAME=’PTAB’;

INDEX_NAME                           PAR                      STATUS
——————————        —                           ——–
PTAB1                                         YES                         N/A
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where INDEX_NAME=’PTAB1′;

INDEX_NAME                       PARTITION_NAME                   STATUS
——————————   ——————————         ——–
PTAB1                                       P1                                                       UNUSABLE
PTAB1                                       P2                                                       UNUSABLE
PTAB1                                       P3                                                       UNUSABLE
PTAB1                                       P4                                                       UNUSABLE
PTAB1                                       P5                                                       UNUSABLE

Elapsed: 00:00:00.24

Now the indexes are in place – rebuild each partition one by one.

QL> alter index PTAB1 rebuild partition p1;

Index altered.

Elapsed: 00:00:00.59
SQL> alter index PTAB1 rebuild partition p2;

Index altered.

Elapsed: 00:00:00.64
SQL> alter index PTAB1 rebuild partition p3;

Index altered.

Elapsed: 00:00:01.35
SQL> alter index PTAB1 rebuild partition p4;

Index altered.

Elapsed: 00:00:01.02

Elapsed: 00:00:00.16
SQL> alter index PTAB1 rebuild partition p5;

Index altered.

Elapsed: 00:00:05.74
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where INDEX_NAME=’PTAB1′;

INDEX_NAME                     PARTITION_NAME               STATUS
—————————— ——————————      ——–
PTAB1                                     P1                                                   USABLE
PTAB1                                     P2                                                  USABLE
PTAB1                                     P3                                                  USABLE
PTAB1                                     P4                                                   USABLE
PTAB1                                     P5                                                   USABLE

Elapsed: 00:00:00.01

Well, now your LOCAL indexes are in place.

Advertisements
  1. Jorge
    October 16, 2016 at 4:26 pm

    And what about If I need to specify diffente tablespaces for each partition?

    • October 16, 2016 at 4:49 pm

      When you rebuild, you can specify the tablespace for each partition.

  2. February 16, 2017 at 2:06 pm

    Good Post

  1. No trackbacks yet.

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: