Oracle中B-Tree、Bitmap和函數(shù)索引使用案例總結(jié)

目錄

一、索引簡(jiǎn)介

1、索引是一個(gè)獨(dú)立的數(shù)據(jù)庫對(duì)象，和數(shù)據(jù)表table一樣。在Oracle中，數(shù)據(jù)庫對(duì)象object都是通過段segment結(jié)構(gòu)表示。我們?cè)跀?shù)據(jù)字典dba_segment中，可以使用索引的名稱搜索出與segment_name相等的字典項(xiàng)目。

2、索引是數(shù)據(jù)庫對(duì)象之一，用于加快數(shù)據(jù)的檢索，類似于書籍的索引。在數(shù)據(jù)庫中索引可以減少數(shù)據(jù)庫程序查詢結(jié)果時(shí)需要讀取的數(shù)據(jù)量，類似于在書籍中我們利用索引可以不用翻閱整本書即可找到想要的信息。

3、索引是建立在表上的可選對(duì)象；索引的關(guān)鍵在于通過一組排序后的索引鍵來取代默認(rèn)的全表掃描檢索方式，從而提高檢索效率

4、索引在邏輯上和物理上都與相關(guān)的表和數(shù)據(jù)無關(guān)，當(dāng)創(chuàng)建或者刪除一個(gè)索引時(shí)，不會(huì)影響基本的表；

5、索引一旦建立，在表上進(jìn)行DML操作時(shí)（例如在執(zhí)行插入、修改或者刪除相關(guān)操作時(shí)），oracle會(huì)自動(dòng)管理索引，索引刪除，不會(huì)對(duì)表產(chǎn)生影響

6、索引對(duì)用戶是透明的，無論表上是否有索引，sql語句的用法不變

7、Oracle數(shù)據(jù)庫會(huì)為表的主鍵和包含唯一約束的列自動(dòng)創(chuàng)建索引。

8、數(shù)據(jù)表和索引是可以分開進(jìn)行存儲(chǔ)的。通常，從性能角度考慮我們常將兩者放置在不同的Tablespace中，這樣做的目的主要是為了分散物理IO。

二、索引原理

1、若沒有索引，搜索某個(gè)記錄時(shí)（例如查找name='wjq'）需要搜索所有的記錄，因?yàn)椴荒鼙ＷC只有一個(gè)wjq，必須全部搜索一遍

2、若在name上建立索引，oracle會(huì)對(duì)全表進(jìn)行一次搜索，將每條記錄的name值按照升序排列，然后構(gòu)建索引條目（name和rowid），存儲(chǔ)到索引段中，查詢name為wjq時(shí)即可直接查找對(duì)應(yīng)地方

3、創(chuàng)建了索引并不一定就會(huì)使用，oracle自動(dòng)統(tǒng)計(jì)表的信息后，決定是否使用索引，表中數(shù)據(jù)很少時(shí)使用全表掃描速度已經(jīng)很快，沒有必要使用索引

三、索引的分類

3.1邏輯分類

3.2物理分類

下面重點(diǎn)講解B-Tree索引、Bitmap索引和函數(shù)索引。

1、B-Tree索引

a、B-Tree索引是Oracle中最常用的索引；B樹索引就是一顆二叉樹（平衡樹），左右兩個(gè)分支相對(duì)平衡；葉子節(jié)點(diǎn)（雙向鏈表）包含索引列和指向表中每個(gè)匹配行的ROWID值。

b、所有葉子節(jié)點(diǎn)具有相同的深度，因而不管查詢條件怎樣，查詢速度基本相同

c、能夠適應(yīng)精確查詢、模糊查詢和比較查詢

說明：

Root為根節(jié)點(diǎn)，branch為分支節(jié)點(diǎn)，leaf到最下面一層稱為葉子節(jié)點(diǎn)。每個(gè)節(jié)點(diǎn)表示一層，當(dāng)查找某一數(shù)據(jù)時(shí)先讀根節(jié)點(diǎn)，再讀支節(jié)點(diǎn)，最后找到葉子節(jié)點(diǎn)。葉子節(jié)點(diǎn)會(huì)存放index entry（索引入口），每個(gè)索引入口對(duì)應(yīng)一條記錄。

