【原創】ORACLE 分割槽與索引

《oracle分割槽與索引》

引言：oracle的分割槽和索引可以說是它自己的亮點，可能你會說在其他資料庫上也有，嗯是的，但oracle的種類效能便利性可以說是比較人性化的，下面我們通過實驗來闡述它們的特性和功能。

1.分別給出一個B-tree索引針對全表掃描效能高和低的例子。

索引定義：oracle資料庫中索引就是為了加快資料訪問速度的一種目錄結構

B-tree索引特點：

（1）二叉樹結構

（2）用比較大小方式查詢索引塊

（3）適合建立在鍵值重複率低的欄位

例如主鍵欄位：強調錶的參照關係，即可以被外來鍵引用

唯一性約束欄位：強調欄位鍵值的唯一性

（4）第一次掃描時，從root根節點進入，後面就不在返回進入了

（5）葉子與葉子之間有指標鏈，不用返回上一層，可以直接定位到下一個葉子節點

（6）主鍵欄位做搜尋時效率與資料量無關，例如 1萬條記錄 1億條記錄檢索效率差不多

（7）索引塊是按順序存放的，資料塊是打散存放的

（8）結果集越小效能越好，結果集越大效能越不好

（9）相比點陣圖索引，佔用空間較多

實驗

LEO1@LEO1> drop table leo1;

Table dropped.

LEO1@LEO1> drop table leo2;

Table dropped.

先清理環境，我們重新建立表和索引，看看在不同執行計劃下的效能如何。

LEO1@LEO1> create table leo1 as select * from dba_objects;

Table created.

我們建立leo1表用於全表掃描

LEO1@LEO1> create table leo2 as select * from dba_objects;

Table created.

我們建立leo2表用於走B-tree索引

LEO1@LEO1> create index idx_leo2 on leo2(object_id);

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true);

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true);

PL/SQL procedure successfully completed.

對leo1和leo2表及表上的索引進行統計分析，以便讓oracle瞭解它們的資料分佈情況

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where wner = 'LEO1';

TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE

------------------------------ ---------- --------- -------------------------------- ---------- --------- -------

LEO1 71961 09-JAN-13 TABLE

LEO2 71962 09-JAN-13 TABLE

好已經顯示出對2張表進行統計分析了，並且還知道了表上的資料有71961行和71962行

LEO1@LEO1> set autotrace traceonly 啟動執行計劃

