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?
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.
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