Index entry 的組成部分：

Indexentry entry header  存放一些控制信息。

Key column length   　　某一key的長(zhǎng)度

Key column value   　　 某一個(gè)key 的值

ROWID  　　                指針，具體指向于某一個(gè)數(shù)據(jù)

創(chuàng)建索引

--創(chuàng)建一張測(cè)試表，并插入1000行數(shù)據(jù)
SEIANG@seiang11g>create table tb_test1(id int,sex char(4),name varchar2(30)) tablespace seiang;
Table created.

SEIANG@seiang11g>begin
  2  for i in 1..1000 loop
  3  insert into tb_test1 values(i,'M','wjq'||i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from tb_test1;

  COUNT(*)
----------
      1000

SEIANG@seiang11g>
SEIANG@seiang11g>select * from tb_test1 where rownum<10;

        ID SEX  NAME
---------- ---- ------------------------------
       702 M    wjq702
       703 M    wjq703
       704 M    wjq704
       705 M    wjq705
       706 M    wjq706
       707 M    wjq707
       708 M    wjq708
       709 M    wjq709
       710 M    wjq710

9 rows selected.


--創(chuàng)建B-Tree索引
SEIANG@seiang11g>create index idx_test1_id on tb_test1(id) tablespace wjq_index;

Index created.

SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
IDX_TEST1_ID                   INDEX
TB_TEST1                       TABLE

索引分離于表，作為一個(gè)單獨(dú)的個(gè)體存在，除了可以根據(jù)單個(gè)字段創(chuàng)建索引，也可以根據(jù)多列創(chuàng)建索引。Oracle要求創(chuàng)建索引最多不可超過32列。

SEIANG@seiang11g>create index idx_test1_sex_name on tb_test1(sex,name) tablespace wjq_index;

Index created.

SEIANG@seiang11g>
SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID','IDX_TEST1_SEX_NAME');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
IDX_TEST1_ID                   INDEX
IDX_TEST1_SEX_NAME             INDEX
TB_TEST1                       TABLE

這里需要理解：

　　編寫一本書，只有章節(jié)頁面定好之后再設(shè)置目錄；數(shù)據(jù)庫索引也是一樣，只有先插入好數(shù)據(jù)，再建立索引。那么我們后續(xù)對(duì)數(shù)據(jù)庫的內(nèi)容進(jìn)行插入、刪除，索引也需要隨之變化。但索引的修改是由oracle自動(dòng)完成的。

上面這張圖能更加清晰的描述索引的結(jié)構(gòu)。

根節(jié)點(diǎn)記錄0至50條數(shù)據(jù)的位置，分支節(jié)點(diǎn)進(jìn)行拆分記錄0至10……42至50，葉子節(jié)點(diǎn)記錄每第數(shù)據(jù)的長(zhǎng)度和值，并由指針指向具體的數(shù)據(jù)。最后一層的葉子節(jié)是雙向鏈接，它們是被有序的鏈接起來，這樣才能快速鎖定一個(gè)數(shù)據(jù)范圍。

例如：

SEIANG@seiang11g>select * from tb_test1 where id>23 and id<32;

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2241831008

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     8 |   288 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST1     |     8 |   288 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1_ID |     8 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">23 AND "ID"<32)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        849  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

如上面查找的列子，通過索引的方式先找到第23條數(shù)據(jù)，再找到第32條數(shù)據(jù)，這樣就能快速的鎖定一個(gè)查找的范圍，如果每條數(shù)據(jù)都要從根節(jié)點(diǎn)開始查找的話，那么效率就會(huì)非常低下。

2、Bitmap索引

a、創(chuàng)建位圖索引時(shí)，oracle會(huì)掃描整張表，并為索引列的每個(gè)取值建立一個(gè)位圖（位圖中，對(duì)表中每一行使用一位（bit，0或者1）來標(biāo)識(shí)該行是否包含該位圖的索引列的取值，如果為1，表示對(duì)應(yīng)的rowid所在的記錄包含該位圖索引列值），最后通過位圖索引中的映射函數(shù)完成位到行的ROWID的轉(zhuǎn)換

b、位圖索引主要針對(duì)大量相同值的列而創(chuàng)建。拿全國(guó)居民登錄表來說，假設(shè)有四個(gè)字段：姓名、性別、年齡、和身份證號(hào)，年齡和性別兩個(gè)字段會(huì)產(chǎn)生許多相同的值，性別只有男女兩種取值，年齡，1到120（假設(shè)最大年齡120歲）個(gè)值。那么不管一張表有幾億條記錄，但根據(jù)性別字段來區(qū)分的話，只有兩種取值（男、女）。那么位圖索引就是根據(jù)字段的這個(gè)特性所建立的一種索引。

c、對(duì)于基數(shù)小的列適合簡(jiǎn)歷位圖索引（例如性別等）

從上圖，我們可以看出，一個(gè)葉子節(jié)點(diǎn)（用不同顏色標(biāo)識(shí)）代表一個(gè)key, start rowid和end rowid規(guī)定這種類型的檢索范圍，一個(gè)葉子節(jié)點(diǎn)標(biāo)記一個(gè)唯一的bitmap值。因?yàn)橐粋€(gè)數(shù)值類型對(duì)應(yīng)一個(gè)節(jié)點(diǎn)，當(dāng)進(jìn)行查詢時(shí)，位圖索引通過不同位圖取值直接的位運(yùn)算（與或），來獲取到結(jié)果集合向量（計(jì)算出的結(jié)果）。

