7.3　索引

索引可以說是Oracle數(shù)據(jù)庫中除了表以外最重要的對(duì)象了。通過添加索引來提高查詢性能，也是最為常見的一種優(yōu)化手段。甚至很多非DBA人員認(rèn)為，數(shù)據(jù)庫優(yōu)化就是加索引。這種觀點(diǎn)雖然有些偏頗，但也說明了索引對(duì)于優(yōu)化的重要意義。

Oracle數(shù)據(jù)庫支持多種索引。下面針對(duì)幾種常用的索引分別加以介紹。

1.B樹索引

B樹索引是Oracle數(shù)據(jù)庫的默認(rèn)索引，也是最為常見的一種索引。通常我們所說的索引都是特指B樹索引（見圖7-1）。那為什么使用B樹索引可以調(diào)高訪問速度呢？這就要從索引結(jié)構(gòu)來說明了。

圖7-1　B樹索引

整個(gè)索引結(jié)構(gòu)就是一個(gè)平衡樹（Balance Tree），這也就是稱為B樹索引的原因。在整個(gè)樹結(jié)構(gòu)中，包含有3種節(jié)點(diǎn)，分別是根節(jié)點(diǎn)（Root）、分支節(jié)點(diǎn)（Branch）、葉子節(jié)點(diǎn)（Leaf）。有的簡(jiǎn)單索引只有根節(jié)點(diǎn)和葉子節(jié)點(diǎn)。在根節(jié)點(diǎn)或分支節(jié)點(diǎn)中，存在一組鍵值范圍，當(dāng)通過條件訪問到這些節(jié)點(diǎn)時(shí)，根據(jù)鍵值范圍路由到不同的分支節(jié)點(diǎn)或葉子節(jié)點(diǎn)。例如上面示例中，如果輸入的條件是'AA'，那么首先查詢根節(jié)點(diǎn)，在這個(gè)節(jié)點(diǎn)中有一組鍵值BC，它代表將鍵值范圍分為3個(gè)區(qū)間，分別是X<B、B<X<C、C<X。因?yàn)檩斎氲臈l件是'AA'，故屬于第一個(gè)區(qū)間，相關(guān)數(shù)據(jù)會(huì)在對(duì)應(yīng)的第一個(gè)分支節(jié)點(diǎn)上。在第一個(gè)分支節(jié)點(diǎn)（L1-1）應(yīng)用同樣的方法，可知數(shù)據(jù)在第一個(gè)葉子節(jié)點(diǎn)（L0-1）。對(duì)于葉子節(jié)點(diǎn)來說，保存的每組記錄中，每條記錄包含兩部分信息：一是索引鍵值，二是對(duì)應(yīng)的行地址（ROWID）。通過行地址，就可以很快定位到數(shù)據(jù)塊中的記錄了。

下面通過一個(gè)示例說明為什么通過索引訪問會(huì)很快。

---

SQL> create table t1 as select * from dba_objects;
//表已創(chuàng)建

SQL> insert into t1 select * from t1;
//已創(chuàng)建 18870 行

SQL> /
//已創(chuàng)建 37740 行

SQL> /
//已創(chuàng)建 75480 行

SQL> /
//已創(chuàng)建 150960 行

SQL> /
//已創(chuàng)建 301920 行

SQL> commit;
//提交完成

SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    89 | 18423 |  2194   (1)| 00:00:27 |
|*  1 |  TABLE ACCESS FULL| T1   |    89 | 18423 |  2194   (1)| 00:00:27 |
--------------------------------------------------------------------------
統(tǒng)計(jì)信息
----------------------------------------------------------
0  recursive calls
0  db block gets
11251  consistent gets
0  physical reads

SQL> create index idx_object_id on t1(object_id);
//索引已創(chuàng)建

SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------------
| Id  | Operation      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT
                       |               |    89 | 18423 |  2188   (1)| 00:00:27 
|   1 |  TABLE ACCESS BY INDEX ROWID
                       | T1            |    89 | 18423 |  2188   (1)| 00:00:27
|*  2 |   INDEX RANGE SCAN
                       | IDX_OBJECT_ID |  2580 |       |     3   (0)| 00:00:01
--------------------------------------------------------------------------------
統(tǒng)計(jì)信息
----------------------------------------------------------
0  recursive calls
0  db block gets
38  consistent gets
0 physical reads
/*
從前后執(zhí)行對(duì)比來看，前者走了全表掃描，后者走了索引掃描。直觀地對(duì)比統(tǒng)計(jì)信息的“consistent gets”一欄，前者需要1萬多次一致性讀，后者只要數(shù)十次一致性讀，兩者差異巨大。自然，執(zhí)行時(shí)間也差異巨大
*/

---

2.位圖索引

位圖索引是另外一種較為常見的索引，雖然說是較為常見，但也僅限于個(gè)別場(chǎng)景，主要適用于分析型數(shù)據(jù)庫中。其原理與B樹索引完全不同。在Oracle的優(yōu)化器中，個(gè)別場(chǎng)景下可以將兩類索引相互轉(zhuǎn)換。這個(gè)在后面的章節(jié)會(huì)有詳細(xì)說明。

