Oracle Index 索引介紹
索引在各種關系型數據庫系統中都是舉足輕重的組成部分，其對于提高檢索數據的速度起至關重要的作用。在Oracle中，索引基本分為以下幾種：B*Tree索引，反向索引，降序索引，位圖索引，函數索引，interMedia全文索引等。
Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對于一個應用程序的性能來說非常重要。一個錯誤的選擇可能會引發死鎖，并導致數據庫性能急劇下降或進程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經運行了幾個小時甚至幾天的進程在幾分鐘得以完成，這樣會使您立刻成為一位英雄。這篇文章就將簡單的討論每個索引選項。主要有以下內容：
[1] 基本的索引概念
查詢DBA_INDEXES視圖可得到表中所有索引的列表，注意只能通過USER_INDEXES的方法來檢索模式(schema)的索引。訪問USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。
[2] 組合索引
當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。在 Oracle9i引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表emp有一個組合索引鍵，該索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中對第一列（empno）指定一個值，否則就不能使用這個索引鍵進行一次范圍掃描。
特別注意：在Oracle9i之前，只有在使用到索引的前導索引時才可以使用組合索引！
[3] ORACLE ROWID
通過每個行的ROWID，索引Oracle提供了訪問單行數據的能力。ROWID其實就是直接指向單獨行的線路圖。如果想檢查重復值或是其他對ROWID本身的引用，可以在任何表中使用和指定rowid列。
[4] 限制索引
限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。下面討論一些常見的問題：
4.1 使用不等于操作符（<>、!=）
下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的語句改成如下的查詢語句，這樣，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，將會使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
特別注意：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。
4.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同樣會限制索引的使用。因為NULL值并沒有被定義。在SQL語句中使用NULL會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引，關于位圖索引在稍后在詳細討論）。
4.3 使用函數
如果不使用基于函數的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。
下面的查詢不會使用索引（只要它不是基于函數的索引）
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的語句改成下面的語句，這樣就可以通過索引進行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999);
4.4 比較不匹配的數據類型
比較不匹配的數據類型也是比較難于發現的性能問題之一。
注意下面查詢的例子，account_number是一個VARCHAR2類型，在account_number字段上有索引。下面的語句將執行全表掃描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自動把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特別注意：不匹配的數據類型之間比較會讓Oracle自動限制索引的使用，即便對這個查詢執行Explain Plan也不能讓您明白為什么做了一次“全表掃描”。
[5] 選擇性
使用USER_INDEXES視圖，該視圖中顯示了一個distinct_keys列。比較一下唯一鍵的數量和表中的行數，就可以判斷索引的選擇性。選擇性越高，索引返回的數據就越少。
[6] 群集因子(Clustering Factor)
Clustering Factor位于USER_INDEXES視圖中。該列反映了數據相對于已索引的列是否顯得有序。如果Clustering Factor列的值接近于索引中的樹葉塊(leaf block)的數目，表中的數據就越有序。如果它的值接近于表中的行數，則表中的數據就不是很有序。
[7] 二元高度(Binary height)
索引的二元高度對把ROWID返回給用戶進程時所要求的I/O量起到關鍵作用。在對一個索引進行分析后，可以通過查詢DBA_INDEXES的B-level列查看它的二元高度。二元高度主要隨著表的大小以及被索引的列中值的范圍的狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會增加。更新索引列也類似于刪除操作，因為它增加了已刪除鍵的數目。重建索引可能會降低二元高度。
[8] 快速全局掃描
在Oracle7.3后就可以使用快速全局掃描(Fast Full Scan)這個選項。這個選項允許Oracle執行一個全局索引掃描操作。快速全局掃描讀取B-樹索引上所有樹葉塊。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT參數可以控制同時被讀取的塊的數目。
[9] 跳躍式掃描
從Oracle9i開始，索引跳躍式掃描特性可以允許優化器使用組合索引，即便索引的前導列沒有出現在WHERE子句中。索引跳躍式掃描比全索引掃描要快的多。下面的程序清單顯示出性能的差別：
create index skip1 on emp5(job,empno);
index created.
select count(*)
from emp5
where empno=7900;
Elapsed:00:00:03.13
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
6826 consistent gets
6819 physical reads
select /*+ index(emp5 skip1)*/ count(*)
from emp5
where empno=7900;
Elapsed:00:00:00.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
1 0 SORT(AGGREGATE)
2 1 INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)
Statistics
21 consistent gets
17 physical reads
[10] 索引的類型
B-樹索引
位圖索引
HASH索引
索引編排表
反轉鍵索引
基于函數的索引
分區索引
本地和全局索引
簡要解釋：
b*tree index：幾乎所有的關系型數據庫中都有b*tree類型索引，也是被最多使用的。其樹結構與二叉樹比較類似，根據rid快速定位所訪問的行。
反向索引：反轉了b*tree索引碼中的字節，是索引條目分配更均勻，多用于并行服務器環境下，用于減少索引葉的競爭。
降序索引：8i中新出現的索引類型，針對逆向排序的查詢。
位圖索引：使用位圖來管理與數據行的對應關系，多用于OLAP系統。
函數索引：這種索引中保存了數據列基于function返回的值，在select * from table where function(column)=value這種類型的語句中起作用。
B*Tree索引
B*Tree索引是最常見的索引結構，默認建立的索引就是這種類型的索引。B*Tree索引在檢索高基數數據列（高基數數據列是指該列有很多不同的值）時提供了最好的性能。當取出的行數占總行數比例較小時B-Tree索引比全表檢索提供了更有效的方法。但當檢查的范圍超過表的10%時就不能提高取回數據的性能。B-Tree索引是基于二叉樹的，由分支塊（branch block）和葉塊（leaf block）組成。在樹結構中，位于最底層底塊被稱為葉塊，包含每個被索引列的值和行所對應的rowid。在葉節點的上面是分支塊，用來導航結構，包含了索引列（關鍵字）范圍和另一索引塊的地址。
假設我們要找索引中值為80的行，從索引樹的最上層入口開始，定位到大于等于50，然后往左找，找到第2個分支塊，定位為75－100，最后再定位到葉塊上，找到80所對應的rowid，然后根據rowid去讀取數據塊獲取數據。如果查詢條件是范圍選擇的，比如where column >20 and column <80，那么會先定位到第一個包含20的葉塊，然后橫向查找其他的葉塊，直到找到包含80的塊為止，不用每次都從入口進去再重新定位。
反向索引
反向索引是B*Tree索引的一個分支，它的設計是為了運用在某些特定的環境下的。Oracle推出它的主要目的就是為了降低在并行服務器（Oracle Parallel Server）環境下索引葉塊的爭用。當B*Tree索引中有一列是由遞增的序列號產生的話，那么這些索引信息基本上分布在同一個葉塊，當用戶修改或訪問相似的列時，索引塊很容易產生爭用。反向索引中的索引碼將會被分布到各個索引塊中，減少了爭用。反向索引反轉了索引碼中每列的字節，通過dump（）函數我們可以清楚得看見它做了什么。舉個例子：1，2，3三個連續的數，用dump()函數看它們在Oracle內部的表示方法。
SQL> select 'number',dump(1,16) from dual
union all select 'number',dump(2,16) from dual
union all select 'number',dump(3,16) from dual;
'NUMBE DUMP(1,16)
------ -----------------
number Typ=2 Len=2: c1,2 （1）
number Typ=2 Len=2: c1,3 （2）
number Typ=2 Len=2: c1,4 （3）
再對比一下反向以后的情況：
SQL> select 'number',dump(reverse(1),16) from dual
2 union all select 'number',dump(reverse(2),16) from dual
3 union all select 'number',dump(reverse(3),16) from dual;
'NUMBE DUMP(REVERSE(1),1
------ -----------------
number Typ=2 Len=2: 2,c1 （1）
number Typ=2 Len=2: 3,c1 （2）
number Typ=2 Len=2: 4,c1 （3）
我們發現索引碼的結構整個顛倒過來了，這樣1，2，3個索引碼基本上不會出現在同一個葉塊里，所以減少了爭用。不過反向索引又一個缺點就是不能在所有使用常規索引的地方使用。在范圍搜索中其不能被使用，例如，where column>value,因為在索引的葉塊中索引碼沒有分類，所以不能通過搜索相鄰葉塊完成區域掃描。
降序索引
降序索引是8i里面新出現的一種索引，是B*Tree的另一個衍生物,它的變化就是列在索引中的儲存方式從升序變成了降序，在某些場合下降序索引將會起作用。舉個例子，我們來查詢一張表并進行排序：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
2 1 INDEX (RANGE SCAN) OF 'IND_BT' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
這里優化器首先選擇了一個索引范圍掃描,然后還有一個排序的步驟。如果使用了降序索引，排序的過程會被取消。
SQL> create index test.ind_desc on test.testrev(a desc，b asc);
索引已創建。
SQL> analyze index test.ind_desc compute statistics;
索引已分析
再來看下執行路徑：
SQL> select * from test where a between 1 and 100 order by a desc，b asc;
已選擇100行。
Execution Plan（SQL執行計劃，稍后會講解如何使用）。
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1 0 INDEX (RANGE SCAN) OF 'IND_DESC' (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
我們看到排序過程消失了，這是因為創建降序索引時Oracle已經把數據都按降序排好了。
另外一個需要注意的地方是要設置init.ora里面的compatible參數為8.1.0或以上，否則創建時desc關鍵字將被忽略。
位圖索引
位圖索引主要用于決策支持系統或靜態數據，不支持行級鎖定。位圖索引最好用于低cardinality列（即列的唯一值除以行數為一個很小的值，接近零），例如又一個“性別”列，列值有“Male”，“Female”，“Null”等3種，但一共有300萬條記錄，那么3/3000000約等于0，這種情況下最適合用位圖索引。
位圖索引可以是簡單的（單列）也可以是連接的（多列），但在實踐中絕大多數是簡單的。在這些列上多位圖索引可以與AND或OR操作符結合使用。位圖索引使用位圖作為鍵值，對于表中的每一數據行位圖包含了TRUE（1）、FALSE（0）、或NULL值。位圖索引的位圖存放在B-Tree結構的頁節點中。B-Tree結構使查找位圖非常方便和快速。另外，位圖以一種壓縮格式存放，因此占用的磁盤空間比B-Tree索引要小得多。位圖索引的格式如表26-1所示。
表26-1 位圖索引的格式
行
值 1 2 3 4 5 6 7 8 9 10
Male 1 0 0 0 0 0 0 0 1 1
Female 0 1 1 1 0 0 1 1 0 0
Null 0 0 0 0 1 1 0 0 0 0
如果搜索where gender=’Male’,要統計性別是”Male”的列行數的話，Oracle很快就能從位圖中找到共3行即第1，9，10行是符合條件的；如果要搜索where gender=’Male’ or gender=’Female’的列的行數的話，也很容易從位圖中找到共8行即1，2，3，4，7，8，9，10行是符合條件的。如果要搜索表的值的話，那么Oracle會用內部的轉換函數將位圖中的相關信息轉換成rowid來訪問數據塊。
函數索引
基于函數的索引也是8i以來的新產物，它有索引計算列的能力，它易于使用并且提供計算好的值，在不修改應用程序的邏輯上提高了查詢性能。使用基于函數的索引有幾個先決條件：
1）必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）權限。
2）必須使用基于成本的優化器，基于規則的優化器將被忽略。
3）必須設置以下兩個系統參數：
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通過alter system set,alter session set在系統級或線程級設置，也可以通過在init.ora添加實現。
這里舉一個基于函數的索引的例子：
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已創建。
SQL> insert into testindex values('a',2);
已創建 1 行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(優化器選擇了全表掃描)
--------------------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(使用了ind_fun索引)
各種索引的創建方法
（1）*Tree索引。
Create index indexname on tablename(columnname[columnname...])
（2）反向索引。
Create index indexname on tablename(columnname[columnname...]) reverse
（3）降序索引。
Create index indexname on tablename(columnname DESC[columnname...])
（4）位圖索引。
Create BITMAP index indexname on tablename(columnname[columnname...])
（5）函數索引。
Create index indexname on tablename(functionname(columnname))
注意：創建索引后分析要索引才能起作用。
analyze index indexname compute statistics;
各種索引使用場合及建議
（1）B*Tree索引。
常規索引，多用于oltp系統，快速定位行，應建立于高cardinality列（即列的唯一值除以行數為一個很大的值，存在很少的相同值）。
（2）反向索引。
B*Tree的衍生產物，應用于特殊場合，在ops環境加序列增加的列上建立，不適合做區域掃描。
（3）降序索引。
B*Tree的衍生產物，應用于有降序排列的搜索語句中，索引中儲存了降序排列的索引碼，提供了快速的降序搜索。
（4）位圖索引。
位圖方式管理的索引，適用于OLAP（在線分析）和DSS（決策處理）系統，應建立于低cardinality列，適合集中讀取，不適合插入和修改，提供比B*Tree索引更節省的空間。
（5）函數索引。
B*Tree的衍生產物，應用于查詢語句條件列上包含函數的情況，索引中儲存了經過函數計算的索引碼值?？梢栽诓恍薷膽贸绦虻幕A上能提高查詢效率。
索引什么時候不工作
首先要聲明兩個知識點：
（1）RBO&CBO。
Oracle有兩種執行優化器，一種是RBO(Rule Based Optimizer)基于規則的優化器，這種優化器是基于sql語句寫法選擇執行路徑的；另一種是CBO（Cost Based Optimizer）基于規則的優化器，這種優化器是Oracle根據統計分析信息來選擇執行路徑，如果表和索引沒有進行分析，Oracle將會使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能選擇錯誤執行路徑，不過CBO是Oracle發展的方向，自8i版本來已經逐漸取代RBO.
（2）AUTOTRACE。
要看索引是否被使用我們要借助Oracle的一個叫做AUTOTRACE功能,它顯示了sql語句的執行路徑，我們能看到Oracle內部是怎么執行sql的，這是一個非常好的輔助工具，在sql調優里廣泛被運用。我們來看一下怎么運用AUTOTRACE：
① 由于AUTOTRACE自動為用戶指定了Execution Plan,因此該用戶使用AUTOTRACE前必須已經建立了PLAN_TABLE。如果沒有的話，請運行utlxplan.sql腳本（它在$ORACLE_HOME/rdbms/admin目錄中）。
② AUTOTRACE可以通過運行plustrce.sql腳本(它在$ORACLE_HOME/sqlplus/admin目錄中)來設置，用sys用戶登陸然后運行plustrce.sql后會建立一個PLUSTRACE角色，然后給相關用戶授予PLUSTRACE角色，然后這些用戶就可以使用AUTOTRACE功能了。
③ AUTOTRACE的默認使用方法是set autotrace on，但是這方法不總是適合各種場合，特別當返回行數很多的時候。Set autotrace traceonly提供了只查看統計信息而不查詢數據的功能。
SQL> set autotrace on
SQL> select * from test;
A
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
SQL> set autotrace traceonly
SQL> select * from test.test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
rows processed
Hints是Oracle提供的一個輔助用法，按字面理解就是‘提示’的意思，確實它起得作用也是提示優化器按它所提供的關鍵字來選擇執行路徑，特別適用于sql調整的時候。使用方法如下：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
具體可參考Oracle SQL Reference。
有了前面這些知識點，接下來讓我們來看一下什么時候索引是不起作用的。以下列出幾種情況。
（1）類型不匹配時。
SQL> create table test.testindex (a varchar(2),b number);
表已創建。
SQL> create index ind_cola on test.testindex(a);
索引已創建。
SQL> insert into test.testindex values('1',1);
已創建 1 行。
SQL> commit;
提交完成。
SQL> analyze table test.testindex compute statistics for all indexes;
表已分析。
SQL> set autotrace on;
SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的優化器，數據類型匹配的情況下)
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）
――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM test.testindex where a=1;（數據類型不匹配的情況）
A B
-- ----------
1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（優化器選擇了全表掃描）
（2）條件列包含函數但沒有創建函數索引。
SQL> select /*+ RULE */* FROM test.testindex where upper(a)= 'A';（使用了函數upper()在列a上）;
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'（優化器選擇全表掃描）
----------------------------------------------------------
創建基于函數的索引
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已創建。
SQL> insert into testindex values('a',2);
已創建1行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/* FROM test.testindex where upper(a)='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'
(在RULE優化器下忽略了函數索引選擇了全表掃描)
-----------------------------------------------------------
SQL> select * FROM test.testindex where upper(a)
='A';
A B
-- ----------
a 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
1 Bytes=5)
2 1 INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
d=1)(CBO優化器使用了ind_fun索引)
（3）復合索引中的前導列沒有被作為查詢條件。
創建一個復合索引
SQL> create index ind_com on test.testindex(a,b);
索引已創建。
SQL> select /*+ RULE*/* from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(條件列表包含前導列時使用索引ind_com)
SQL> select /*+ RULE*/* from test.testindex where b=1;
未選定行
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX'(條件列表不包括前導列是選擇全表掃描)
-----------------------------------------------------------
（4）CBO模式下選擇的行數比例過大，優化器采取了全表掃描。
SQL> select * from test.testindex where a='1';
A B
-- ----------
1 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)
（表一共2行，選擇比例為50%,所以優化器選擇了全表掃描）
――――――――――――――――――――――――――――――――――
下面增加表行數
SQL> declare i number;
2 begin
3 for i in 1 .. 100 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
102
SQL> select * from test.testindex where a='1';
A B
---- ----------
1 1
1 2
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
（表一共102行，選擇比例為2/102=2%,所以優化器選擇了索引掃描）
（5）CBO模式下表很久沒分析，表的增長明顯，優化器采取了全表掃描。
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
已選擇13行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，選擇比例為13/102>10%,優化器選擇了全表掃描)
――――――――――――――――――――――――――――――――――
增加表行數
SQL> declare i number;
2 begin
3 for i in 200 .. 1000 loop
4 insert into test.testindex values (to_char(i),i);
5 end loop;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
100 100
1000 1000
已選擇14行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共903行，選擇比例為14/903<5%,優化器選擇了全表掃描,選擇路徑是錯誤的)
―――――――――――――――――――――――――――――
給表做分析
SQL> analyze table test.testindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A B
---- ----------
1 2
1 1
10 10
100 100
1000 1000
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
已選擇14行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
24 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
rd=24)
（經過分析后優化器選擇了正確的路徑，使用了ind_cola索引）
posted on 2008-01-15 21:43 mjgforever 閱讀(9185) 評論(1) 收藏 舉報
浙公網安備 33010602011771號