舉例講解：

假設(shè)存在數(shù)據(jù)表T，有兩個(gè)數(shù)據(jù)列A和B，取值如下，我們看到A和B列中存在相同的數(shù)據(jù)。

對(duì)兩個(gè)數(shù)據(jù)列A、B分別建立位圖索引：idx_t_bita和idx_t_bitb。兩個(gè)索引對(duì)應(yīng)的存儲(chǔ)邏輯結(jié)構(gòu)如下：

Idx_t_bita索引結(jié)構(gòu)，對(duì)應(yīng)的是葉子節(jié)點(diǎn)：

Idx_t_bitb索引結(jié)構(gòu)，對(duì)應(yīng)的是葉子節(jié)點(diǎn)：

對(duì)查詢“select * from t where b=1 and (a=’L’ or a=’M’)”

分析：

位圖索引使用方面，和B*索引有很大的不同。B*索引的使用，通常是從根節(jié)點(diǎn)開始，經(jīng)過不斷的分支節(jié)點(diǎn)比較到最近的符合條件葉子節(jié)點(diǎn)。通過葉子節(jié)點(diǎn)上的不斷scan操作，“掃描”出結(jié)果集合rowid。

而位圖索引的工作方式截然不同。通過不同位圖取值直接的位運(yùn)算（與或），來獲取到結(jié)果集合向量（計(jì)算出的結(jié)果）。

針對(duì)實(shí)例SQL，可以拆分成如下的操作：

1、a=’L’ or a=’M’

a=L：向量：1010

a=M：向量：0001

or操作的結(jié)果，就是兩個(gè)向量的或操作：結(jié)果為1011。

2、結(jié)合b=1的向量

中間結(jié)果向量：1011

B=1：向量：1001

and操作的結(jié)果，1001。翻譯過來就是第一和第四行是查詢結(jié)果。

3、獲取到結(jié)果rowid

目前知道了起始rowid和終止rowid，以及第一行和第四行為操作結(jié)果。可以通過試算的方法獲取到結(jié)果集合rowid。

位圖索引的特點(diǎn)：

（1）Bitmap索引的存儲(chǔ)空間節(jié)省 

（2）Bitmap索引創(chuàng)建的速度快

（3）Bitmap索引允許鍵值為空 

