Oracle索引詳解

標籤：快速   等於   evel   搜尋   預設   ima   create   name   需要   

Oracle索引詳解（一）### --索引介紹

??索引對於Oracle學習來說，非常重要，在資料量巨大的狀況下，使用恰到好處的索引，將會使得資料查詢時間大大減少，於2017/12/25暫時對Oracle中的索引進行一個大致的瞭解。

• 索引的建立文法
• 索引的特點
• 索引的不足
• 比較適合建立索引的列的特點
• 不適合建立索引的列的特點
• 限制索引（建立了索引，但是無法使用）
• 查詢索引
• 複合式索引
• Oracle rowid
• 選擇性
• 群集因子
• 二元高度
• 快速全表掃描
• 跳躍式掃描

索引的建立文法

create or replace unique|bitmap index <schema>.<index_name> on <schema>.<table_name>  (<column_name>|<expression> asc|desc ,   <column_name>|<expression> asc|desc ...)    tablespace <tablespace_name>    storage <storage_settings>    logging|nologging    compute statistics    nocompress|compress<nn>    nosort|reverse    partition|global partition<partition_setting> 

• unique|bitmap : unique表示唯一值索引，bitmap表示位元影像索引，為空白則預設為B-tree索引
• column_name|expression asc|desc , ... :可以單列索引，也可以多列進行聯合索引，當為
• tablespace : 制定存放索引的資料表空間（當表和索引在不同的資料表空間的時候，效率更高）
• storage : 可以設定資料表空間的儲存參數
• logging|nologging : 是否對索引產生redolog（對於大表來說，可以設定為nologging從而來減少空間佔用，提高效率）
• compute statistics : 設定為建立索引時，收集統計資訊
• nocompress|compressnn : 是否使用“鍵壓縮”（使用鍵壓縮可以刪除一個鍵列中出現的重複值）
• nosort|reverse : nosort表示與表中相同的順序進行建立索引，reverse表示使用與表中相反的順序進行建立索引
• partition|nopartition|global partition : 可以在分區表上和未分區表上對建立的索引進行分區

索引的特點

1. 大大加快檢索資料的速度
2. 通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性
3. 加速表與表之間的串連
4. 查詢語句匯總含有分組或者排序的語句時，速度更快
5. 查詢的過程中，使用索引，使用最佳化隱藏器，從而提高系統的效能

索引的不足

1. 建立和維護索引，比較耗費時間，隨著資料量的增大而增大
2. 建立索引，佔一定的物理空間（聚簇索引，佔用空間會更大）
3. 在對錶進行增刪改的時候，索引相應的也需要進行動態更新

比較適合建立索引的列的特點

1. 經常需要搜尋的列上
2. 主鍵，一般建立唯一性索引，保持資料的唯一性
3. 外鍵，提高表與表之間串連的速度
4. 需要排序的列上
5. where子句後邊經常出現的欄位
6. 經常需要根據範圍進行搜尋的列上，比如日期

不適合建立索引的列的特點

1. 很少進行搜尋的列上
2. 列取值比較少的列上
3. blob類型的列上
4. 修改頻率比較高的列上

限制索引（建立了索引，但是無法使用）

1. 使用不等於<> 、 != ，（不等於操作符一定會進行全表掃描）
2. 使用is null 、 is not null （只要索引中出現一個null，那麼這個索引就報廢了。所以在建立索引的時候，一定要將準備建立索引的列設定為not null）
3. 使用函數（where子句中含有trunc()、add_months()之類）的時候，sql最佳化器會自動忽略掉索引
4. where子句中，進行了資料類型不匹配的比較，比如(where row_num = ‘1‘)的時候，生氣了最佳化器會限制索引的使用

查詢索引

• dba_indexes
• user_indexes
• uesr_ind_columns

複合式索引

1. 索引中，包含不止一個列。
2. 在Oracle9i之前，需要先使用前置索引，才能使用複合式索引。

Oracle rowid

??實體表中，每一行都有rowid,通過每一行的rowid,Oracle提供了訪問單行資料的能力。

選擇性

??user_indexes中distinct_keys,選擇性越高，那麼索引返回的值就越少。

群集因子

??user_indexes中的clustering factor 越接近 leaf block的值的話，說明表中的資料越有序。

二元高度

??dba_indexes 的 Blevel列查看對應索引的二元高度，二元高度隨著表的大小以及被索引的列中，值的範圍的狹窄程度而變化。重建索引可以降低二元高度。

快速全表掃描

??允許Oracle執行一個全域索引的掃描操作，快速全表掃描可以快速讀取B-tree索引上的所有樹葉塊。

跳躍式掃描

create index TT_index on TT(teamid,areacode);select /*+ index(tt TT_index )*/ count(areacode) from tt;

Oracle索引詳解