Oracle資料庫開發——瞭解索引
關於索引，有一種比喻，表資料是本大書，索引時這本大書的目錄。如果書只有幾頁，你完全可以一眼翻看完，那目錄就沒太大必要了。
因為看目錄的時間，幾乎可以讓你看完這本書。
索引是關聯式資料庫中的一種基本物件，它是表中資料與相應儲存位置的關係。利用索引可以加快資料的檢索速度，並實現對完整性的檢查。
確切的說，索引是建立在表列上的資料庫物件(書的目錄是基於書的內容嘛)。
當然，目錄對閱讀內容的使用者會有影響！
表上能否建立索引，建立什麼型別的索引，以及建立多少索引，會直接影響到應用的效能。如果沒有索引或索引建立的不正確，則會影響資料
查詢的速度，因為可能此時不得不進行全表掃描;如果索引，則會影響更新的速度，因為要花更多的時間來維護索引(內容變了，目錄也要變)。
建立索引的原則:
A.依據表的大小建立索引。一般來說，小表不必建索引，可以透過全表掃描的效能分析來判斷建立索引後是否改善了資料庫效能。
B.依據表和列的特徵建立索引。
在經常進行查詢的列上建立索引，可以提高搜尋的速度;
在經常進行連線查詢的列上建立索引，可以提高搜尋速度;
C.限制表中索引的數量。過多或過少的索引都會影響系統的效能
D.要合理安排符合索引總列的順序，將頻繁使用的列放在其他列的前面
Oracle資料庫中提供的索引型別:B-樹索引、點陣圖索引、基於函式的索引、反向值索引、域索引。
create table student
(s_num number(6)
,s_name varchar2(20)
,s_sex varchar2(1)
)
insert into student values (001,'Tom','F');
insert into student values (002,'Jane','M');
insert into student values (003,'James','F');
insert into student values (004,'Criss','F');
insert into student values (005,'Marry','M');
insert into student values (006,'Rose','M');
insert into student values (007,'Lin','F');
insert into student values (008,'Jack','F');
insert into student values (009,'Scott','F');
insert into student values (010,'Tomas','F');
又稱為平衡樹索引，是資料庫中最常用的一種索引。
這種索引的葉子節點儲存索引鍵值和指向索引行的ROWID。
create unique index student_idx on student(number) tablespace users;
B-樹索引可以用unique限定為唯一索引。
與B-樹索引不同，點陣圖索引不儲存ROWID值，也不儲存鍵值。它是一個索引鍵條目儲存指向多行的指標，即每個索引條目指向多行。
點陣圖索引適合索引基值數少，高度重複，且只讀的應用環境使用，所以適合於資料倉儲等環境。
create bitmap index student_bitmap_idx on student (s_sex) tablespace users;
點陣圖索引實際上是一個二維陣列，陣列的列由表記錄數決定，行數由索引列基值數決定。
舉個例子來說:
student表的sex欄位值只有 男，女 兩個基值。表中資料假設有10條。3男，7女
那麼我們可以想象下索引該怎麼保持值以迅速的區分我們需要的資料呢?
M F F F F M M M F F
0 1 1 1 1 0 1 0 1 1
F為男，M為女，我們在過濾條件中加入 sex = 'F' 希望在找到男生的相關資料記錄，那麼此時資料庫只要關注索引值為1的資料記錄即可。
SQL> explain plan for select *from student where s_sex = 'F';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039275447
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 |
| 1 | TABLE ACCESS BY INDEX ROWID | STUDENT | 7 | 189 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
|* 3 | BITMAP INDEX SINGLE VALUE | STUDENT_BITMAP_IDX | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S_SEX"='F')
Note
-----
- dynamic sampling used for this statement (level=2)
S_SEX='F'是過濾條件，走了STUDENT_BITMAP_IDX的索引，BITMAP INDEX SINGLE VALUE，這個VALUE是不是就是代表 F 的索引值呢?
基於函式的索引是在B-樹索引或點陣圖索引的基礎上，將一個函式計算得到的結果，作為索引值而建立的索引。
例如: 加快 select * from student where upper(s_name) like '%JA%' 查詢效率
--建立函式索引
SQL> create index student_fun_idx on student(upper(s_name)) tablespace users;
Index created
--檢視執行計劃驗證是否走函式索引
SQL> explain plan for select * from student where upper(s_name) like '%JA%';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2499371011
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 39 | 2
|* 2 | INDEX FULL SCAN | STUDENT_FUN_IDX | 1 | | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("S_NAME") IS NOT NULL AND UPPER("S_NAME") LIKE '%JA%')
Note
-----
- dynamic sampling used for this statement (level=2)
注意函式索引的寫法，WHERE條件 索引欄位使用方式 必須與 函式索引的建立方式 相同
即:
where upper(s_name) = 'XXX' 走索引，索引全掃描
where s_name = upper('XXX')或者 where s_name like upper('%JA%') 不走索引，全表掃描
驗證一下:
SQL> explain plan for select * from student where upper(s_name) = 'CRISS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2505413714
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 39 | 2
|* 2 | INDEX RANGE SCAN | STUDENT_FUN_IDX | 1 | | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("S_NAME")='CRISS')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
SQL> explain plan for select * from student where s_name = upper('Criss');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2356778634
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| STUDENT | 1 | 27 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("S_NAME"='CRISS')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected
反向鍵值索引可以避免不平衡的索引，使索引條目在索引中分佈的更加均勻。
可以直接建立 :
create index student_rev_idx on student(s_num) reverse;
或者用alter index 語句修改
alter index student_rev_idx rebuild noreverse;
alter index student_rev_idx rebuild reverse;
這裡有一個疑問:
什麼樣的表適合建立反向鍵值索引？或者說，什麼情況下，應該將普通的B-tree索引利用alter index idx_name rebuild reverse 子句修改成反向鍵值索引？
改變後，索引段中儲存的鍵值分佈會有什麼變化呢？
先了解一下反向鍵值的儲存形式及作用。
反向鍵值索引的儲存形式:
原值:12345
索引值:54321
反向鍵值索引的作用:
消除熱塊、使得B-Tree索引分佈更加平衡(防止樹的單邊增長嚴重)
舉個例子:
索引鍵原值: 12345
12346
12347
這個三條連續資料將儲存在同一個資料塊上！
反向鍵值索引值: 54321
64321
74321
三條資料將儲存在不同的資料塊上！
由此，可以解決上述問題，B-tree 鍵值太靠近，使得樹單邊增長，要不斷調整才能達到balance的表適合建立反向鍵值索引。
將原連續鍵值reverse後，形成多個不同鍵值分佈在不同的資料塊上。
===========================================================================================
索引的修改
索引的修改包括 合併索引、重構索引、重新命名索引。
在資料庫的使用過程中，表資料的不斷更新，就會在索引中產生越來越多的儲存碎片，這會使得索引失效。
這種情況下可以使用合併所引或重構索引來清除這些儲存碎片。
索引合併:
alter index index_name coalesce deallocate unused;
coalesce 表示合併索引，deallocate unused 表示合併後釋放多餘的儲存空間
索引重構:
alter index index_name rebuild;
其實質上是先刪除原來的索引，然後再根據原來的索引結構重建。
重新命名索引:
alter index index_name rename to new_name;
============================================================================================
索引的監視
開發中，我們可以透過開啟索引監視，來了解索引的使用情況
step 1 . 開啟我們要監視的索引
SQL> alter index student_rev_idx monitoring usage;
Index altered
step 2 . 檢視動態效能檢視 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
INDEX_NAME USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX NO 05/15/2014 10:32:02
當前索引 STUDENT_REV_IDX 的狀態為未使用
step 3 . 執行走索引的SQL語句
SQL> select * from student where s_num = '1004';
S_NUM S_NAME S_SEX
------- -------------------- -----
1004 Criss F
step 4 . 再次檢視動態效能檢視 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
INDEX_NAME USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX YES 05/15/2014 10:32:02
此時，索引STUDENT_REV_IDX狀態為使用過
step 4 . 當不需要監視索引時，關閉監視
SQL> alter index student_rev_idx nomonitoring usage;
Index altered
因為看目錄的時間，幾乎可以讓你看完這本書。
索引是關聯式資料庫中的一種基本物件，它是表中資料與相應儲存位置的關係。利用索引可以加快資料的檢索速度，並實現對完整性的檢查。
確切的說，索引是建立在表列上的資料庫物件(書的目錄是基於書的內容嘛)。
當然，目錄對閱讀內容的使用者會有影響！
表上能否建立索引，建立什麼型別的索引，以及建立多少索引，會直接影響到應用的效能。如果沒有索引或索引建立的不正確，則會影響資料
查詢的速度，因為可能此時不得不進行全表掃描;如果索引，則會影響更新的速度，因為要花更多的時間來維護索引(內容變了，目錄也要變)。
建立索引的原則:
A.依據表的大小建立索引。一般來說，小表不必建索引，可以透過全表掃描的效能分析來判斷建立索引後是否改善了資料庫效能。
B.依據表和列的特徵建立索引。
在經常進行查詢的列上建立索引，可以提高搜尋的速度;
在經常進行連線查詢的列上建立索引，可以提高搜尋速度;
C.限制表中索引的數量。過多或過少的索引都會影響系統的效能
D.要合理安排符合索引總列的順序，將頻繁使用的列放在其他列的前面
Oracle資料庫中提供的索引型別:B-樹索引、點陣圖索引、基於函式的索引、反向值索引、域索引。
create table student
(s_num number(6)
,s_name varchar2(20)
,s_sex varchar2(1)
)
insert into student values (001,'Tom','F');
insert into student values (002,'Jane','M');
insert into student values (003,'James','F');
insert into student values (004,'Criss','F');
insert into student values (005,'Marry','M');
insert into student values (006,'Rose','M');
insert into student values (007,'Lin','F');
insert into student values (008,'Jack','F');
insert into student values (009,'Scott','F');
insert into student values (010,'Tomas','F');
又稱為平衡樹索引，是資料庫中最常用的一種索引。
這種索引的葉子節點儲存索引鍵值和指向索引行的ROWID。
create unique index student_idx on student(number) tablespace users;
B-樹索引可以用unique限定為唯一索引。
與B-樹索引不同，點陣圖索引不儲存ROWID值，也不儲存鍵值。它是一個索引鍵條目儲存指向多行的指標，即每個索引條目指向多行。
點陣圖索引適合索引基值數少，高度重複，且只讀的應用環境使用，所以適合於資料倉儲等環境。
create bitmap index student_bitmap_idx on student (s_sex) tablespace users;
點陣圖索引實際上是一個二維陣列，陣列的列由表記錄數決定，行數由索引列基值數決定。
舉個例子來說:
student表的sex欄位值只有 男，女 兩個基值。表中資料假設有10條。3男，7女
那麼我們可以想象下索引該怎麼保持值以迅速的區分我們需要的資料呢?
M F F F F M M M F F
0 1 1 1 1 0 1 0 1 1
F為男，M為女，我們在過濾條件中加入 sex = 'F' 希望在找到男生的相關資料記錄，那麼此時資料庫只要關注索引值為1的資料記錄即可。
SQL> explain plan for select *from student where s_sex = 'F';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039275447
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 |
| 1 | TABLE ACCESS BY INDEX ROWID | STUDENT | 7 | 189 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | |
|* 3 | BITMAP INDEX SINGLE VALUE | STUDENT_BITMAP_IDX | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S_SEX"='F')
Note
-----
- dynamic sampling used for this statement (level=2)
S_SEX='F'是過濾條件，走了STUDENT_BITMAP_IDX的索引，BITMAP INDEX SINGLE VALUE，這個VALUE是不是就是代表 F 的索引值呢?
基於函式的索引是在B-樹索引或點陣圖索引的基礎上，將一個函式計算得到的結果，作為索引值而建立的索引。
例如: 加快 select * from student where upper(s_name) like '%JA%' 查詢效率
--建立函式索引
SQL> create index student_fun_idx on student(upper(s_name)) tablespace users;
Index created
--檢視執行計劃驗證是否走函式索引
SQL> explain plan for select * from student where upper(s_name) like '%JA%';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2499371011
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 39 | 2
|* 2 | INDEX FULL SCAN | STUDENT_FUN_IDX | 1 | | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("S_NAME") IS NOT NULL AND UPPER("S_NAME") LIKE '%JA%')
Note
-----
- dynamic sampling used for this statement (level=2)
注意函式索引的寫法，WHERE條件 索引欄位使用方式 必須與 函式索引的建立方式 相同
即:
where upper(s_name) = 'XXX' 走索引，索引全掃描
where s_name = upper('XXX')或者 where s_name like upper('%JA%') 不走索引，全表掃描
驗證一下:
SQL> explain plan for select * from student where upper(s_name) = 'CRISS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2505413714
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2
| 1 | TABLE ACCESS BY INDEX ROWID| STUDENT | 1 | 39 | 2
|* 2 | INDEX RANGE SCAN | STUDENT_FUN_IDX | 1 | | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("S_NAME")='CRISS')
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
SQL> explain plan for select * from student where s_name = upper('Criss');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2356778634
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| STUDENT | 1 | 27 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("S_NAME"='CRISS')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected
反向鍵值索引可以避免不平衡的索引，使索引條目在索引中分佈的更加均勻。
可以直接建立 :
create index student_rev_idx on student(s_num) reverse;
或者用alter index 語句修改
alter index student_rev_idx rebuild noreverse;
alter index student_rev_idx rebuild reverse;
這裡有一個疑問:
什麼樣的表適合建立反向鍵值索引？或者說，什麼情況下，應該將普通的B-tree索引利用alter index idx_name rebuild reverse 子句修改成反向鍵值索引？
改變後，索引段中儲存的鍵值分佈會有什麼變化呢？
先了解一下反向鍵值的儲存形式及作用。
反向鍵值索引的儲存形式:
原值:12345
索引值:54321
反向鍵值索引的作用:
消除熱塊、使得B-Tree索引分佈更加平衡(防止樹的單邊增長嚴重)
舉個例子:
索引鍵原值: 12345
12346
12347
這個三條連續資料將儲存在同一個資料塊上！
反向鍵值索引值: 54321
64321
74321
三條資料將儲存在不同的資料塊上！
由此，可以解決上述問題，B-tree 鍵值太靠近，使得樹單邊增長，要不斷調整才能達到balance的表適合建立反向鍵值索引。
將原連續鍵值reverse後，形成多個不同鍵值分佈在不同的資料塊上。
===========================================================================================
索引的修改
索引的修改包括 合併索引、重構索引、重新命名索引。
在資料庫的使用過程中，表資料的不斷更新，就會在索引中產生越來越多的儲存碎片，這會使得索引失效。
這種情況下可以使用合併所引或重構索引來清除這些儲存碎片。
索引合併:
alter index index_name coalesce deallocate unused;
coalesce 表示合併索引，deallocate unused 表示合併後釋放多餘的儲存空間
索引重構:
alter index index_name rebuild;
其實質上是先刪除原來的索引，然後再根據原來的索引結構重建。
重新命名索引:
alter index index_name rename to new_name;
============================================================================================
索引的監視
開發中，我們可以透過開啟索引監視，來了解索引的使用情況
step 1 . 開啟我們要監視的索引
SQL> alter index student_rev_idx monitoring usage;
Index altered
step 2 . 檢視動態效能檢視 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
INDEX_NAME USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX NO 05/15/2014 10:32:02
當前索引 STUDENT_REV_IDX 的狀態為未使用
step 3 . 執行走索引的SQL語句
SQL> select * from student where s_num = '1004';
S_NUM S_NAME S_SEX
------- -------------------- -----
1004 Criss F
step 4 . 再次檢視動態效能檢視 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
INDEX_NAME USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX YES 05/15/2014 10:32:02
此時，索引STUDENT_REV_IDX狀態為使用過
step 4 . 當不需要監視索引時，關閉監視
SQL> alter index student_rev_idx nomonitoring usage;
Index altered
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28929558/viewspace-1161733/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫開發——瞭解rowidOracle資料庫
- Oracle資料庫開發——瞭解分割槽表Oracle資料庫
- Oracle資料庫開發——深入索引結構Oracle資料庫索引
- 瞭解oracle資料庫體系結構（3）Oracle資料庫
- 瞭解oracle資料庫體系結構（2）Oracle資料庫
- 瞭解oracle資料庫體系結構（1）Oracle資料庫
- 瞭解Oracle資料字典Oracle
- Oracle資料庫開發——序列Oracle資料庫
- 軟體開發人員真的瞭解SQL索引嗎(聚集索引)SQL索引
- 瞭解圖形資料庫資料庫
- 深入瞭解Oracle資料字典Oracle
- Oracle資料庫索引管理規範Oracle資料庫索引
- Oracle資料庫開發——表（概念）Oracle資料庫
- Oracle資料庫開發指南（二）Oracle資料庫
- ORACLE資料庫開發經驗Oracle資料庫
- Oracle資料庫索引使用及索引失效總結 轉Oracle資料庫索引
- 提的最多的資料庫“索引”，先來簡單瞭解一下資料庫索引
- 瞭解資料庫連線池資料庫
- 瞭解MySQl資料庫目錄MySql資料庫
- 深入瞭解Oracle資料字典(zt)Oracle
- Oracle資料庫開發——臨時表Oracle資料庫
- Oracle資料庫開發——同義詞Oracle資料庫
- Oracle資料庫開發指南（原創）Oracle資料庫
- 【資料庫】mysql資料庫索引資料庫MySql索引
- 資料庫索引資料庫索引
- oracle資料庫建立、刪除索引等操作Oracle資料庫索引
- oracle資料庫中索引空間的重用Oracle資料庫索引
- Oracle資料庫中索引的維護 ztOracle資料庫索引
- Oracle資料庫中索引的維護(zt)Oracle資料庫索引
- Oracle資料庫中索引的維護(轉)Oracle資料庫索引
- ORACLE資料庫開發經驗總結Oracle資料庫
- 帶你快速瞭解Oracle資料庫提供的恢復機制Oracle資料庫
- Oracle資料庫開發——表（資料完整性約束）Oracle資料庫
- 達夢資料庫索引結構詳解資料庫索引
- indexedDB 資料庫 索引Index資料庫索引
- 資料庫索引原理資料庫索引
- 資料庫索引《二》資料庫索引
- 資料庫索引《一》資料庫索引