（4）Bitmap索引對(duì)表記錄的高效訪問

創(chuàng)建Bitmap索引

--接著上面B-Tree索引所創(chuàng)建的表tb_test1為例，基于該表來創(chuàng)建Bitmap索引
對(duì)于上面表來說sex（性別）只有兩種值，最適合用來創(chuàng)建位圖所引
SEIANG@seiang11g>create bitmap index bitmap_idx_test1_sex on tb_test1(sex) tablespace wjq_index;

Index created.

SEIANG@seiang11g>select object_name,object_type from user_objects where object_name in ('TB_TEST1','BITMAP_IDX_TEST1_SEX');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
BITMAP_IDX_TEST1_SEX           INDEX
TB_TEST1                       TABLE

SEIANG@seiang11g>select * from tb_test1 where sex='M';

1000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2608569169

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |  1000 | 36000 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TB_TEST1             |  1000 | 36000 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                      |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BITMAP_IDX_TEST1_SEX |       |       |            |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SEX"='M')

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


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
      33757  bytes sent via SQL*Net to client
       1249  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--查看表tb_test1上的所有創(chuàng)建的索引及類型
SEIANG@seiang11g>select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='TB_TEST1';

INDEX_NAME                     INDEX_TYPE           TABLE_NAME                     COLUMN_NAME          STATUS
------------------------------ -------------------- ------------------------------ -------------------- --------
IDX_TEST1_ID                   NORMAL               TB_TEST1                       ID                   VALID
IDX_TEST1_SEX_NAME             NORMAL               TB_TEST1                       SEX                  VALID
IDX_TEST1_SEX_NAME             NORMAL               TB_TEST1                       NAME                 VALID
BITMAP_IDX_TEST1_SEX           BITMAP               TB_TEST1                       SEX                  VALID

3、函數(shù)索引

a、當(dāng)經(jīng)常要訪問一些函數(shù)或者表達(dá)式時(shí)，可以將其存儲(chǔ)在索引中，這樣下次訪問時(shí)，該值已經(jīng)計(jì)算出來了，可以加快查詢速度

b、函數(shù)索引既可以使用B-Tree索引，也可以使用位圖索引；當(dāng)函數(shù)結(jié)果不確定時(shí)采用B樹索引，結(jié)果是固定的某幾個(gè)值時(shí)使用位圖索引

c、函數(shù)索引中可以使用len、trim、substr、upper（每行返回獨(dú)立結(jié)果），不能使用如sum、max、min、avg等

d、函數(shù)索引有一點(diǎn)要特別注意，在使用函數(shù)索引的時(shí)候，SQL語句中的對(duì)應(yīng)表達(dá)式必須與創(chuàng)建函數(shù)索引的表達(dá)式完全一致（當(dāng)然，空格、關(guān)鍵字大小寫的可以忽略），如果不是完全一致，則也利用不上函數(shù)索引。

創(chuàng)建函數(shù)索引

--創(chuàng)建一張測(cè)試表tb_test2，同時(shí)插入相應(yīng)的數(shù)據(jù)
SEIANG@seiang11g>create table tb_test2 as select * from dba_objects where owner in ('SYS','BI','SCOTT','PUBLIC','SYSTEM');

Table created.

SEIANG@seiang11g>
SEIANG@seiang11g>select owner,count(*) from tb_test2 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                              34002
SYSTEM                                618
SCOTT                                  25
BI                                      8
SYS                                 37803

SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from tb_test2;

  COUNT(*)
----------
     72456

--為owner列創(chuàng)建普通的B-Tree索引
SEIANG@seiang11g>create index idx_test2_owner on tb_test2(owner);

Index created.


--利用索引列，針對(duì)列值為BI，進(jìn)行普通查詢；與預(yù)想一樣，這里用到了索引掃描
SEIANG@seiang11g>select * from tb_test2 where owner='BI';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1141247240

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     8 |  1656 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST2        |     8 |  1656 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2_OWNER |     8 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='BI')

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


