Oracle中B-Tree、Bitmap和函式索引使用案例總結
目錄
一、索引簡介
1、索引是一個獨立的資料庫物件，和資料表table一樣。在Oracle中，資料庫物件object都是透過段segment結構表示。我們在資料字典dba_segment中，可以使用索引的名稱搜尋出與segment_name相等的字典專案。
2、索引是資料庫物件之一，用於加快資料的檢索，類似於書籍的索引。在資料庫中索引可以減少資料庫程式查詢結果時需要讀取的資料量，類似於在書籍中我們利用索引可以不用翻閱整本書即可找到想要的資訊。
3、索引是建立在表上的可選物件；索引的關鍵在於透過一組排序後的索引鍵來取代預設的全表掃描檢索方式，從而提高檢索效率
4、索引在邏輯上和物理上都與相關的表和資料無關，當建立或者刪除一個索引時，不會影響基本的表；
5、索引一旦建立，在表上進行DML操作時（例如在執行插入、修改或者刪除相關操作時），oracle會自動管理索引，索引刪除，不會對錶產生影響
6、索引對使用者是透明的，無論表上是否有索引，sql語句的用法不變
7、Oracle資料庫會為表的主鍵和包含唯一約束的列自動建立索引。
8、資料表和索引是可以分開進行儲存的。通常，從效能角度考慮我們常將兩者放置在不同的Tablespace中，這樣做的目的主要是為了分散物理IO。
二、索引原理
1、若沒有索引，搜尋某個記錄時（例如查詢name='wjq'）需要搜尋所有的記錄，因為不能保證只有一個wjq，必須全部搜尋一遍
2、若在name上建立索引，oracle會對全表進行一次搜尋，將每條記錄的name值按照升序排列，然後構建索引條目（name和rowid），儲存到索引段中，查詢name為wjq時即可直接查詢對應地方
3、建立了索引並不一定就會使用，oracle自動統計表的資訊後，決定是否使用索引，表中資料很少時使用全表掃描速度已經很快，沒有必要使用索引
三、索引的分類
3.1邏輯分類
|
single column or concatenated |
對一列或多列建索引 |
|
unique or nonunique |
唯一的和非唯一的索引，也就是對某一列或幾列的鍵值（key）是否是唯一的 |
|
Function-based |
基於函式的索引，當執行某些函式時需要對其進行計算，可以將某些函式的計算結果事先儲存並加以索引，提高效率 |
|
Doman |
索引資料庫以外的資料，使用相對較少 |
3.2物理分類
|
B-Tree |
B-Tree索引也是我們傳統上常見所理解的索引，它又可以分為正常索引和反向鍵索引（資料列中的資料是反向儲存的）。 |
|
Bitmap |
點陣圖索引 |
下面重點講解B-Tree索引、Bitmap索引和函式索引。
1、B-Tree索引
a、B-Tree索引是Oracle中最常用的索引；B樹索引就是一顆二叉樹（平衡樹），左右兩個分支相對平衡；葉子節點（雙向連結串列）包含索引列和指向表中每個匹配行的ROWID值。
b、所有葉子節點具有相同的深度，因而不管查詢條件怎樣，查詢速度基本相同
c、能夠適應精確查詢、模糊查詢和比較查詢
說明：
Root為根節點，branch為分支節點，leaf到最下面一層稱為葉子節點。每個節點表示一層，當查詢某一資料時先讀根節點，再讀支節點，最後找到葉子節點。葉子節點會存放index entry（索引入口），每個索引入口對應一條記錄。
Index entry 的組成部分：
Indexentry entry header 存放一些控制資訊。
Key column length 某一key的長度
Key column value 某一個key 的值
ROWID 指標，具體指向於某一個資料
建立索引
--建立一張測試表，並插入1000行資料
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.
--建立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
索引分離於表，作為一個單獨的個體存在，除了可以根據單個欄位建立索引，也可以根據多列建立索引。Oracle要求建立索引最多不可超過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
這裡需要理解：
編寫一本書，只有章節頁面定好之後再設定目錄；資料庫索引也是一樣，只有先插入好資料，再建立索引。那麼我們後續對資料庫的內容進行插入、刪除，索引也需要隨之變化。但索引的修改是由oracle自動完成的。
上面這張圖能更加清晰的描述索引的結構。
根節點記錄0至50條資料的位置，分支節點進行拆分記錄0至10……42至50，葉子節點記錄每第資料的長度和值，並由指標指向具體的資料。最後一層的葉子節是雙向連結，它們是被有序的連結起來，這樣才能快速鎖定一個資料範圍。
例如：
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條資料，再找到第32條資料，這樣就能快速的鎖定一個查詢的範圍，如果每條資料都要從根節點開始查詢的話，那麼效率就會非常低下。
2、Bitmap索引
a、建立點陣圖索引時，oracle會掃描整張表，併為索引列的每個取值建立一個點陣圖（點陣圖中，對錶中每一行使用一位（bit，0或者1）來標識該行是否包含該點陣圖的索引列的取值，如果為1，表示對應的rowid所在的記錄包含該點陣圖索引列值），最後透過點陣圖索引中的對映函式完成位到行的ROWID的轉換
b、點陣圖索引主要針對大量相同值的列而建立。拿全國居民登入表來說，假設有四個欄位：姓名、性別、年齡、和身份證號，年齡和性別兩個欄位會產生許多相同的值，性別只有男女兩種取值，年齡，1到120（假設最大年齡120歲）個值。那麼不管一張表有幾億條記錄，但根據性別欄位來區分的話，只有兩種取值（男、女）。那麼點陣圖索引就是根據欄位的這個特性所建立的一種索引。
c、對於基數小的列適合簡歷點陣圖索引（例如性別等）
從上圖，我們可以看出，一個葉子節點（用不同顏色標識）代表一個key, start rowid和end rowid規定這種型別的檢索範圍，一個葉子節點標記一個唯一的bitmap值。因為一個數值型別對應一個節點，當進行查詢時，點陣圖索引透過不同點陣圖取值直接的位運算（與或），來獲取到結果集合向量（計算出的結果）。
舉例講解：
假設存在資料表T，有兩個資料列A和B，取值如下，我們看到A和B列中存在相同的資料。
對兩個資料列A、B分別建立點陣圖索引：idx_t_bita和idx_t_bitb。兩個索引對應的儲存邏輯結構如下：
Idx_t_bita索引結構，對應的是葉子節點：
Idx_t_bitb索引結構，對應的是葉子節點：
對查詢“select * from t where b=1 and (a=’L’ or a=’M’)”
分析：
點陣圖索引使用方面，和B*索引有很大的不同。B*索引的使用，通常是從根節點開始，經過不斷的分支節點比較到最近的符合條件葉子節點。透過葉子節點上的不斷scan操作，“掃描”出結果集合rowid。
而點陣圖索引的工作方式截然不同。透過不同點陣圖取值直接的位運算（與或），來獲取到結果集合向量（計算出的結果）。
針對例項SQL，可以拆分成如下的操作：
1、a=’L’ or a=’M’
a=L：向量：1010
a=M：向量：0001
or操作的結果，就是兩個向量的或操作：結果為1011。
2、結合b=1的向量
中間結果向量：1011
B=1：向量：1001
and操作的結果，1001。翻譯過來就是第一和第四行是查詢結果。
3、獲取到結果rowid
目前知道了起始rowid和終止rowid，以及第一行和第四行為操作結果。可以透過試算的方法獲取到結果集合rowid。
點陣圖索引的特點：
（1）Bitmap索引的儲存空間節省
（2）Bitmap索引建立的速度快
（3）Bitmap索引允許鍵值為空
（4）Bitmap索引對錶記錄的高效訪問
建立Bitmap索引
--接著上面B-Tree索引所建立的表tb_test1為例，基於該表來建立Bitmap索引
對於上面表來說sex（性別）只有兩種值，最適合用來建立點陣圖所引
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上的所有建立的索引及型別
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、函式索引
a、當經常要訪問一些函式或者表示式時，可以將其儲存在索引中，這樣下次訪問時，該值已經計算出來了，可以加快查詢速度
b、函式索引既可以使用B-Tree索引，也可以使用點陣圖索引；當函式結果不確定時採用B樹索引，結果是固定的某幾個值時使用點陣圖索引
c、函式索引中可以使用len、trim、substr、upper（每行返回獨立結果），不能使用如sum、max、min、avg等
d、函式索引有一點要特別注意，在使用函式索引的時候，SQL語句中的對應表示式必須與建立函式索引的表示式完全一致（當然，空格、關鍵字大小寫的可以忽略），如果不是完全一致，則也利用不上函式索引。
建立函式索引
--建立一張測試表tb_test2，同時插入相應的資料
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列建立普通的B-Tree索引
SEIANG@seiang11g>create index idx_test2_owner on tb_test2(owner);
Index created.
--利用索引列，針對列值為BI，進行普通查詢；與預想一樣，這裡用到了索引掃描
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緩衝區，避免影響後續操作對於物理讀的觀察。
SEIANG@seiang11g>alter system flush buffer_cache;
System altered.
--使用UPPER函式進行條件過濾，並觀察執行計劃，透過執行計劃，可以明顯看出，未使用索引掃描，進而導致大量的物理讀操作。
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
透過上面的示例可以看到，即使條件列建立了索引，當索引列上使用函式進行條件匹配，執行計劃將不會選擇索引掃描。
--在索引列上建立函式索引
SEIANG@seiang11g>create index func_idx_test2_owner on tb_test2(UPPER(owner));
Index created.
--檢視並驗證建立的函式索引
需要注意的，由於此索引是基於函式建立的，因此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函式進行條件查詢，此時執行計劃使用索引掃描，進而物理讀明顯降低。
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
透過上面的示例可以看到，由於建立了函式索引，執行計劃重新選擇了索引掃描，物理讀（physical reads）明顯降低。
四、索引的常見操作
4.1建立索引
CREATE
[UNIQUE] | [BITMAP] INDEX index_name --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2 --bitmap，建立點陣圖索引
[ASC|DESC],…]
| [express])
[TABLESPACE tablespace_name]
[PCTFREE n1] --指定索引在資料塊中空閒空間
[STORAGE
(INITIAL n2)]
[NOLOGGING] --表示建立和重建索引時允許對錶做DML操作，預設情況下不應該使用
[NOLINE]
[NOSORT]; --表示建立索引時不進行排序，預設不適用，如果資料已經是按照該索引順序排列的可以使用
4.2修改索引
（1）重新命名索引
alter index [index_name] rename to bitmap_index;
（2）改變索引
alter index [index_name] storage(next 400K maxextents 100);
索引建立後，感覺不合理，也可以對其引數進行修改。詳情檢視相關文件
（3）調整索引的空間
--新增加空間
alter index [index_name] allocate extent (size 200K datafile '/disk6/index01.dbf');
--釋放空間
alter index [index_name] deallocate unused;
索引在使用的過程中可能會出現空間不足或空間浪費的情況，這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關於空間的新增oracle可以自動幫助，如果瞭解資料庫的情況下手動增加可以提高效能。
（4）重新建立索引
索引是由oracle自動完成，當我們對資料庫頻繁的操作時，索引也會跟著進行修改，當我們在資料庫中刪除一條記錄時，對應的索引中並沒有把相應的索引只是做一個刪除標記，但它依然佔據著空間。除非一個塊中所有的標記全被刪除的時，整個塊的空間才會被釋放。這樣時間久了，索引的效能就會下降。這個時候可以重新建立一個乾淨的索引來提高效率。
alter index [index_name] rebuild tablespace [tablespace_name];
透過上面的命令就可以重現建立一個索引，oracle重建立索引的過程：
1、鎖表，鎖表之後其他人就不能對錶做任何操作。
2、建立新的（乾淨的）臨時索引。
3、把老的索引刪除掉
4、把新的索引重新命名為老索引的名字
5、對錶進行解鎖。
（5）移動索引
其實，我們移動索引到其它表空間也同樣使用上面的命令，在指定表空間時指定不同的表空間。新的索引建立在別位置，把老的幹掉，就相當於移動了。
alter index [index_name] rebuild tablespace [tablespace_name];
（6）線上重新建立索引：
上面介紹，在建立索引的時候，表是被鎖定，不能被使用。對於一個大表，重新建立索引所需要的時間較長，為了滿足使用者對錶操作的需求，就產生的這種線上重新建立索引。
alter index [index_name] rebuild online;
建立過程：
1、鎖住表
2、建立立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中資料；on-gong DML也就是使用者所做的一些增刪改的操作。
3、對錶進行解鎖
4、從老的索引建立一個新的索引。
5、IOT表裡存放的是on-going DML資訊,IOT表的內容與新建立的索引合併。
6、鎖住表
7、再次將IOT表的內容更新到新索引中，把老的索引幹掉。
8、把新的索引重新命名為老索引的名字
9、對錶進行解鎖
（7）合併索引
表使用一段時間後在索引中會產生碎片，此時索引效率會降低，可以選擇重建索引或者合併索引,合併索引方式更好些，無需額外儲存空間，代價較）
如上圖，在很多索引中有剩餘的空間，可以透過一個命令把剩餘空間整合到一起。
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';
--檢視函式索引的詳細定義
select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER';
（9）分析索引
檢查所引的有效果，前面介紹，索引用的時間久了會產生大量的碎片、垃圾資訊與浪費的剩餘空間了。可以透過重新建立索引來提高所引的效能。
可以透過一條命令來完成分析索引，分析的結果會存放在在index_stats表中。
SEIANG@seiang11g>select count(*) from index_stats;
COUNT(*)
----------
0
--刪除200行資料
SEIANG@seiang11g>delete tb_test1 where id>800;
200 rows deleted.
--進行索引分析
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）這個所引高度是2，（NAME）索引名為IDX_TEST1_ID，（LF_ROWS）所引表有1000行資料，（LF_BLKS）佔用3個塊，（DEL_LF_ROWS）刪除200條記錄。
這裡也驗證了前面所說的一個問題，刪除的200條資料只是標記為刪除，因為總的資料條數依然為1000條，佔用3個塊，那麼每個塊大於333條記錄，只有刪除的資料大於333條記錄，這時一個塊被清空，總的資料條數才會減少。
（10）監控索引
無論是投產之後還是開發測試中，我們都在資料表中加入了一些索引。通常我們是不能實時監視每條語句的執行計劃，那麼在oracle中，可以藉助monitoring usage關鍵字和v$object_usage檢視實現這個功能，發現一些不常用的索引，定位最佳化目標。
--啟用監控功能並且收集監控結果。
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
--關閉索引監控功能
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];
擴充套件補充：常用的oracle索引檢視
較為重要的oracle索引檢視如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions
說明：
dba_indexes與user_indexes檢視，主要涵蓋了索引的引數、狀態以及關聯的表資訊，但不包含具體的列資訊。
dba_ind_columns與user_ind_columns檢視，主要涉及具體的索引列的資訊。
dba_expressions與user_expressions檢視，主要針對函式索引，可以檢視具體的函式資訊。
五、索引建立原則總結
1、權衡索引個數與DML之間關係，DML也就是插入、刪除資料操作。這裡需要權衡一個問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會影響插入、刪除資料的速度，因為我們修改的表資料，索引也要跟著修改。這裡需要權衡我們的操作是查詢多還是修改多。
2、如果有兩個或者以上的索引，其中有一個唯一性索引，而其他是非唯一，這種情況下oracle將使用唯一性索引而完全忽略非唯一性索引
3、把索引與對應的表放在不同的表空間。當讀取一個表的時候，表與索引是同時進行的。如果表與索引和在一個表空間裡就會產生資源競爭，放在兩個表這空就可並行執行。這樣做的目的主要是分散物理IO
4、最好使用一樣大小是塊。Oracle預設五塊，讀一次I/O，如果你定義6個塊或10個塊都需要讀取兩次I/O。最好是5的整數倍更能提高效率。
5、如果一個表很大，建立索引的時間很長，因為建立索引也會產生大量的redo資訊，所以在建立索引時可以設定不產生或少產生redo資訊。只要表資料存在，索引失敗了大不了再建，所以可以不需要產生redo資訊。
6、建索引的時候應該根據具體的業務SQL來建立，特別是where條件，還有where條件的順序，儘量將過濾大範圍的放在後面，因為SQL執行是從後往前的。（小李飛菜刀）
7、至少要包含組合索引的第一列（即如果索引建立在多個列上，只有它的第一個列被where子句引用時，最佳化器才會使用該索引）
8、小表不要簡歷索引
9、對於基數大的列適合建立B樹索引，對於基數小的列適合簡歷點陣圖索引
10、列中有很多空值，但經常查詢該列上非空記錄時應該建立索引
11、經常進行連線查詢的列應該建立索引
12、使用create index時要將最常查詢的列放在最前面
13、限制表中索引的數量（建立索引耗費時間，並且隨資料量的增大而增大；索引會佔用物理空間；當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，降低了資料的維護速度）
六、SQL語句不走索引的情況
1、萬用字元在搜尋詞首出現時，oracle不能使用索引；
--我們在name上建立索引；
create index index_name on student('name');
--下面的方式oracle不適用name索引
select * from student where name like '%wjq%';
--如果萬用字元出現在字串的其他位置時，最佳化器能夠利用索引；如下：
select * from student where name like 'wjq%';
2、不要在索引列上使用not，可以採用其他方式代替如下：（oracle碰到not會停止使用索引，而採用全表掃描）
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;
關於更多SQL語句不走索引的情況請參考部落格：【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（苦練七十二變，笑對八十一難）
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2147377/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分析函式使用總結Oracle函式
- ORACLE常用結構和函式使用方法總結Oracle函式
- oracle的B-tree索引結構分析Oracle索引
- Oracle中利用函式索引處理資料傾斜案例Oracle函式索引
- 【Bitmap Index】B-Tree索引與Bitmap點陣圖索引的鎖代價比較研究Index索引
- Oracle 函式總結Oracle函式
- oracle 與 mysql 中的函式總結OracleMySql函式
- Oracle 中 Over() 函式學習總結Oracle函式
- oracle 索引使用及索引失效總結Oracle索引
- Oracle優化案例-自定義函式索引（五）Oracle優化函式索引
- 【索引】Bitmap點陣圖索引與普通的B-Tree索引鎖的比較索引
- B-tree and Bitmap IndexIndex
- Oracle常用函式總結Oracle函式
- oracle函式索引Oracle函式索引
- C++中的函式指標和函式物件總結C++函式指標物件
- B-Tree索引與Bitmap點陣圖索引的鎖代價比較研究索引
- ORACLE 實用函式總結Oracle函式
- Oracle之函式索引Oracle函式索引
- caffe中各種cblas的函式使用總結函式
- Oracle資料庫索引使用及索引失效總結 轉Oracle資料庫索引
- Oracle分析函式七——分析函式案例Oracle函式
- 索引中使用函式索引函式
- MySQL Hash索引和B-Tree索引的區別MySql索引
- spark中的聚合函式總結Spark函式
- JS 中的函式 this 指向總結JS函式
- [轉]：bitmap索引和B*tree索引分析索引
- Emgucv使用中常用函式總結函式
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- 函式索引使用細節——自定義函式的索引化函式索引
- Clickhouse的bitmap函式函式
- Oracle 19c中基於函式的索引Oracle函式索引
- Oracle 中 replace函式和translate函式比較Oracle函式
- 【函式】Oracle中聚合函式rank()使用方法函式Oracle
- Oracle中的時間函式用法(to_date、to_char) (總結）Oracle函式
- oracle 點陣圖索引（bitmap index）Oracle索引Index
- sequence 和索引函式呼叫索引函式
- python 中 print 函式用法總結Python函式
- Oracle基於函式的索引Oracle函式索引