下面首先來看看位圖索引的結(jié)構(gòu)，示例如表7-1所示。

表7-1　位圖索引結(jié)構(gòu)

在上面的顯示中，10.0.3=>文件號(hào)＋塊號(hào)＋行號(hào)。從表7-1可見，位圖索引是在指定的地址范圍，若對(duì)應(yīng)記錄是某個(gè)鍵值，則對(duì)應(yīng)值設(shè)置為1，否則設(shè)置為0。從上面結(jié)構(gòu)可見，如果位圖索引的不同值很少，則空間占用很少。換句話說，其存儲(chǔ)密度很高。

下面通過一個(gè)示例說明位圖索引的用法。

---

create table t1 as select * from dba_objects where rownum<=50000;
//表已創(chuàng)建

update t1 set status='NOVALID' where object_id=20;
//更新3條

update t1 set status=NULL where object_id=21;
//更新1條

commit;
//提交完成
alter table t1 add constraint pk_t1 primary key(object_id);
//索引已創(chuàng)建

create bitmap index idx_status on t1(status);
//索引已創(chuàng)建

select count(*) from t1;
--------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE           |            |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT |            | 50000 |     2   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN
                                  | IDX_STATUS |       |            |          |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5  consistent gets
/*
默認(rèn)使用位圖索引，并且走的是位圖索引快速全掃描。即使位圖索引字段有空值，由于位圖索引保存空值，因此也沒有問題。此外，這也要看位圖索引字段值的基數(shù)，如果基數(shù)較低，則該位圖索引較小；如果基數(shù)很大，則位圖索引會(huì)很大。在基數(shù)很大的情況下，COUNT(*)會(huì)選擇B樹索引，而不會(huì)走位圖索引掃描
*/

select /*+ index(t1 pk_t1) */ count(*) from t1;
------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |   106   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| PK_T1 | 50000 |   106   (1)| 00:00:02 |
------------------------------------------------------------------
Statistics
----------------------------------------------------------
105  consistent gets
//強(qiáng)制使用主鍵索引(B樹索引)，可看到一致性讀大大增加。這也間接說明了位圖索引的高密度存儲(chǔ)特點(diǎn)

---

3.其他索引

上面我們談到了最為常見的兩種索引類型，下面再看看其他索引類型。從本質(zhì)上來講，它們還是B樹索引或者位圖索引。

（1）函數(shù)索引

函數(shù)索引就是將一個(gè)函數(shù)計(jì)算的結(jié)果存儲(chǔ)在列中，而不是存儲(chǔ)列數(shù)據(jù)本身，可以把基于函數(shù)的索引看成是一個(gè)虛擬列上的索引。總之，所謂函數(shù)索引也只不過是基于已加工的邏輯列所創(chuàng)建的索引而已。

---

SQL> create table t1 as select * from dba_objects;
//表已創(chuàng)建

SQL> create index idx_object_name on t1(object_name);
//索引已創(chuàng)建

SQL> select * from t1 where upper(object_name)='EMP';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   621 |    74   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |   621 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------
//雖然在object_name字段上建立了索引，但是由于使用了upper()函數(shù)，導(dǎo)致無法利用該索引

SQL> create index idx_object_name_upper on t1(upper(object_name));
//索引已創(chuàng)建

SQL> select * from t1 where upper(object_name)='EMP';
---------------------------------------------------------------------------------
| Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| 
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                       |   179 | 48867 |    35   (0)| 
|   1 |  TABLE ACCESS BY INDEX ROWID
                           | T1                    |   179 | 48867 |    35   (0)| 
|*  2 |   INDEX RANGE SCAN | IDX_OBJECT_NAME_UPPER |    72 |       |     1   (0)| 
---------------------------------------------------------------------------------
//創(chuàng)建了單獨(dú)的函數(shù)索引，此時(shí)的查詢就可以利用索引

---

（2）虛擬列索引

這里要先談一下虛擬列。虛擬列是在11g中新引入的一個(gè)技術(shù)，從字面上看，創(chuàng)建的列不是真正的物理保存，只是一個(gè)定義。而基于虛擬列創(chuàng)建的索引，就是虛擬列索引。在某種程度上，虛擬列索引和上面談到的函數(shù)索引有些類似。

（3）虛擬索引

在11g中，Oracle可以通過NOSEGMENT子句命令創(chuàng)建一個(gè)永遠(yuǎn)不會(huì)使用且不會(huì)為其分配任何盤區(qū)的索引。如果想要?jiǎng)?chuàng)建一個(gè)很大的索引，但并不想給它分配空間，則要先確定優(yōu)化器是否會(huì)選擇使用該索引。如果確定了這個(gè)索引是有用的，可以刪除該索引，然后使用不包含NOSEGMENT的語句重建它。

（4）不可見索引

