溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容主要講解“怎么理解oracle 12c分區(qū)表不完全索引”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“怎么理解oracle 12c分區(qū)表不完全索引”吧!
有的分區(qū)表根據(jù)業(yè)務(wù)特點，熱點數(shù)據(jù)只是最近幾個月，甚至是當月數(shù)據(jù)，這時候在其他不活躍分區(qū)上建索引就顯得浪費存儲空間，對有的全局索引來說還會影響性能。oracle 12c中提供了只在部分分區(qū)上建索引的功能，不完全索引的出現(xiàn)，完美的解決了這個問題。
建一個分區(qū)表
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 ); |
oracle通過關(guān)鍵字indexing作為不完全索引的開關(guān)。在這個建表語句中，表級別的indexing默認是on，分區(qū)的indexing屬性如果沒有指定，那么會繼承表級別參數(shù)。這里的這個建表語句，把表級別indexing關(guān)掉，顯示指示分區(qū)P3 indexing 為on，那么其他三個分區(qū)p1,p2的indexing就是off了。這樣就可以實現(xiàn)P3分區(qū)上建不完全索引了。
該關(guān)鍵字屬性可以通過查看dba_tab_partitions的indexing得到
SYS@cdbtest1(MING)> COL PARTITION_NAME FOR A30 SYS@cdbtest1(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 |
可以看到只有P3是顯示指定的ON，其他分區(qū)集成了表級別的indexing屬性。
插入數(shù)據(jù)
begin for i in 1 .. 2999 loop insert into part1 values(i,i,'mingshuo'); end loop ; commit; end; /
|
創(chuàng)建全局索引
create index id_part1_global on part1(id) global indexing full; create index code_part1_global on part1(code) global indexing partial; SYS@cdbtest1(MING)> col INDEX_NAME for a30 SYS@cdbtest1(MING)> select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_GLOBAL','CODE_PART1_GLOBAL');
INDEX_NAME PAR INDEXIN ------------------------------ --- ------- CODE_PART1_GLOBAL NO PARTIAL ID_PART1_GLOBAL NO FULL |
indexing full是默認的，雖然P1，P2分區(qū)indexing是OFF，但是如果在創(chuàng)建索引時指定了indexing full，那么還是會基于所有分區(qū)創(chuàng)建索引；
如果指定了indexing partial，那么就會按照分區(qū)的indexing屬性，選擇性的基于indexing為ON的分區(qū)上創(chuàng)建索引。
也就是說創(chuàng)建索引時指定的indexing參數(shù)優(yōu)先級更高，會覆蓋表定義中的indexing屬性。
如果創(chuàng)建索引時不指定那么就按照默認的indexing full來生效，創(chuàng)建索引時指定indexing partial會按照表定義時的indexing屬性。
這里，id_part1_global是完全索引，code_part1_global是不完全索引。
對此，利用執(zhí)行計劃印證一下。
MING@ming(MING)> explain plan for select count(id) from part1;
Explained.
MING@ming(MING)> set line 200 MING@ming(MING)> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2604063405
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FAST FULL SCAN| ID_PART1_GLOBAL | 1 | 13 | 5 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
9 rows selected.
|
id列上是正常的全局索引，所以走了索引快速掃描。
code列上是不完全索引，count(code)看一下執(zhí)行計劃：
先刪除之前id列上的索引避免干擾
drop index ID_PART1_GLOBAL;
MING@ming(MING)> explain plan for select count(code) from part1;
Explained.
MING@ming(MING)> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1564279961
----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 549 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | VIEW | VW_TE_2 | 2 | 26 | 549 (1)| 00:00:01 | | | | 3 | UNION-ALL | | | | | | | | |* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PART1 | 1 | 26 | 1 (0)| 00:00:01 |3 | 3 | |* 5 | INDEX RANGE SCAN | CODE_PART1_GLOBAL | 1 | | 1 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ITERATOR | | 1 | 26 | 548 (1)| 00:00:01 |1 | 2 | |* 7 | TABLE ACCESS FULL | PART1 | 1 | 26 | 548 (1)| 00:00:01 |1 | 2 | -----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter("PART1"."ID">=2000 AND "PART1"."ID"<3000) 5 - access("CODE">2600) 7 - filter("CODE">2600)
10 rows selected.
|
看執(zhí)行計劃就發(fā)現(xiàn)，P3分區(qū)確實采用了CODE_PART1_GLOBAL索引，其他分區(qū)采用全表掃描，兩個結(jié)果集union all。
再來看分區(qū)索引
drop index CODE_PART1_GLOBAL; create index id_part1_partial on part1(id) local indexing partial; create index code_part1_partial on part1(code) local indexing full;
SYS@cdbtest1(MING)> select index_name,partitioned,indexing from dba_indexes where index_name in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');
INDEX_NAME PAR INDEXIN ------------------------------ --- ------- ID_PART1_PARTIAL YES PARTIAL CODE_PART1_PARTIAL YES FULL
SYS@cdbtest1(MING)> col HIGH_VALUE for a15 SYS@cdbtest1(MING)> col PARTITION_NAME for a20 SYS@cdbtest1(MING)> col index_name for a25 SYS@cdbtest1(MING)> set line 150 SYS@cdbtest1(MING)> select INDEX_NAME,HIGH_VALUE,PARTITION_NAME,STATUS,LEAF_BLOCKS,NUM_ROWS from dba_ind_partitions where INDEX_NAME in ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');
INDEX_NAME HIGH_VALUE PARTITION_NAME STATUS LEAF_BLOCKS NUM_ROWS ------------------------- --------------- -------------------- -------- ----------- ---------- CODE_PART1_PARTIAL 1000 P1 USABLE 3 999 CODE_PART1_PARTIAL 2000 P2 USABLE 3 1000 CODE_PART1_PARTIAL 3000 P3 USABLE 3 1000 ID_PART1_PARTIAL 1000 P1 UNUSABLE 0 0 ID_PART1_PARTIAL 2000 P2 UNUSABLE 0 0 ID_PART1_PARTIAL 3000 P3 USABLE 3 1000
6 rows selected.
|
通過status和num_rows兩列可以看到，indexing full確實優(yōu)先級更高，沒有指定indexing的，按照表定義，id列上索引只在P3分區(qū)存在，code列索引每個分區(qū)都有。
下面也看一下不完全索引在執(zhí)行計劃中的表現(xiàn)：
MING@ming(MING)> select count(code) from part1 where code<200;
Execution Plan ---------------------------------------------------------- Plan hash value: 186457639
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE ALL| | 1 | 13 | 3 (0)| 00:00:01 | 1 | 3 | |* 3 | INDEX RANGE SCAN | CODE_PART1_PARTIAL | 1 | 13 | 3 (0)| 00:00:01 | 1 | 3 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("CODE"<200)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
MING@ming(MING)> select count(id) from part1 where id<200;
Execution Plan ---------------------------------------------------------- Plan hash value: 1660407118
------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 275 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE SINGLE| | 1 | 13 | 275 (1)| 00:00:01 | 1 | 1 | |* 3 | TABLE ACCESS FULL | PART1 | 1 | 13 | 275 (1)| 00:00:01 | 1 | 1 | -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - filter("ID"<200)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 6 recursive calls 4 db block gets 92 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
MING@ming(MING)> select count(id) from part1 where id>2900;
Execution Plan ---------------------------------------------------------- Plan hash value: 3675562320
------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE SINGLE| | 1 | 13 | 1 (0)| 00:00:01 | 3 | 3 | |* 3 | INDEX RANGE SCAN | ID_PART1_PARTIAL | 1 | 13 | 1 (0)| 00:00:01 | 3 | 3 | ------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("ID">2900)
Note ----- - dynamic statistics used: dynamic sampling (level=2)
Statistics ---------------------------------------------------------- 11 recursive calls 4 db block gets 44 consistent gets 4 physical reads 0 redo size 543 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
MING@ming(MING)> select count(id) from part1 where id>1900;
Execution Plan ---------------------------------------------------------- Plan hash value: 712638347
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 277 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | VIEW | VW_TE_2 | 1422 | 18486 | 277 (1)| 00:00:01 | | | | 3 | UNION-ALL | | | | | | | | | 4 | PARTITION RANGE SINGLE| | 1 | 25 | 1 (0)| 00:00:01 | 3 | 3 | |* 5 | INDEX RANGE SCAN | ID_PART1_PARTIAL | 1 | 25 | 1 (0)| 00:00:01 | 3 | 3 | | 6 | PARTITION RANGE SINGLE| | 1421 | 35525 | 276 (1)| 00:00:01 | 2 | 2 | |* 7 | TABLE ACCESS FULL | PART1 | 1421 | 35525 | 276 (1)| 00:00:01 | 2 | 2 | --------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
5 - access("PART1"."ID">=2000 AND "PART1"."ID"<3000) 7 - filter("PART1"."ID">1900)
|
id>1900的時候，會跨越兩個分區(qū)，P2走分區(qū)掃描，P3走不完全索引，兩個結(jié)果集union all。
改變表的indexing屬性
比如這里我需要P2，P3的indexing屬性都是ON，P2定義的時候是OFF，可以通過下面的語句修改：
alter table part1 modify partition P2 indexing on;
實際生產(chǎn)環(huán)境下，如果因為之前不了解這種不完全索引，那么建表的時候不會按照我上面實驗的建表語句中，表級別indexing設(shè)置為OFF，后續(xù)如果要用這個新特性的話，就需要去修改分區(qū)的indexing，這時候就需要上面的語句，把分區(qū)級別修改為OFF。
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 ON P3 ON
|
這里要注意一下索引的有效性
如果之前將分區(qū)indexing從ON修改為OFF，那么之前存在的分區(qū)上的不完全索引會失效，換句話說，已經(jīng)存在的不完全索引的usable或者unusable狀態(tài)是跟indexing的ON或者OFF對應(yīng)的。indexing由OFF變?yōu)镺N，索引從unusable變?yōu)閡sable，indexing由ON變?yōu)镺FF，索引從usable變?yōu)閡nusable。
到此，相信大家對“怎么理解oracle 12c分區(qū)表不完全索引”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