Statistics
----------------------------------------------------------
         23  recursive calls
          0  db block gets
         99  consistent gets
          1  physical reads
          0  redo size
       2238  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed


--清空buffer_cache緩沖區(qū)，避免影響后續(xù)操作對(duì)于物理讀的觀察。
SEIANG@seiang11g>alter system flush buffer_cache;

System altered.

--使用UPPER函數(shù)進(jìn)行條件過濾，并觀察執(zhí)行計(jì)劃，通過執(zhí)行計(jì)劃，可以明顯看出，未使用索引掃描，進(jìn)而導(dǎo)致大量的物理讀操作。
SEIANG@seiang11g>select * from tb_test2 where upper(owner)='BI';

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2703936182

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    12 |  2484 |   290   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TB_TEST2 |    12 |  2484 |   290   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("OWNER")='BI')

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


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1106  consistent gets
       1039  physical reads
          0  redo size
       1854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通過上面的示例可以看到，即使條件列建立了索引，當(dāng)索引列上使用函數(shù)進(jìn)行條件匹配，執(zhí)行計(jì)劃將不會(huì)選擇索引掃描。

--在索引列上建立函數(shù)索引
SEIANG@seiang11g>create index func_idx_test2_owner on tb_test2(UPPER(owner));

Index created.

--查看并驗(yàn)證建立的函數(shù)索引
需要注意的，由于此索引是基于函數(shù)建立的，因此columns一列無法顯示真正的列名，可以通過user_ind_expressions視圖查看

SEIANG@seiang11g>select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS
  2  from user_ind_columns a,user_indexes b
  3  where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='TB_TEST2';

INDEX_NAME                     INDEX_TYPE                TABLE_NAME                     COLUMN_NAME          STATUS
------------------------------ ------------------------- ------------------------------ -------------------- --------
IDX_TEST2_OWNER                NORMAL                    TB_TEST2                       OWNER                VALID
FUNC_IDX_TEST2_OWNER           FUNCTION-BASED NORMAL     TB_TEST2                       SYS_NC00016$         VALID

SEIANG@seiang11g>select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER';

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION              COLUMN_POSITION
------------------------------ ------------------------------ ------------------------------ ---------------
FUNC_IDX_TEST2_OWNER           TB_TEST2                       UPPER("OWNER")                               1

--再次使用UPPER函數(shù)進(jìn)行條件查詢，此時(shí)執(zhí)行計(jì)劃使用索引掃描，進(jìn)而物理讀明顯降低。
SEIANG@seiang11g>alter system flush buffer_cache;

System altered.
SEIANG@seiang11g>select * from tb_test2 where upper(owner)='BI';

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617808431

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     8 |  1792 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST2             |     8 |  1792 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FUNC_IDX_TEST2_OWNER |     8 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("OWNER")='BI')

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


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         74  consistent gets
        296  physical reads
          0  redo size
       1854  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

通過上面的示例可以看到，由于建立了函數(shù)索引，執(zhí)行計(jì)劃重新選擇了索引掃描，物理讀（physical reads）明顯降低。

四、索引的常見操作

4.1創(chuàng)建索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，創(chuàng)建位圖索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在數(shù)據(jù)塊中空閑空間
[STORAGE (INITIAL n2)]
[NOLOGGING]                                --表示創(chuàng)建和重建索引時(shí)允許對(duì)表做DML操作，默認(rèn)情況下不應(yīng)該使用
[NOLINE]
[NOSORT];                                   --表示創(chuàng)建索引時(shí)不進(jìn)行排序，默認(rèn)不適用，如果數(shù)據(jù)已經(jīng)是按照該索引順序排列的可以使用

4.2修改索引

（1）重命名索引

alter index [index_name] rename to bitmap_index;

（2）改變索引

alter index [index_name] storage(next 400K maxextents 100);

索引創(chuàng)建后，感覺不合理，也可以對(duì)其參數(shù)進(jìn)行修改。詳情查看相關(guān)文檔

（3）調(diào)整索引的空間

--新增加空間

