ITLife365 生活 互动学习空间 - 互动学习空间

你是否很是困扰，为什么禁用了索引了，但是表插入数据还是很慢，很耗费时间呢？
A very common historical technique for loading data into a table efficiently was to
一个很常见的历史技术的数据加载到表有效地的步骤如下：

1、drop the indexes
2、truncate the table
3、load the data
4、recreate the indexes

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

set the indexes to unusable
truncate the table
load the data
rebuild the indexes

However, you're probably wondering why that load is taking so long. Here's why...

以下是一个完整的验证例子：
C:\Documents and Settings\Administrator>sqlplus sys/oracle11g as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 12月 3 14:40:18 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

已连接到空闲例程。

SQL> spool e:\me.txt
SQL> startup
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  535662592 bytes                                      
Fixed Size                  1334380 bytes                                      
Variable Size             318768020 bytes                                      
Database Buffers          209715200 bytes                                      
Redo Buffers                5844992 bytes                                      
数据库装载完毕。
数据库已经打开。
SQL>
SQL> drop table itlife365;

表已删除。

SQL> create table itlife365(x number );

表已创建。

SQL> create index itlife365_idx on itlife365(x);

索引已创建。

SQL> select status from user_indexes where table_name='ITLIFE365';

STATUS                                                                         
----------------                                                               
VALID                                                                          

SQL> alter index itlife365_idx unusable;

索引已更改。

SQL> select status from user_indexes where table_name='ITLIFE365';

STATUS                                                                         
----------------                                                               
UNUSABLE                                                                       

SQL> truncate table itlife365;

表被截断。

SQL> select status from user_indexes where table_name='ITLIFE365';

STATUS                                                                         
----------------                                                               
VALID                                                                          

SQL> spool off;
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.

看了上面的例子，明白了吧，truncate table * 后 ,alter index itlife365_idx unusable 的作用失效了，需要重新执行alter index itlife365_idx unusable。
或者在truncate table  后在禁用索引。