ORA-39817: index XXXXXX was ignored due to: 会有提示告警,数据会load
SKIP_UNUSABLE_INDEXES requested and index segment was initially unusable
.. . imported 1.544 MB 85612 rows
skip_unusable_indexes
关于参数 skip_unusable_indexes :
在 9i中 这个参数是 session级默认是 false ,10g中 该参数被调整为系统级,同时默认为 true ,顾名思义就是会跳过不可用的索引,避免ORA-01502错误,提高系统的可用性,但是会牺牲系统的性能。
注意:即使该参数值为 true 时 ,仍然不能避免 查询中 使用 hints 去走索引而报索引不可用的错,详细请看以下测试:
查看 9i 和 10g中此参数的设置:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as justone5
SQL> show parameter skip
NAME TYPE VALUE
------------------------------------ -----------
skip_unusable_indexes boolean TRUE
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as justone4
SQL> show parameter skip
10g中该参数的影响
-------------i am split----------------------------------------------
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as justone5
SQL> show parameter skip
NAME TYPE VALUE
------------------------------------ -----------
skip_unusable_indexes boolean TRUE
SQL> create table test_a(a number);
Table created
SQL> create index idx_test_a on test_a(a);
Index created
SQL> insert into test_a select rownum from dba_objects where rownum <1000;
999 rows inserted
SQL> commit;
Commit complete
SQL> alter index idx_test_a unusable;
Index altered
SQL> select * from test_a where a=1;
A
----------
1
SQL> alter system set skip_unusable_indexes=false;
System altered
SQL> select * from test_a where a=1;
select * from test_a where a=1
ORA-01502: 索引 'JUSTONE5.IDX_TEST_A' 或这类索引的分区处于不可用状态
SQL> alter system set skip_unusable_indexes=true;
System altered
SQL> select /*+ index(test_a)*/* from test_a where a=1;
select /*+ index(test_a)*/* from test_a where a=1
ORA-01502: 索引 'JUSTONE5.IDX_TEST_A' 或这类索引的分区处于不可用状态
SKIP_UNUSABLE_INDEXES的存在很好的保证了系统的健壮性,但是对于使用hint强制使用索引的语句和唯一索引的插入、删除语句却不能保证。
==============================================================================
在以前的实验中,曾经提到,为了加快装载大数据量的表,我们可以首先置表上的索引为unusable,不论使用IMP,SQL LOADER还是INSERT语句,我们通过设置参数skip_unusable_indexes为True,来首先装载数据,然后再重建索引。 例如:
SQL> create table t(i int );
Table created.
SQL> create index ind_t on t (i);
Index created.
SQL> alter index ind_t unusable;
Index altered.
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> insert into t select mod(rownum,2) from all_objects where rownum <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL> alter index ind_t rebuild ;
Index altered.
但是,这有一个前提,即置为unusable的索引不是UNIQUE,或者PRIMATY KEY使用的索引,例如:
SQL> create table t(i int constraint t_pk primary key deferrable,j int);
Table created.
SQL> alter index t_pk unusable;
Index altered.
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> insert into t select mod(rownum,2), 1 from all_objects where rownum <= 10;
insert into t select mod(rownum,2), 1 from all_objects where rownum <= 10
*
ERROR at line 1:
ORA-01502: index 'T_PK' or partition of such index is in unusable state
这是因为ORACLE必须保证数据的一致性,不能跳过使用唯一性限制的索引。 如果我能假设加载的数据是干净的,在ORACLE9i 及以后,我们可以这样做:
SQL> create table t(i int constraint t_pk primary key deferrable,j int);
Table created.
SQL> alter table t disable constraint t_pk KEEP INDEX;
Table altered.
SQL> alter index t_pk unusable;
Index altered.
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> insert into t select mod(rownum,2), 1 from all_objects where rownum <= 10;
10 rows created.
SQL> alter index t_pk rebuild;
Index altered.
SQL> alter table t enable constraint t_pk;
alter table t enable constraint t_pk
*
ERROR at line 1:
ORA-02437: cannot validate (T_PK) - primary key violated