alter index [index_name] allocate extent (size 200K datafile '/disk6/index01.dbf');

--釋放空間

alter index [index_name] deallocate unused;

索引在使用的過程中可能會(huì)出現(xiàn)空間不足或空間浪費(fèi)的情況，這個(gè)時(shí)候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關(guān)于空間的新增oracle可以自動(dòng)幫助，如果了解數(shù)據(jù)庫的情況下手動(dòng)增加可以提高性能。

（4）重新創(chuàng)建索引

索引是由oracle自動(dòng)完成，當(dāng)我們對(duì)數(shù)據(jù)庫頻繁的操作時(shí)，索引也會(huì)跟著進(jìn)行修改，當(dāng)我們?cè)跀?shù)據(jù)庫中刪除一條記錄時(shí)，對(duì)應(yīng)的索引中并沒有把相應(yīng)的索引只是做一個(gè)刪除標(biāo)記，但它依然占據(jù)著空間。除非一個(gè)塊中所有的標(biāo)記全被刪除的時(shí)，整個(gè)塊的空間才會(huì)被釋放。這樣時(shí)間久了，索引的性能就會(huì)下降。這個(gè)時(shí)候可以重新建立一個(gè)干凈的索引來提高效率。

alter index [index_name] rebuild tablespace [tablespace_name];

通過上面的命令就可以重現(xiàn)建立一個(gè)索引，oracle重建立索引的過程：

1、鎖表，鎖表之后其他人就不能對(duì)表做任何操作。

2、創(chuàng)建新的（干凈的）臨時(shí)索引。

3、把老的索引刪除掉

4、把新的索引重新命名為老索引的名字

5、對(duì)表進(jìn)行解鎖。

（5）移動(dòng)索引

其實(shí)，我們移動(dòng)索引到其它表空間也同樣使用上面的命令，在指定表空間時(shí)指定不同的表空間。新的索引創(chuàng)建在別位置，把老的干掉，就相當(dāng)于移動(dòng)了。

alter index [index_name] rebuild tablespace [tablespace_name];

（6）在線重新創(chuàng)建索引：

上面介紹，在創(chuàng)建索引的時(shí)候，表是被鎖定，不能被使用。對(duì)于一個(gè)大表，重新創(chuàng)建索引所需要的時(shí)間較長(zhǎng)，為了滿足用戶對(duì)表操作的需求，就產(chǎn)生的這種在線重新創(chuàng)建索引。

alter index [index_name]  rebuild  online;

創(chuàng)建過程：

1、鎖住表

2、創(chuàng)建立臨時(shí)的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中數(shù)據(jù)；on-gong DML也就是用戶所做的一些增刪改的操作。

3、對(duì)表進(jìn)行解鎖

4、從老的索引創(chuàng)建一個(gè)新的索引。

5、IOT表里存放的是on-going DML信息,IOT表的內(nèi)容與新創(chuàng)建的索引合并。

6、鎖住表

7、再次將IOT表的內(nèi)容更新到新索引中，把老的索引干掉。

8、把新的索引重新命名為老索引的名字

9、對(duì)表進(jìn)行解鎖

（7）合并索引

表使用一段時(shí)間后在索引中會(huì)產(chǎn)生碎片，此時(shí)索引效率會(huì)降低，可以選擇重建索引或者合并索引,合并索引方式更好些，無需額外存儲(chǔ)空間，代價(jià)較）

如上圖，在很多索引中有剩余的空間，可以通過一個(gè)命令把剩余空間整合到一起。

alter index [index_name] coalesce;

（8）查看索引

select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS from user_ind_columns a,user_indexes b where a.INDEX_NAME=b.INDEX_NAME and a.TABLE_NAME='XXX';

--查看函數(shù)索引的詳細(xì)定義

select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER';

（9）分析索引

檢查所引的有效果，前面介紹，索引用的時(shí)間久了會(huì)產(chǎn)生大量的碎片、垃圾信息與浪費(fèi)的剩余空間了?？梢酝ㄟ^重新創(chuàng)建索引來提高所引的性能。

