引數SKIP_UNUSABLE_INDEXES的一點測試！

warehouse發表於2007-10-13
Index

之前並不知道SKIP_UNUSABLE_INDEXES是一個引數，在imp/impdp中經常看到！透過以下試驗發現即使SKIP_UNUSABLE_INDEXES=y，status＝UNUSABLE的index還是伴隨著tt表的匯出匯入一起匯出匯入，只是SKIP_UNUSABLE_INDEXES=y時，當imp匯入表時insert的資料不會同時再去update index key了，但是unique index例外 ，因為它扮演著constraint的作用！


SQL> DESC TT
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER(38)

SQL> SELECT * FROM TT;

ID
----------
1
2

SQL> CREATE INDEX IDX_TT ON TT(ID);

索引已建立。

SQL> alter table tt move tablespace dmt;

表已更改。

SQL> select index_name ,table_name , status from user_INDEXES where index_name='
IDX_TT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
IDX_TT TT UNUSABLE

SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> alter session set skip_unusable_indexes=false;

會話已更改。

SQL> select * from tt;

ID
----------
1
2

SQL> delete from tt;
delete from tt
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> update tt set id=100;
update tt set id=100
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> select * from tt;

ID
----------
1
2

SQL> drop index idx_tt;

索引已刪除。

SQL> create unique index idx_tt on tt(id);

索引已建立。

SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean FALSE
SQL> alter table tt move tablespace dmt;

表已更改。

SQL> select index_name ,table_name , status from user_INDEXES where index_name='
IDX_TT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
IDX_TT TT UNUSABLE

SQL> select * from tt;

ID
----------
1
2

SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> update tt set id=100;
update tt set id=100
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> delete from tt;
delete from tt
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_TT' 或這類索引的分割槽處於不可用狀態


SQL> drop index idx_tt;

索引已刪除。

SQL> alter table tt add constraint pk_tt primary key (id);

表已更改。

SQL> select index_name ,table_name , status from user_INDEXES where table_name='
TT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_TT TT VALID

SQL> alter table tt move tablespace dmt;

表已更改。

SQL> select index_name ,table_name , status from user_INDEXES where table_name='
TT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_TT TT UNUSABLE
--建立primary key時建立的index如果status＝UNUSABLE此時把select都報錯！
SQL> select * from tt;
select * from tt
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> update tt set id=100;
update tt set id=100
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> delete from tt;
delete from tt
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE UNUSABLE


SQL> SHOW PARAMETER SKIP_UNUSABLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean FALSE
我們清晰的看到下面skip_unusable_indexes＝true時對unique index不起作用，
因為unqiue index此時扮演著constraint的角色
SQL> alter session set SKIP_UNUSABLE_INDEXES=true;

會話已更改。

SQL> delete from tt;
delete from tt
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> update tt set id=100;
update tt set id=100
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> insert into tt values(3);
insert into tt values(3)
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.PK_TT' 或這類索引的分割槽處於不可用狀態


SQL> select * from tt;

ID
----------
1
2

SQL> alter table tt drop constraint pk_tt;

表已更改。

SQL> create index idx_tt on tt(id);

索引已建立。

SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT TT NONUNIQUE VALID

SQL> alter table tt move tablespace dmt;

表已更改。

SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT TT NONUNIQUE UNUSABLE


SQL> show parameter SKIP_UNUSABLE_INDEX

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
SQL> select *from tt;

ID
----------
1
2

SQL> insert into tt values(3);

已建立 1 行。

SQL> commit;

提交完成。

SQL> update tt set id=100;

已更新3行。

SQL> rollback;

回退已完成。

SQL> delete from tt;

已刪除3行。

SQL> rollback;

回退已完成。

SQL>
--=====================================================================================
SQL> host exp file=e:expa.dmp tables=tt

Export: Release 10.2.0.1.0 - Production on 星期六 10月 13 21:43:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將匯出指定的表透過常規路徑...
. . 正在匯出表 TT匯出了 3 行
成功終止匯出, 沒有出現警告。

SQL> host imp file=e:expa.dmp SKIP_UNUSABLE_INDEXES=y full=y

Import: Release 10.2.0.1.0 - Production on 星期六 10月 13 21:45:19 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 XYS 的物件匯入到 XYS
. 正在將 XYS 的物件匯入到 XYS
. . 正在匯入表 "TT"匯入了 3 行
成功終止匯入, 沒有出現警告。

SQL> connect
已連線。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT TT NONUNIQUE UNUSABLE


--我們看到即使SKIP_UNUSABLE_INDEXES=y，status＝UNUSABLE的index還是伴隨著tt表的匯出匯入
一起匯出匯入，只是
SKIP_UNUSABLE_INDEXES=y時，當imp匯入表時insert的資料不會同時再去update index key了！
SQL> connect
已連線。
SQL> drop index idx_tt;

索引已刪除。

SQL> alter table tt add constraint pk_tt primary key (id);

表已更改。

SQL> alter table tt move tablespace dmt;

表已更改。

SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE UNUSABLE


SQL> host exp file=e:expa.dmp tables=tt

Export: Release 10.2.0.1.0 - Production on 星期六 10月 13 22:27:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將匯出指定的表透過常規路徑...
. . 正在匯出表 TT匯出了 3 行
成功終止匯出, 沒有出現警告。

SQL> host imp file=e:expa.dmp SKIP_UNUSABLE_INDEXES=y full=y

Import: Release 10.2.0.1.0 - Production on 星期六 10月 13 22:27:37 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案
已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入
. 正在將 XYS 的物件匯入到 XYS
. 正在將 XYS 的物件匯入到 XYS
. . 正在匯入表 "TT"匯入了 3 行
IMP-00017: 由於 ORACLE 錯誤 14063, 以下語句失敗:
"ALTER TABLE "TT" ADD CONSTRAINT "PK_TT" PRIMARY KEY ("ID") USING INDEX PCT"
"FREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS"
" 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) TABLESPACE "DMT" LOGGING"
IMP-00003: 遇到 ORACLE 錯誤 14063
ORA-14063: 唯一/主約束條件關鍵字中存在無用索引
IMP-00017: 由於 ORACLE 錯誤 14048, 以下語句失敗:
"ALTER INDEX "PK_TT" UNUSABLE ENABLE "
IMP-00003: 遇到 ORACLE 錯誤 14048
ORA-14048: 分割槽維護操作不能與其它操作組合
成功終止匯入, 但出現警告。

SQL> connect
已連線。
SQL> select index_name ,table_name , uniqueness,status from user_INDEXES where t
able_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE UNUSABLE


SQL>
透過以上操作我們看到SKIP_UNUSABLE_INDEXES=y對unique index不起作用，因為此時的unique index扮演者
constraint的作用所以在insert資料時index必須被更新

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-61743/，如需轉載，請註明出處，否則將追究法律責任。

相關文章