LEO1@LEO1> select * from leo1 where object_id=10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 97 | 287 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL | LEO1 | 1 | 97 | 287 (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表掃描，代價Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------------------------------

1 - filter("OBJECT_ID"=10000) 謂詞條件

Statistics

---------------------------------------------------------------------------

1 recursive calls

0 db block gets

1031 consistent gets 1031個一致性讀

1026 physical reads

0 redo size

1626 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1@LEO1> select * from leo2 where object_id=10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 2495991774

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO2 | 1 | 97 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_LEO2 | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走B-tree索引，代價Cost=2

Predicate Information (identified by operation id):

----------------------------------------------------------------------------------------

2 - access("OBJECT_ID"=10000) 謂詞條件

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets 4個一致性讀=先訪問root->在找branch->在找leaf+遞迴IO

0 physical reads

0 redo size

1629 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

我們從上面的執行計劃可以看出，走B-tree索引效率要比全表掃描高出很多很多，尤其在鍵值重複率低的欄位非常適合使用B-tree索引（流程：先訪問root->在找branch->在找leaf->在找到鍵值key->訪問對應ROWID資料塊->提取資料），我們還要知道當結果集越小使用索引訪問速度越快，如果結果集較大那麼，我們看看效能如何呢？

LEO1@LEO1> select * from leo1 where object_id>=10000;

62253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 62216 | 5893K| 287 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL | LEO1 | 62216 | 5893K| 287 (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表掃描，代價Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">=10000)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

5118 consistent gets 5118個一致性讀

0 physical reads

0 redo size

3245084 bytes sent via SQL*Net to client

46174 bytes received via SQL*Net from client

4152 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

62253 rows processed

LEO1@LEO1> select /*+ index(leo2 idx_leo2) */ * from leo2 where object_id>=10000;

62254 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2495991774

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 62217 | 5893K| 1073 (1)| 00:00:13 |

| 1 | TABLE ACCESS BY INDEX ROWID| LEO2 | 62217 | 5893K| 1073 (1)| 00:00:13 |

|* 2 | INDEX RANGE SCAN | IDX_LEO2 | 62217 | | 139 (0)| 00:00:02 |

----------------------------------------------------------------------------------------

走B-tree索引，代價Cost=1073

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">=10000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

9312 consistent gets 哇塞居然9312個一致性讀

49 physical reads

0 redo size

7232860 bytes sent via SQL*Net to client

46174 bytes received via SQL*Net from client

4152 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

62254 rows processed

我用hint方式強制走索引，很明顯索引的效率大大低於全表掃描，看來CBO的判斷還是正確的，因為會出現這種情況呢，走索引的原理是先訪問一次索引塊，在訪問一次資料塊，這樣便至少是2次IO，當你查詢結果集越大時，消耗IO資源就越多（一致性讀次數就越多），所以呢還不如直接訪問表效率高，你這時很聰明，可能會問結果集多大時，索引效率就不好了呢，一般超過總量1/5時效率就會變差，這只是一個經驗值，大家要多多測試出真知。

2.分別給出一個Bitmap索引針對b-tree索引效能高和低的例子。

Bitmap索引特點：

（1）鍵值行結構

（2）使用點陣圖標識鍵值

（3）適合建立在鍵值重複率高的欄位

（4）鍵值重複率越高，佔用的空間越少，每個獨立鍵值佔用一行

（5）適合OLAP系統

（6）DML操作會鎖定整個點陣圖索引段，導致阻塞和無法大併發

（7）位運算效率非常高，例如 and or not 運算

實驗

注：我的實驗要結合上下文一起看哦，有的表或者物件會在上面的實驗中建立

LEO1@LEO1> create table leo3 as select * from dba_objects; 建立leo3與leo2表結構一樣

Table created.

LEO1@LEO1> create bitmap index idx_leo3 on leo3(object_id); 建立bitmap索引

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO3',cascade=>true); 統計分析

PL/SQL procedure successfully completed.

LEO1@LEO1> set autotrace off;

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where wner = 'LEO1';

TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE

------------------------------ ---------- --------- ------------

LEO1 71961 09-JAN-13 TABLE

LEO2 71962 09-JAN-13 TABLE

LEO3 71964 09-JAN-13 TABLE

LEO1@LEO1> select count(*) from leo2 where object_id>10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 788375040

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 45 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX FAST FULL SCAN | IDX_LEO2 | 62216 | 303K| 45 (0)| 00:00:01 |

----------------------------------------------------------------------------------

快速索引全掃描，把索引鏈分割成若干區域，多索引塊並行掃描，所以很快，Cost=45

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID">10000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

167 consistent gets 167個一致性讀，一次IO讀取多個塊

1 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1@LEO1> select count(*) from leo3 where object_id>10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 1835111598

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 218 (0)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | BITMAP CONVERSION COUNT | | 62218 | 303K| 218 (0)| 00:00:03 |

--------------------------------------------------------------------------------------

點陣圖索引範圍掃描->點陣圖值轉換成統計值->集合排序，Cost=218

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_ID">10000)

filter("OBJECT_ID">10000)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

219 consistent gets 219個一致性讀

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');

SEGMENT_NAME EXTENTS BLOCKS

IDX_LEO2 B-tree索引 17 256

IDX_LEO3 Bitmap索引 18 384

因為B-tree索引和Bitmap索引組成結構不同，在Bitmap索引中每個獨立鍵值佔用一行，我們知道object_id是沒有重複值的，所以組成Bitmap索引時每個object_id鍵值都佔用一行，因此就比B-tree索引佔用的索引塊多，佔用的索引塊多掃描的就多一致性IO就多效率就低，總而言之，Bitmap索引適合重複率高的欄位

LEO1@LEO1> create table leo4 as select * from dba_objects; 建立leo4

Table created.

LEO1@LEO1> create table leo5 as select * from dba_objects; 建立leo5

Table created.

LEO1@LEO1> create index idx_leo4 on leo4(object_type); 建立B-tree

Index created.

LEO1@LEO1> create bitmap index idx_leo5 on leo5(object_type); 建立Bitmap

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO4',cascade=>true); 統計分析

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO5',cascade=>true); 統計分析

PL/SQL procedure successfully completed.

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where wner = 'LEO1';

TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE

------------------------------ ---------- --------- ------------

LEO1 71961 09-JAN-13 TABLE

LEO2 71962 09-JAN-13 TABLE

LEO3 71964 09-JAN-13 TABLE

LEO4 71966 09-JAN-13 TABLE

LEO5 71967 09-JAN-13 TABLE

LEO1@LEO1> select * from leo4 where object_type='TABLE';

2807 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 412720909

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1674 | 158K| 75 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO4 | 1674 | 158K| 75 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_LEO4 | 1674 | | 5 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走B-tree索引範圍掃描，Cost=75

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

99 recursive calls

0 db block gets

568 consistent gets 568個一致性讀

0 physical reads

0 redo size

312088 bytes sent via SQL*Net to client

2581 bytes received via SQL*Net from client

189 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

2807 rows processed

LEO1@LEO1> select * from leo5 where object_type='TABLE';

2808 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 174753293

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1674 | 158K| 203 (0)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO5 | 1674 | 158K| 203 (0)| 00:00:03 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

-----------------------------------------------------------------------------------------

走Bitmap索引

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

355 consistent gets 355個一致性讀

0 physical reads

0 redo size

312171 bytes sent via SQL*Net to client

2581 bytes received via SQL*Net from client

189 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2808 rows processed

LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');

SEGMENT_NAME EXTENTS BLOCKS

IDX_LEO4 17 256

IDX_LEO5 2 16

如上所示兩個索引各自佔用空間情況，下面我們來分析一下，在鍵值重複率高情況下為什麼點陣圖索引效率好？

不出我們所料Bitmap索引比B-tree索引效率要高哦，正好中了鍵值重複率越高，佔用的空間越少這句話，因為object_type欄位有很多重複鍵值，我們在查詢過程中只對object_type='TABLE'的“鍵值行”掃描一遍即可知道哪些記錄符合條件，從佔用索引塊數量上也能看出掃描16個塊要比掃描256個塊快大發啦！哈哈

3.演示DML操作導致點陣圖索引鎖定點陣圖段示例

Bitmap鎖定特點：

（1）當我們操作同一個“鍵值行”時，會產生鎖定整個鍵值行

（2）所以不建議頻繁進行DML操作

（3）適合OLAP系統例如報表生成位運算統計分析

實驗

LEO1@LEO1> select distinct sid from v$mystat; 顯示當前會話id，用於區別其他會話操作

SID

----------------------

133

會話133

LEO1@LEO1> create table leo6 (id int,name varchar2(20)); 建立leo6表，2個欄位

Table created.

LEO1@LEO1> create bitmap index idx_leo6 on leo6(id); 在id欄位上建立點陣圖索引

Index created.

LEO1@LEO1> insert into leo6 values(1,'leo'); 插入記錄

1 row created.

LEO1@LEO1> insert into leo6 values(1,'sun'); 插入記錄

1 row created.

LEO1@LEO1> commit; 提交

Commit complete.

LEO1@LEO1> select * from leo6; 顯示資料

ID NAME

----------------------------

1 leo

1 sun

Id列的值相同，從Bitmap索引結構上看，這兩條記錄都是在同一個鍵值行上，我們如果操作其中的一條記錄那麼就是對這個鍵值行操作

LEO1@LEO1> update leo6 set id=2 where name='leo'; 更新一條記錄

1 row updated.

會話157，重新開啟一個會話

LEO1@LEO1> update leo6 set id=2 where name='sun';

這時你會驚訝的發現游標不動了，命令提示符也不能顯示出來了，怎麼回事，想一想你是不是在哪裡碰到過這種情況，對這就是傳說中的“阻塞”現象，是被第133會話給阻塞了，而我們原來碰到的是當2個會話同時修改同一條記錄時會發生阻塞，而現在我們更新的是2條不同記錄為什麼也會發生阻塞呢，一位偉人說過“存在即合理”，那麼既然發生了，就必然會有關係，只是這種關係與記錄無關，而是發生在索引鍵值行上。

這就是Bitmap索引的一個特性：DML操作會鎖定整個點陣圖段（鍵值行）導致阻塞現象，這是因為oracle為了保證資料一致性和完整性，必須將索引鍵值行鎖定，防止其他會話對其修改，歸根結底這都是由於點陣圖索引的構造原理造成的，一個鍵值行對應多條記錄，當其中任意記錄值被修改時，oracle會鎖定整個鍵值行資訊，此時另一個會話如果要修改這個鍵值行中的記錄時，這個操作就會被之前的操作所阻塞。

解決方案：要麼commit/rollback 133會話，要麼終止157會話

我們從v$lock檢視中看一下鎖定情況

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ------------ -- ---------- ---------- ---------- ----------

133 AE 100 0 4 0 0

157 AE 100 0 4 0 0

133 TO 65927 1 3 0 0

133 TO 5003 1 3 0 0

157 TM 73837 0 3 0 0

133 TM 73837 0 3 0 0

157 TX 589827 1307 6 0 0

133 TX 131088 1284 6 0 1

157 TX 131088 1284 0 4 0

SID：會話id

ID1+ID2：修改資料塊上記錄地址

LMODE：持有的鎖型別

REQUEST：正在請求的鎖

BLOCK：阻塞會話個數

說明：133會話正在持有一個6級TX事務鎖（排他鎖）並且正好阻塞另一個會話，從ID1+ID2地址上看133會話恰恰正在阻塞157會話，而157會話目前沒有鎖正在請求一個4級TX事務鎖，只有133會話提交後才會釋放6級TX鎖

133會話

LEO1@LEO1> commit;

Commit complete.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);

SID TY ID1 ID2 LMODE REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

133 AE 100 0 4 0 0

157 AE 100 0 4 0 0

133 TO 65927 1 3 0 0

133 TO 5003 1 3 0 0

157 TM 73837 0 3 0 0

157 TX 589827 1307 6 0 0

此時133會話的6級TX鎖已經被釋放了，157會話阻塞解除可以繼續操作並獲得一個6級鎖

4.建立一個全文索引（Text index），比較它和傳統的模糊查詢的效能。

全文索引特點：

（1）使用字串拆字方法檢索“字詞短語”，適合文字搜尋

（2）場景在用文字模糊查詢時，使用全文索引比較高效例 where name like ‘%leo%’

（3）我們目的找到所有包含“leo”字串的記錄，由於leo字串在欄位中的位置是不固定的，使用B-tree索引就沒有辦法進行檢索，而使用全文索引就可以很好按照拆分字的原理進行檢索

（4）全文索引是一個邏輯名，內部包含許多基表和基索引，它們實際佔用空間，而全文索引名不佔用空間不是段物件，這些內部物件組成了全文索引

（5）全文索引佔用空間大，一般全文索引是基表大小1.5倍

（6）管理維護成本高，bug多

實驗

LEO1@LEO1> create table leo7 (id number,name varchar2(20)); 建立leo7表

Table created.

LEO1@LEO1> create index idx_leo7 on leo7(name); 在name欄位建立B-tree索引

Index created.

LEO1@LEO1> insert into leo7 values (1,'leo');

1 row created.

LEO1@LEO1> insert into leo7 values (2,'leo leo');

1 row created.

LEO1@LEO1> insert into leo7 values (3,'leo leo leo'); 插入3條記錄

1 row created.

LEO1@LEO1> commit; 提交

Commit complete.

LEO1@LEO1> select * from leo7; 顯示有3條

ID NAME

---------- --------------------

1 leo

2 leo leo

3 leo leo leo

LEO1@LEO1> create table leo8 as select * from leo7; 我們通過leo7建立leo8表

Table created.

LEO1@LEO1> create index idx_text_leo8 on leo8(name) indextype is ctxsys.context;

Index created.

注：我們在建立oracle 11g全文索引時等待時間較長，大約8秒鐘，為什麼會這麼長時間呢，它又做了哪些內容呢？

（1）先檢查oracle是否已安裝“全文檢索工具”，oracle11g 預設安裝的，oracle10g預設沒有安裝

（2）再進行“語法分析”就是我們上面提到的“拆字”過程，例如建立詞法分析器及相關表等操作

如果你的是oracle10g資料庫，請參考《oracle10g 不能成功建立全文索引的解決方法》http://f.dataguru.cn/thread-49019-1-1.html

LEO1@LEO1> set autotrace on 顯示執行計劃

LEO1@LEO1> select * from leo7 where name like '%leo%';

ID NAME

---------- --------------------

1 leo

2 leo leo

3 leo leo leo

Execution Plan

----------------------------------------------------------

Plan hash value: 598568836

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| LEO7 | 3 | 75 | 0 (0)| 00:00:01 |

|* 2 | INDEX FULL SCAN | IDX_LEO7 | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走的是索引全掃描，當資料量小的時候還可以，大了就差些了

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%leo%') 執行計劃重寫了謂詞條件，因為它發現這樣更高效

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

12 consistent gets 12個一致性讀

0 physical reads

0 redo size

680 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

LEO1@LEO1> select * from leo8 where contains(name,'leo')>0;

ID NAME

---------- --------------------

1 leo

2 leo leo

3 leo leo leo

Execution Plan

----------------------------------------------------------

Plan hash value: 287112382

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 37 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO8 | 1 | 37 | 4 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX | IDX_TEXT_LEO8 | | | 4 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

域索引就是全文索引，可能會有當第一次執行的時候效率較低，多執行幾次後效率就提高了

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("CTXSYS"."CONTAINS"("NAME",'leo')>0)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

11 recursive calls

0 db block gets

11 consistent gets 11個一致性讀

0 physical reads

0 redo size

680 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

小結：使用全文索引的時候，當檢索資料量大時短語組成較複雜時效率較好。

5.分別演示分割槽索引的效能優化全域性索引和差於全域性索引的示例，並分析原因。

本地分割槽索引特點：

（1）本地分割槽索引擅長某一個分割槽內檢索資料，縮小掃描範圍速度就上去了

（2）當跨分割槽檢索時，由於每個分割槽上面都有一個獨立本地索引，oracle搜尋引擎就會遍歷每個分割槽的獨立索引段和資料段（在多個索引段間跳躍），這樣系統IO就會增大多倍，效率降低

（3）本地分割槽索引在每個分割槽要麼都有，要麼都沒有

（4）DDL操作無需rebuild索引，不會導致索引無效

全域性分割槽索引特點：

（1）跨分割槽檢索資料效率較高，因為是一個整體無需跳躍，節約了IO和索引塊管理開銷

（2）分割槽內掃描效率沒有本地索引高，因為掃描範圍較大

（3）DDL操作會導致索引無效例如增分割槽刪分割槽 truncate分割槽都需要rebuild索引

（4）一個分割槽表上經常有DDL操作，將會導致全域性索引無效（必須rebuild），在建全域性索引時請注意這點

分割槽優勢：體現管理收益而不是效能收益，它在管理和規劃上方便了我們運維資料，而效能上不一定能提高多少

實驗跨分割槽掃描

LEO1@LEO1> create table partition_leo9 建立分割槽表，分割槽越多實驗效果越明顯

partition by range (object_id)

(

partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(40000),

partition p5 values less than(50000),

partition p6 values less than(60000),

partition pm values less than(maxvalue)

) as select * from dba_objects; 2 3 4 5 6 7 8 9 10 11

Table created.

LEO1@LEO1> create table partition_leo10 建立partition_leo10與partition_leo9表結構一樣

partition by range (object_id)

(

partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(40000),

partition p5 values less than(50000),

partition p6 values less than(60000),

partition pm values less than(maxvalue)

) as select * from dba_objects; 2 3 4 5 6 7 8 9 10 11

Table created.

LEO1@LEO1> select count(*) from partition_leo9 partition (p1);

COUNT(*)

------------------

9708

LEO1@LEO1> select count(*) from partition_leo10 partition (p1);

COUNT(*)

------------------

9708

LEO1@LEO1> select count(*) from partition_leo9 partition (p2);

COUNT(*)

------------------

9806

LEO1@LEO1> select count(*) from partition_leo9 partition (p3);

COUNT(*)

------------------

10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p4);