可以通過一條命令來完成分析索引，分析的結(jié)果會(huì)存放在在index_stats表中。

SEIANG@seiang11g>select count(*) from index_stats;

  COUNT(*)
----------
         0

--刪除200行數(shù)據(jù)
SEIANG@seiang11g>delete tb_test1 where id>800;

200 rows deleted.

--進(jìn)行索引分析
SEIANG@seiang11g>analyze index IDX_TEST1_ID validate structure;

Index analyzed.

SEIANG@seiang11g>select count(*) from index_stats;

  COUNT(*)
----------
         1

SEIANG@seiang11g>
SEIANG@seiang11g>select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

    HEIGHT NAME                              LF_ROWS    LF_BLKS DEL_LF_ROWS
---------- ------------------------------ ---------- ---------- -----------
         2 IDX_TEST1_ID                         1000          3         200

說明：

（HEIGHT）這個(gè)所引高度是2，（NAME）索引名為IDX_TEST1_ID，（LF_ROWS）所引表有1000行數(shù)據(jù)，（LF_BLKS）占用3個(gè)塊，（DEL_LF_ROWS）刪除200條記錄。

　　這里也驗(yàn)證了前面所說的一個(gè)問題，刪除的200條數(shù)據(jù)只是標(biāo)記為刪除，因?yàn)榭偟臄?shù)據(jù)條數(shù)依然為1000條，占用3個(gè)塊，那么每個(gè)塊大于333條記錄，只有刪除的數(shù)據(jù)大于333條記錄，這時(shí)一個(gè)塊被清空，總的數(shù)據(jù)條數(shù)才會(huì)減少。

（10）監(jiān)控索引

無論是投產(chǎn)之后還是開發(fā)測(cè)試中，我們都在數(shù)據(jù)表中加入了一些索引。通常我們是不能實(shí)時(shí)監(jiān)視每條語句的執(zhí)行計(jì)劃，那么在oracle中，可以借助monitoring usage關(guān)鍵字和v$object_usage視圖實(shí)現(xiàn)這個(gè)功能，發(fā)現(xiàn)一些不常用的索引，定位優(yōu)化目標(biāo)。

--啟用監(jiān)控功能并且收集監(jiān)控結(jié)果。
SEIANG@seiang11g>alter index IDX_TEST1_ID monitoring usage;

Index altered.

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       YES NO  11/09/2017 14:18:02

SEIANG@seiang11g>select * from tb_test1 where id<10;

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2241831008

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     9 |   324 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_TEST1     |     9 |   324 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<10)

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


Statistics
----------------------------------------------------------
         41  recursive calls
          3  db block gets
         58  consistent gets
          5  physical reads
        548  redo size
        859  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          9  rows processed

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       YES YES 11/09/2017 14:18:02


--關(guān)閉索引監(jiān)控功能
SEIANG@seiang11g>alter index IDX_TEST1_ID nomonitoring usage;

Index altered.

SEIANG@seiang11g>select * from v$object_usage where index_name='IDX_TEST1_ID';

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IDX_TEST1_ID                   TB_TEST1                       NO  YES 11/09/2017 14:18:02 11/09/2017 14:21:34

（11）刪除索引

drop index [index_name];

擴(kuò)展補(bǔ)充：常用的oracle索引視圖

較為重要的oracle索引視圖如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions

說明：
dba_indexes與user_indexes視圖，主要涵蓋了索引的參數(shù)、狀態(tài)以及關(guān)聯(lián)的表信息，但不包含具體的列信息。
dba_ind_columns與user_ind_columns視圖，主要涉及具體的索引列的信息。
dba_expressions與user_expressions視圖，主要針對(duì)函數(shù)索引，可以查看具體的函數(shù)信息。

五、索引建立原則總結(jié)

