SKIP_UNUSABLE_INDEXES的使用與索引失效解決方法
SKIP_UNUSABLE_INDEXES引數的主要用途是當索引為usable狀態時保證sql的執行，雖然它保證了系統的健壯性，但是有可能系統沒有使用該索引而導致效能的低下。還有該引數對於使用hint的某些sql和唯一索引的插入、刪除語句卻不生效 比如使用了index。
建立測試表和索引
SQL> conn test/test
已連線。
SQL> drop table a;
表已刪除。
SQL> create table a(id number);
表已建立。
SQL> create unique index idx_a_id on a(id);
索引已建立。
SQL> declare
2 begin
3 for a in 1..1000 loop
4 insert into a(id) values(a);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME TYPE VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes boolean TRUE
SQL> select * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
124 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
修改skip_unusable_indexes為false
SQL> alter system set skip_unusable_indexes=false scope=memory;
系統已更改。
將索引修改為不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出現錯誤提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
將skip_unusable_indexes修改為true
SQL> alter system set skip_unusable_indexes=true scope=memory;
系統已更改。
對於查詢操作此時該sql能夠正常執行，但是此時進行的是全表掃描
SQL> select * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1) 統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用hint強制使用索引，此時會提示索引無效
SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態 插入操作會出錯
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
SQL>
解決方法，重建索引
SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
統計資訊
----------------------------------------------------------
15 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index test.idx_a_id;
索引已刪除。
SQL> create index test.idx_a_id on a(id);
索引已建立。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已建立 1 行。
SQL> commit;
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
建立測試表和索引
SQL> conn test/test
已連線。
SQL> drop table a;
表已刪除。
SQL> create table a(id number);
表已建立。
SQL> create unique index idx_a_id on a(id);
索引已建立。
SQL> declare
2 begin
3 for a in 1..1000 loop
4 insert into a(id) values(a);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME TYPE VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes boolean TRUE
SQL> select * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
124 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
修改skip_unusable_indexes為false
SQL> alter system set skip_unusable_indexes=false scope=memory;
系統已更改。
將索引修改為不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出現錯誤提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
將skip_unusable_indexes修改為true
SQL> alter system set skip_unusable_indexes=true scope=memory;
系統已更改。
對於查詢操作此時該sql能夠正常執行，但是此時進行的是全表掃描
SQL> select * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A | 4 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1) 統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用hint強制使用索引，此時會提示索引無效
SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態 插入操作會出錯
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出現錯誤:
ORA-01502: 索引 'TEST.IDX_A_ID' 或這類索引的分割槽處於不可用狀態
SQL>
解決方法，重建索引
SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;
執行計劃
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX_A_ID | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)
統計資訊
----------------------------------------------------------
15 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
402 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index test.idx_a_id;
索引已刪除。
SQL> create index test.idx_a_id on a(id);
索引已建立。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已建立 1 行。
SQL> commit;
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29802484/viewspace-1985725/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 引起索引失效的原因和解決方法索引
- @Transactional詳解（作用、失效場景與解決方法）
- iMac和MBP乙太網介面失效的原因與解決方法Mac
- oracle 索引使用及索引失效總結Oracle索引
- iOS下的點選事件失效解決方法iOS事件
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- 解決IE中img.onload失效的方法
- Win10應用搜尋功能失效無法使用的解決方法Win10
- VNPY中開盤前掛單失效的解決方法
- MacOS Big Sur更新後git失效的解決方法MacGit
- win10系統下使用Win+G截圖失效的解決方法Win10
- 使用SUI與JQuery衝突的解決方法UIjQuery
- 分割槽表 全域性索引與本地索引失效測試索引
- 建立索引意外終止解決方法索引
- Oracle資料庫索引使用及索引失效總結 轉Oracle資料庫索引
- mysql索引失效的情況MySql索引
- 探索MySQL的InnoDB索引失效MySql索引
- 索引失效場景索引
- Oracle索引失效-likeOracle索引
- tarui drop失效,解決配置UI
- ASP程式設計中Session物件失效的客戶端解決方法程式設計Session物件客戶端
- 解決busuanzi_count突然失效的方法（hexo-theme-next）Hexo
- 面試官：聊聊索引失效？失效的原因是什麼？面試索引
- [資料庫]索引失效資料庫索引
- MySql ORDER BY索引是否失效MySql索引
- JQuery中ajax的使用與快取問題的解決方法jQuery快取
- Laravel 5.8+scout7.0 使用 orderBy 排序失效解決方案Laravel排序
- Win10系統下Adobe Flash Player失效的解決方法Win10
- Windows10系統總出現usb裝置失效的解決方法Windows
- Win10截圖動畫失效怎麼辦 Win10截圖動畫失效解決方法Win10動畫
- .gitignore 失效問題解決Git
- MySQL 使用 like "%x"，索引一定會失效嗎？MySql索引
- win8.1update致win8.1啟用失效解決方法
- 索引失效系列——說說is null索引Null
- window10聚焦無法使用怎麼解決 win10聚焦功能失效修復方法Win10
- Wicket中JQuery事件繫結失效的解決jQuery事件
- SpringBoot框架：兩個方法同時呼叫時父方法使內部方法的DataSource註解失效的解決辦法Spring Boot框架
- skip_unusable_indexesIndex