A very common historical technique for loading data into a table efficiently was to
Way back in version 8.0, Oracle gave us the facility to set an index to UNUSABLE so that we would not have to remember the DDL that we used to the create the indexes in the first place. So the process then became
However, you're probably wondering why that load is taking so long. Here's why...
SQL> drop table t; Table dropped. SQL> create table t ( x number ); Table created. SQL> create index tx on t ( x ); Index created. SQL> alter index tx unusable; Index altered. SQL> select status from user_indexes 2 where table_name = 'T'; STATUS -------- UNUSABLE SQL> truncate table t; Table truncated. SQL> select status from user_indexes 2 where table_name = 'T'; STATUS -------- VALID
Ouch! This is actually documented behaviour (its in the v9 and v10 docs). So remember to set your indexes to unusable AFTER you have performed your truncate.