1、權(quán)衡索引個(gè)數(shù)與DML之間關(guān)系，DML也就是插入、刪除數(shù)據(jù)操作。這里需要權(quán)衡一個(gè)問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會(huì)影響插入、刪除數(shù)據(jù)的速度，因?yàn)槲覀冃薷牡谋頂?shù)據(jù)，索引也要跟著修改。這里需要權(quán)衡我們的操作是查詢多還是修改多。

2、如果有兩個(gè)或者以上的索引，其中有一個(gè)唯一性索引，而其他是非唯一，這種情況下oracle將使用唯一性索引而完全忽略非唯一性索引

3、把索引與對(duì)應(yīng)的表放在不同的表空間。當(dāng)讀取一個(gè)表的時(shí)候，表與索引是同時(shí)進(jìn)行的。如果表與索引和在一個(gè)表空間里就會(huì)產(chǎn)生資源競(jìng)爭(zhēng)，放在兩個(gè)表這空就可并行執(zhí)行。這樣做的目的主要是分散物理IO

4、最好使用一樣大小是塊。Oracle默認(rèn)五塊，讀一次I/O，如果你定義6個(gè)塊或10個(gè)塊都需要讀取兩次I/O。最好是5的整數(shù)倍更能提高效率。

5、如果一個(gè)表很大，建立索引的時(shí)間很長(zhǎng)，因?yàn)榻⑺饕矔?huì)產(chǎn)生大量的redo信息，所以在創(chuàng)建索引時(shí)可以設(shè)置不產(chǎn)生或少產(chǎn)生redo信息。只要表數(shù)據(jù)存在，索引失敗了大不了再建，所以可以不需要產(chǎn)生redo信息。

6、建索引的時(shí)候應(yīng)該根據(jù)具體的業(yè)務(wù)SQL來創(chuàng)建，特別是where條件，還有where條件的順序，盡量將過濾大范圍的放在后面，因?yàn)?/span>SQL執(zhí)行是從后往前的。（小李飛菜刀）

7、至少要包含組合索引的第一列（即如果索引建立在多個(gè)列上，只有它的第一個(gè)列被where子句引用時(shí)，優(yōu)化器才會(huì)使用該索引）

8、小表不要簡(jiǎn)歷索引

9、對(duì)于基數(shù)大的列適合建立B樹索引，對(duì)于基數(shù)小的列適合簡(jiǎn)歷位圖索引

10、列中有很多空值，但經(jīng)常查詢?cè)摿猩戏强沼涗洉r(shí)應(yīng)該建立索引

11、經(jīng)常進(jìn)行連接查詢的列應(yīng)該創(chuàng)建索引

12、使用create index時(shí)要將最常查詢的列放在最前面

13、限制表中索引的數(shù)量（創(chuàng)建索引耗費(fèi)時(shí)間，并且隨數(shù)據(jù)量的增大而增大；索引會(huì)占用物理空間；當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，降低了數(shù)據(jù)的維護(hù)速度）

六、SQL語句不走索引的情況

1、通配符在搜索詞首出現(xiàn)時(shí)，oracle不能使用索引；

--我們?cè)?/span>name上創(chuàng)建索引；
create index index_name on student('name');

--下面的方式oracle不適用name索引
select * from student where name like '%wjq%';

--如果通配符出現(xiàn)在字符串的其他位置時(shí)，優(yōu)化器能夠利用索引；如下：
select * from student where name like 'wjq%';

2、不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not會(huì)停止使用索引，而采用全表掃描）

select * from student where not (score=100);
select * from student where score <> 100;

--替換為
select * from student where score>100 or score <100

3、索引上使用空值比較將停止使用索引；

select * from student where score is not null;

關(guān)于更多SQL語句不走索引的情況請(qǐng)參考博客：【Oracle index】SQL語句無法走索引的一些情況分析及語句改寫思路 ：http://blog.chinaunix.net/uid-7655508-id-3637972.html

參考鏈接：

http://www.cnblogs.com/wishyouhappy/p/3681771.html

http://www.cnblogs.com/fnng/archive/2012/10/10/2719221.html

 

作者：SEian.G（苦練七十二變，笑對(duì)八十一難）