共計 4384 個字符，預計需要花費 11 分鐘才能閱讀完成。
本篇內容主要講解“oracle 12c 分區表不完全索引分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“oracle 12c 分區表不完全索引分析”吧!
實驗一
實驗準備
create table part1
(id int, code int,name varchar2(100))
indexing off
partition by range (id)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000) indexing on
);
MING@ming(MING) col partition_name for a30
MING@ming(MING) select PARTITION_NAME,indexing from dba_tab_partitions where table_owner= MING AND TABLE_NAME= PART1
PARTITION_NAME INDE
—————————— —-
P1 OFF
P2 OFF
P3 ON
創建索引
MING@ming(MING) create index code_part1_global on part1(code) global indexing partial;
Index created.
MING@ming(MING) create index id_part1_partial on part1(id) local indexing partial;
Index created.
索引狀態
MING@ming(MING) COL INDEX_NAME FOR A30
MING@ming(MING) select index_name,staTUS from user_indexes where table_name= PART1
INDEX_NAME STATUS
—————————— ——–
CODE_PART1_GLOBAL VALID
ID_PART1_PARTIAL N/A
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_PART1_PARTIAL
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL UNUSABLE
P3 ID_PART1_PARTIAL USABLE
P2 分區 ID_PART1_PARTIAL 索引是 unusable 的，重建這個索引
MING@ming(MING)
alter index ID_PART1_PARTIAL rebuild partition p2 parallel 2 online;
Index altered.
MING@ming(MING) col partition_name for a30
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_PART1_PARTIAL
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL USABLE
P3 ID_PART1_PARTIAL USABLE
MING@ming(MING) select PARTITION_NAME,indexing from dba_tab_partitions where table_owner= MING AND TABLE_NAME= PART1
PARTITION_NAME INDE
—————————— —-
P1 OFF
P2 OFF
P3 ON
重建某個分區的索引要用 rebuild partition 的方法。
前面的實驗已經得到，修改 indexing 屬性會相應的更改索引的狀態；通過上述實驗，我們可以只針對某個分區重建索引，而且修改索引的狀態不會改變 indexing 屬性。
當然也可以在 indexing 為 on 的時候，修改索引為 unusable
MING@ming(MING) alter index ID_PART1_PARTIAL modify partition p3 unusable;
Index altered.
實驗二
修改 indexing 屬性的時候，索引的狀態修改行為探究
把 ID_PART1_PARTIAL 索引刪掉后重建，那么 P2 分區是 UNUSABLE。
P2 分區數據開啟事務
MING@ming(MING) update part1 set name= yy where id=1500;
2 rows updated.
新開會話修改 indexing 屬性
MING@ming(MING) alter table part1 modify partition p2 indexing on;
alter table part1 modify partition p2 indexing on
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
這說明修改分區 indexing，其上的索引不是以 online 的方式重建的，生產環境如果有頻繁的 DML 事務，那么將會失敗。這時候可以采上面實驗中的方法，只針對索引，狀態修改為 usable，然后找合適的時機修改 indexing 屬性。
MING@ming(MING) alter index ID_PART1_PARTIAL rebuild partition p2 online;
Index altered.
針對 alter table part1 modify partition p2 indexing on 的 10046 事件，部分遞歸 sql 如下：
LOCK TABLE PART1 PARTITION (P2) IN EXCLUSIVE MODE NOWAIT
alter index MING . CODE_PART1_GLOBAL coalesce cleanup
insert into index_orphaned_entry$ (indexobj#, tabpartdobj#, hidden) values (:1, :2, :3)
insert /*+ RELATIONAL(PART1) NO_PARALLEL APPEND NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into MING . PART1 pa
rtition (P2) select /*+ RELATIONAL(PART1) NO_PARALLEL */ * from MING . PART1 partition (P2) insert not u
nique partial global indexes
delete from index_orphaned_entry$ where indexobj#=:1
可以看到修改 indexing 屬性的時候，會獲得一個獨占鎖，這樣就是當有活動事務的時候修改 indexing 報錯的原因了。
實驗三
間隔分區是否也能使用不完全索引呢？
創建間隔分區表
MING@ming(MING) create table day_part (id number,eitime date)
2
indexing off
3 partition by range(eitime)
4 interval (numtodsinterval(3, day))
5 (
6 partition p1 values less than (to_date( 2000-01-01 , yyyy-mm-dd))
7 );
Table created.
創建成功！
插入數據并創建索引
MING@ming(MING) insert into day_part values(1,sysdate);
MING@ming(MING) insert into day_part values(2,sysdate);
MING@ming(MING) insert into day_part values(2,sysdate+5);
MING@ming(MING) insert into day_part values(2,sysdate+10);
MING@ming(MING) commit;
MING@ming(MING) create index id_day_part on day_part(id) local indexing partial;
Index created.
查詢
MING@ming(MING) col PARTITION_NAME for a30
MING@ming(MING) col INDEX_NAME for a30
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_DAY_PART
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_DAY_PART USABLE
SYS_P420 ID_DAY_PART USABLE
SYS_P421 ID_DAY_PART USABLE
SYS_P422 ID_DAY_PART USABLE
MING@ming(MING) alter table DAY_PART modify partition SYS_P420 indexing off;
Table altered.
這里就不在展示了，但是對于間隔分區表來說，不完全索引也是可用的。
到此，相信大家對“oracle 12c 分區表不完全索引分析”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
