CODEWITHSUNDEEP
oracle-databaseindexing
GTeckHan Goh
GTeckHan Goh

Reputation: 97

Drop Index and Create Index VS Set Index to unusable and Rebuild Index

how to know which one is a better way to select insert with huge record table. I select a table with 5 million records and insert into another table.

I worry about the index cause it slow. what can I do on the index?

  1. Drop index -> Select Insert -> Create index back.
  2. Set index to unusable -> Select Insert -> Rebuild index back.

Is this 2 ways is the same performance? or set unusable is better because does not need to drop and recreate,worry about drop and recreate also will cause it slow.

Upvotes: 1

Views: 2030

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Definitely go with (2), ie, unusable then rebuild. The performance cost is the same as drop and recreate.

Otherwise you always run the risk that the DDL you use to recreate the index is not the same as what is currently in place for the existing index. For example, it could be globally partitioned, it could have basic or advanced compression, it could have a custom pctfree setting, it could be stored in a particular tablespace.

I should note though that 5million rows is not a huge amount, so you should benchmark the cost of doing the insert with the index left in place versus the cost of insert followed by rebuild.

For example, an insert on a table with 2 indexes didn't take too long on my laptop

SQL> create table t as
  2  select d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 100 )
  4  where rownum <= 5000000;

Table created.

SQL>
SQL> create table t1 as select * from t;

Table created.

SQL>
SQL> create index ix1 on t ( owner );

Index created.

SQL> create index ix2 on t ( object_name );

Index created.

SQL>
SQL> set timing on
SQL> insert /*+ APPEND */ into t
  2  select * from t1;

5000000 rows created.

Elapsed: 00:01:19.91

Upvotes: 2

Related Questions