COUNT(*)

------------------

10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p5);

COUNT(*)

------------------

10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p6);

COUNT(*)

------------------

9606

LEO1@LEO1> select count(*) from partition_leo9 partition (pm);

COUNT(*)

------------------

12872

我們基於每個分割槽檢視一下記錄數

LEO1@LEO1> create index idx_partition_leo9 on partition_leo9(object_id); 建立一個全域性分割槽索引

Index created.

LEO1@LEO1> create index idx_partition_leo10 on partition_leo10(object_id) local;建立一個本地分割槽索引

Index created.

LEO1@LEO1> select table_name,index_name,partitioned,status from dba_indexes where table_name in ('PARTITION_LEO9','PARTITION_LEO10');

TABLE_NAME INDEX_NAME PAR STATUS

------------------------------ ------------------------------ --------------------------- --- -------------------- --- -------------------

PARTITION_LEO10 IDX_PARTITION_LEO10 YES N/A 本地分割槽索引，分成7個索引段

PARTITION_LEO9 IDX_PARTITION_LEO9 NO VALID 全域性分割槽索引，就1個索引段

LEO1@LEO1> select count(*) from partition_leo9 where object_id>1000;

COUNT(*)

-----------------

71049

Execution Plan

----------------------------------------------------------

Plan hash value: 549732231

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 12 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| IDX_PARTITION_LEO9 | 3997 | 51961 | 12 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走全域性索引，在跨分割槽掃描資料時，效率要好於本地索引，因為不需要再多個索引段間跳躍

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">1000)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

