溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容主要講解“oracle 12c分區(qū)表不完全索引分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“oracle 12c分區(qū)表不完全索引分析”吧!
實(shí)驗(yàn)準(zhǔn)備
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 |
創(chuàng)建索引
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. |
索引狀態(tài)
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分區(qū)ID_PART1_PARTIAL索引是unusable的，重建這個(gè)索引
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 |
重建某個(gè)分區(qū)的索引要用rebuild partition的方法。
前面的實(shí)驗(yàn)已經(jīng)得到，修改indexing屬性會(huì)相應(yīng)的更改索引的狀態(tài)；通過上述實(shí)驗(yàn)，我們可以只針對(duì)某個(gè)分區(qū)重建索引，而且修改索引的狀態(tài)不會(huì)改變indexing屬性。
當(dāng)然也可以在indexing為on的時(shí)候，修改索引為unusable
MING@ming(MING)> alter index ID_PART1_PARTIAL modify partition p3 unusable;
Index altered. |
修改indexing屬性的時(shí)候，索引的狀態(tài)修改行為探究
把ID_PART1_PARTIAL索引刪掉后重建，那么P2分區(qū)是UNUSABLE。
P2分區(qū)數(shù)據(jù)開啟事務(wù)
MING@ming(MING)> update part1 set name='yy' where id=1500;
2 rows updated. |
新開會(huì)話修改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 |
這說明修改分區(qū)indexing，其上的索引不是以online的方式重建的，生產(chǎn)環(huán)境如果有頻繁的DML事務(wù)，那么將會(huì)失敗。這時(shí)候可以采上面實(shí)驗(yàn)中的方法，只針對(duì)索引，狀態(tài)修改為usable，然后找合適的時(shí)機(jī)修改indexing屬性。
MING@ming(MING)> alter index ID_PART1_PARTIAL rebuild partition p2 online;
Index altered. |
針對(duì)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屬性的時(shí)候，會(huì)獲得一個(gè)獨(dú)占鎖，這樣就是當(dāng)有活動(dòng)事務(wù)的時(shí)候修改indexing報(bào)錯(cuò)的原因了。
間隔分區(qū)是否也能使用不完全索引呢？
創(chuàng)建間隔分區(qū)表
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. |
創(chuàng)建成功！
插入數(shù)據(jù)并創(chuàng)建索引
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.
|
這里就不在展示了，但是對(duì)于間隔分區(qū)表來說，不完全索引也是可用的。
到此，相信大家對(duì)“oracle 12c分區(qū)表不完全索引分析”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