不可見索引不是一種特殊的索引類型，而是使索引對(duì)優(yōu)化器“不可見”，導(dǎo)致沒有查詢會(huì)使用它。這對(duì)于評(píng)估索引使用效果非常有幫助，特別是對(duì)某些第三方應(yīng)用，無法修改代碼，這個(gè)特性十分有用。下面通過一個(gè)示例說明。

---

SQL> create table t1 as select * from dba_objects;
//表已創(chuàng)建

SQL> create index idx_id on t1(object_id);
//索引已創(chuàng)建

SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     3 |   621 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                              | T1     |     3 |   621 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IDX_ID |    73 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> alter index idx_id invisible;
//索引已更改

SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   621 |    74   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |   621 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------
//將索引設(shè)置為不可見后，優(yōu)化器將不考慮這個(gè)索引，因此選用了全表掃描方式

---

（5）壓縮索引

Oracle中的索引鍵允許壓縮存儲(chǔ)索引鍵中前面重復(fù)的部分，并且是每個(gè)葉塊而不是每個(gè)葉塊中的每行存儲(chǔ)重復(fù)的值。壓縮和非壓縮索引在使用上差別不大，但壓縮索引能節(jié)省大量空間。利用壓縮索引，塊緩沖區(qū)緩存比以前能存放更多的索引條目，緩存命中率可能會(huì)上升，物理I/O應(yīng)該會(huì)下降，但是要多占用一些CPU時(shí)間來處理索引，還會(huì)增加塊競(jìng)爭(zhēng)的可能性。

下面通過一個(gè)示例說明。

---

SQL> create table t as select * from all_objects;
//表已創(chuàng)建

SQL> create table idx_stats as select '       ' what,a.* from index_stats a where 1=0;
//表已創(chuàng)建

SQL> create index t_idx_0 on t(owner,object_type,object_name);
//索引已創(chuàng)建

SQL> analyze index t_idx_0 validate structure;
//索引已分析

SQL> insert into idx_stats select 'compress_0',a.* from index_stats a where a.name='T_IDX_0';
已創(chuàng)建 1 行

SQL> drop index t_idx_0;
//索引已刪除

SQL> create index t_idx_1 on t(owner,object_type,object_name) compress 1;
//索引已創(chuàng)建

SQL> analyze index t_idx_1 validate structure;
//索引已分析

SQL> insert into idx_stats select 'compress_1',a.* from index_stats a where a.name='T_IDX_1';
//已創(chuàng)建 1 行

SQL> drop index t_idx_1;
//索引已刪除

SQL> create index t_idx_2 on t(owner,object_type,object_name) compress 2;
//索引已創(chuàng)建

SQL> analyze index t_idx_2 validate structure;
//索引已分析

SQL> insert into idx_stats select 'compress_2',a.* from index_stats a where a.name='T_IDX_2';
//已創(chuàng)建 1 行

SQL> select what,height,lf_blks,br_blks,btree_space,opt_cmpr_count,opt_cmpr_pctsave from idx_stats;
WHAT        HEIGHT  LF_BLKS  BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ------- -------- -------- ----------- -------------- ----------------
compress_0       2      109        1      880032              2               29
compress_1       2       94        1      759656              2               18
compress_2       2       76        1      615728              2                0
/*
從輸出可見，對(duì)于不壓縮、壓縮一個(gè)字段(compress=1)、壓縮兩個(gè)字段(compress=2)，對(duì)應(yīng)索引的葉子節(jié)點(diǎn)明顯減少
*/

---

（6）復(fù)合索引

當(dāng)某個(gè)索引包含多個(gè)已索引列時(shí)，這個(gè)索引就稱為復(fù)合索引。如果查詢條件中包含多個(gè)列，往往可以應(yīng)用到復(fù)合索引。下面通過一個(gè)示例說明。

---

SQL> create table t1 as select * from dba_objects;
//表已創(chuàng)建

SQL> create index idx_1 on t1(owner,object_id);
//索引已創(chuàng)建

SQL> select * from t1 where owner='SYS' and object_id=20;
--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     2 |   414 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID
                               | T1    |     2 |   414 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN     | IDX_1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
//此時(shí)利用了復(fù)合索引

---

（7）反轉(zhuǎn)索引

反轉(zhuǎn)索引是一種特殊的B樹索引。它將索引列中列值的每個(gè)字節(jié)的位置反轉(zhuǎn)。例如“12345”，反轉(zhuǎn)之后是“54321”。其最大特點(diǎn)就是對(duì)于原來相連比較緊密的值，強(qiáng)制使其分散到相距比較遠(yuǎn)的位置上。這樣可以使數(shù)據(jù)更均勻地分布。但由于反轉(zhuǎn)索引的特點(diǎn)，導(dǎo)致只有精準(zhǔn)匹配查找才能使用反轉(zhuǎn)索引。下面通過一個(gè)示例說明。

---

create table t1 as select rownum id from dba_objects;
create index t1_idx on t1(id);
alter index idx_ t1_idx rebuild reverse;

---