199 consistent gets 199個一致性讀

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1@LEO1> select count(*) from partition_leo10 where object_id>1000;

COUNT(*)

-----------------

71057

Execution Plan

----------------------------------------------------------

Plan hash value: 3364377641

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 12 (0)| 00:00:01 | | |

| 1 | SORT AGGREGATE | | 1 | 13 | | | | |

| 2 | PARTITION RANGE ALL| | 3947 | 51311 | 12 (0)| 00:00:01 | 1 | 7 |

|* 3 | INDEX RANGE SCAN | IDX_PARTITION_LEO10 | 3947 | 51311 | 12 (0)| 00:00:01 | 1 | 7 |

------------------------------------------------------------------------------------------------------------

走本地索引，Pstart：掃描的起始分割槽號 1，Pstop：掃描的結束分割槽號 7，從這裡可以看出執行計劃在7個分割槽中的7個本地索引段間進行了跳躍掃描，這樣就增大了系統開銷

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_ID">1000)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

152 recursive calls

0 db block gets

290 consistent gets 290個一致性讀，比全域性索引多

162 physical reads

0 redo size

528 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

小結：在跨分割槽檢索資料時，全域性索引效率好於本地索引，原因上面我已經闡述說明了。

實驗分割槽內掃描

LEO1@LEO1> select /*+ index(partition_leo9 idx_partition_leo9) */ count(*) from partition_leo9 where object_id>1000 and object_id<8000;

