有關partition scan的案例
表結構如下：
SQL> desc msg_request_history
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
MSGTYPE NUMBER(8) 0
MSGKEY VARCHAR2(50)
MSGDATE DATE Y SYSDATE
STATUS NUMBER(2) Y 0
按msgdate按天來分割槽。
SQL> set autotrace traceonly;
SQL>
SQL> select count(*) from msg_request_history
2 where trunc(MSGDATE)= trunc(to_date('20070719','yyyy-mm-dd'))
3 and MSGKEY='11175998';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (FULL) OF 'MSG_REQUEST_HISTORY' (Cost=3 C
ard=1 Bytes=36)
由於where條件中是trunc(MSGDATE)，並沒有用到partition prune，走的是PARTITION RANGE (ALL)。
SQL> select count(*) from msg_request_history
2 where MSGDATE= to_date('20070719000000','yyyy-mm-dd hh24:mi:ss')
3 and MSGKEY='11175998';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE)
3 2 TABLE ACCESS (FULL) OF 'MSG_REQUEST_HISTORY' (Cost=2 C
ard=1 Bytes=36)
由於where條件中是MSGDATE = ，實現了partition prune，走的是PARTITION RANGE (SINGLE)。
SQL> select count(*) from msg_request_history
2 where MSGDATE between to_date('20070719000000','yyyy-mm-dd hh24:mi:ss') and to_date('20070721000000','yyyy-mm-dd hh24:mi:ss')
3 and MSGKEY='11175998';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 PARTITION RANGE (ITERATOR)
4 3 TABLE ACCESS (FULL) OF 'MSG_REQUEST_HISTORY' (Cost=5
6 Card=1 Bytes=36)
由於where條件中是MSGDATE between and ，同樣實現了partition prune，走的是PARTITION RANGE (ITERATOR)。Executes child operations for each partition in the table specified by a range of partition keys。
接下來在msgkey列上建立normal index。
SQL> create index indx_msg_request_history on msg_request_history(msgkey);
Index created
SQL>
SQL> select count(*) from msg_request_history
2 where MSGDATE= to_date('20070719000000','yyyy-mm-dd hh24:mi:ss')
3 and MSGKEY='11175998';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MSG_REQUEST_HIS
TORY' (Cost=2 Card=1 Bytes=36)
3 2 INDEX (RANGE SCAN) OF 'INDX_MSG_REQUEST_HISTORY' (NON-
UNIQUE) (Cost=1 Card=21)
發現走的是index range scan，並沒有PARTITION RANGE (SINGLE)。
SQL> select trunc(msgdate),count(*) from msg_request_history group by trunc(msgdate);
TRUNC(MSGDATE) COUNT(*)
-------------- ----------
2007-7-19 23368
2007-7-20 23368
2007-7-21 23368
2007-7-23 70104
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('indx_msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
4M
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
6.4375M
SQL>
SQL> exec COMMON_PARTITION.PROC_DROP_ALL_PARTITIONS('20070731');
PL/SQL procedure successfully completed
SQL> select trunc(msgdate),count(*) from msg_request_history group by trunc(msgdate);
TRUNC(MSGDATE) COUNT(*)
-------------- ----------
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
.0625M
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('indx_msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
4M
SQL> select trunc(msgdate),count(*) from msg_request_history group by trunc(msgdate);
TRUNC(MSGDATE) COUNT(*)
-------------- ----------
SQL>
SQL> alter index indx_msg_request_history rebuild online;
Index altered
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('indx_msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
.0625M
SQL>
在drop分割槽後發現normal index並沒有釋放空間，也就是說索引得需重建才行。即使在drop partition的時候帶上“update global indexes”也不能回縮索引，看來這個選項只能用在分割槽索引上。
將normal index給刪除掉建立local non-prefixed partition index後執行計劃如下：
SQL> create index indx_msg_request_history on msg_request_history(msgkey) local;
Index created
SQL> select count(*) from msg_request_history
2 where MSGDATE= to_date('20070719000000','yyyy-mm-dd hh24:mi:ss')
3 and MSGKEY='11175998';
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'MSG_REQUEST_HI
STORY' (Cost=2 Card=1 Bytes=36)
4 3 INDEX (RANGE SCAN) OF 'INDX_MSG_REQUEST_HISTORY' (NO
N-UNIQUE) (Cost=1 Card=8)
可看到效能提高很多。同時對於的索引資料也跟著縮小。
SQL> select trunc(msgdate),count(*) from msg_request_history group by trunc(msgdate);
TRUNC(MSGDATE) COUNT(*)
-------------- ----------
2007-7-19 23368
2007-7-20 23368
2007-7-21 23368
2007-7-23 70104
SQL> exec COMMON_PARTITION.PROC_DROP_PARTITION('20070720');
PL/SQL procedure successfully completed
SQL> exec COMMON_PARTITION.PROC_DROP_PARTITION('20070721');
PL/SQL procedure successfully completed
SQL> exec COMMON_PARTITION.PROC_DROP_PARTITION('20070722');
PL/SQL procedure successfully completed
SQL> exec COMMON_PARTITION.PROC_DROP_PARTITION('20070723');
PL/SQL procedure successfully completed
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('indx_msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
1.6875M
SQL> select sum(bytes/1024/1024)||'M' from user_segments where segment_name=upper('msg_request_history');
SUM(BYTES/1024/1024)||'M'
-----------------------------------------
1.875M
SQL>
下面是建立global索引
SQL> create index indx_msg_request_history on msg_request_history(msgkey) global;
Index created
SQL> select count(*) from msg_request_history
2 where MSGDATE= to_date('20070719000000','yyyy-mm-dd hh24:mi:ss')
3 and MSGKEY='11175998';
COUNT(*)
----------
4
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=36)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MSG_REQUEST_HIS
TORY' (Cost=2 Card=1 Bytes=36)
3 2 INDEX (RANGE SCAN) OF 'INDX_MSG_REQUEST_HISTORY' (NON-
UNIQUE) (Cost=1 Card=31)
可看出global index也即normal方式的索引。
發覺有一段話來形容分割槽表和分割槽索引特別好，先引用如下（分割槽表的通俗解釋）：
普通表呢像一個小學生用的新華字典，分割槽表呢像一套博士們辭海，在同一個漂亮的盒子裡面(表名）有若干本辭海分冊（每一冊就是一個分割槽了）。
如果說檢索一張普通表就像查新化字典，檢索分割槽表就像查辭海了。具體而言呢，又這麼幾種方式：
1). 因為你知道你查的內容只會出現在某些分冊裡面，於是你很快的從辭海盒子裡面取出你要的那個冊子，不加思索的翻到索引頁，根據索引頁的指示，你飛快的翻到你的目標頁面。取一本本冊子呢就叫partition range [inlist] iterator，找索引頁當然就是index range scan。如果你不找索引頁，準備翻完整本書的找，那就是full table scan了。如果你只找一本冊子的，那partition range iterator也就不必了。
2). 哦，你不知道你要查的內容在那本冊子裡？ 那你只好辛苦一點，翻閱所有冊子了。這時，你做的動作就叫partition range all. 而對於每本冊子言，也許你會找索引頁（index scan），也許你想翻遍全冊（full table scan）。
3). 也許你發現一冊冊的開啟索引頁找內容太繁重了，你突然想起來對你的辭海做個整改。於是你把每冊的索引頁全都拆了下來，專門裝訂成一冊。每次你想利用索引頁找東西時，你就開啟這個索引冊。從索引冊，你就可以找到你要內容在哪一冊哪個地方。這就是global index scan. 相對於1,2，就叫local index scan.
4). 你有兒子嗎？有一天，你想培訓兒子的能力，於是你就找來你兒子給你翻冊子，找資料。可是你兒子非得和老子一起找才肯幫你。於是你們父子倆就開始一起檢索起辭海來，你查某些冊子，他查另一些冊子。這就叫partition scan.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/38542/viewspace-926728/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210910]table scan相關統計.txt
- Partition Pruning和Partition-Wise Joins
- SCAN
- Clique Partition
- redis的scan用法解析Redis
- 關於“INS-40922 Invalid Scan Name – Unresolvable to IP address”
- oracle partition by group by,詳解partition by和group by對比Oracle
- 分割槽Partition
- 新增SCAN IP
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- 7.74 DATAOBJ_TO_PARTITIONOBJ
- 86. Partition List
- oracle partition by 語法Oracle
- B. Range and Partition
- 深入理解Redis的scan命令Redis
- scan ip的手動切換
- Redis 中 Keys 與 Scan 的使用Redis
- skim、scan和browse的區別
- Laravel Query Builder 複雜查詢案例：子查詢實現分割槽查詢 partition byLaravelUI
- 分割槽函式Partition By的基本用法函式
- RAC 增加SCAN IP
- Peace or partition? Cyprus - Espresso EconomistEspresso
- 7.73 DATAOBJ_TO_MAT_PARTITIONOBJ
- 3-Partition 問題
- Redis中的Scan命令踩坑記Redis
- Spring裡component-scan的工作原理Spring
- 阿里雲內容安全介面 敏感詞 green scan、圖片 image scan阿里
- 深度學習有哪些好玩的案例？深度學習
- 關於創業教育的案例分析創業
- 申請入駐 Steam遊戲營銷：有關“今日特惠”、DLC的案例分析遊戲
- redis scan迭代模糊匹配Redis
- Codeforces 1948E Clique Partition
- 分割槽partition知識點
- Partition|Disk Utility 如何分割磁碟
- ROWNUMBER() OVER( PARTITION BY COL1
- Oracle RAC修改Scan IP，Public IP的方法Oracle
- ABAP下載的病毒掃描Virus Scan
- 簡單談談MySQL的loose index scanMySqlIndex
- 分割槽函式partition by的基本用法【轉載】函式