COUNT(*)

----------------

6891

Execution Plan

----------------------------------------------------------

Plan hash value: 549732231

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 71 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| IDX_PARTITION_LEO9 | 3180 | 41340 | 71 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

如果不加hints的話CBO會走全表掃描的（其實全表掃描效率並不高，這次CBO犯傻了），所以我們強制使用hints走全域性索引，因為只在一個分割槽內檢索，還要掃描整個索引段，沒有縮小掃描範圍，效率自然沒有本地索引高

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<8000)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

20 consistent gets 20個一致性讀

0 physical reads

0 redo size

527 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

LEO1@LEO1> select /*+ index(partition_leo10 idx_partition_leo10) */ count(*) from partition_leo10 where object_id>1000 and object_id<8000;

COUNT(*)

----------

6891

Execution Plan

----------------------------------------------------------

Plan hash value: 785094176

---------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT| | 1 | 13 | 67 (0)| 00:00:01 | | |

| 1 | SORT AGGREGATE| | 1 | 13 | | | | |

| 2 | PARTITION RANGE SINGLE| | 3147 | 40911 | 67 (0)| 00:00:01 | 1 | 1 |

|* 3 | INDEX RANGE SCAN| IDX_PARTITION_LEO10 | 3147 | 40911 | 67 (0)| 00:00:01 | 1 | 1 |

---------------------------------------------------------------------------------------------------------------

同理我們強制使用hints走本地索引，看我們起始掃描分割槽和結束掃描分割槽都是同一個，哈->這就說明CBO執行計劃只對這一個分割槽做掃描，縮小了掃描的範圍，自然效率就高

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_ID">1000 AND "OBJECT_ID"<8000)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

17 consistent gets 17個一致性讀比全域性索引少

0 physical reads

0 redo size

527 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

小結：在分割槽內掃描資料時，本地索引優於全域性索引，原理我在上面都闡述清楚了，歡迎大家來討論交流。

Leonarding
2013.1.12
天津&winter
分享技術~成就夢想
Blog：www.leonarding.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-752577/，如需轉載，請註明出處，否則將追究法律責任。

【原創】ORACLE 分割槽與索引

相關文章