索引@oracel索引技術之索引最佳化

下面情況即使表中存在索引，最佳化器也會傾向於選擇全表掃描

1 被訪問的表很小 有少數幾個塊組成

2 需要從表中獲取大量的資料

3 where 子句引用索引列上只存在極少數不同值

4 表上定義了較高並行度

索引掃描： 

索引唯一掃描 查詢只從表中獲取單個行 在主鍵約束索引列上指定相等條件時也會使用索引唯一掃描 在指定某個唯一索引的所有列時候也會使用

索引範圍掃描  訪問具有高度選擇性的資料時 按升序返回索引列的值 如果所有索引列都是相同的 按照rowid的順序輸出

索引跳躍式掃描 一個查詢未在where子句中指定複合索引的前導列

全索引掃描 給定索引列中所有條目的操作 是全表掃描 然後排序的很好替代方式 select *  from  a order by id;

索引快速全掃描 當索引本身包含查詢中指定的所有列時 select b,e from test where e>1000; 使用多塊讀來讀取索引

---查詢索引是否有被使用

alter index employees_idx1 monitoring usage;--開啟索引監控

select index_name,monitoring,used from v$object_usage;--查詢使用情況

---可以檢視資料庫訪問某個索引的次數

select * from dba_hist_sql_plan;

select * from dba_hist_sqlstat;

/*+no_index(表名，索引名)*/      索引

/*+no_indext_ffs(表名，索引名)*/ 索引快速掃描

/*+no_indext_ss(表名，索引名)*/  跳躍式索引掃描

/*+full(表名)*/                  強制表掃描

1000w行資料 表使用10w 個表塊儲存 索引塊有2w個大約 索引高度為3 BLEVEL為2 每個索引列有100個不同的值 這些值是均勻分佈的 所以該索引的每個索引值將出現1000w/100

select * from a where id='abcde';

首先讀取根和分支塊

然後讀取索引頁塊的1% 相當於20000*1%

因為請求了表中所有列,所以接下來資料庫必須讀取表中所有行，此時關鍵變數就是表中的聚簇因子 假設每個資料塊選擇1%，總成本便是0.01*10000000

此時若是選擇全表掃描 multi_loock_read_count的值設定為10的話，一共會有10000000/10次訪問，可以看出比走索引更快

dba_tab_cols中的unm_distince列可以想哈看任意列中不同數值的數量

列的選擇性*頁塊 來估算訪問索引期間 資料庫必須讀取多少個資料塊 

選擇性：列值有多少個不同的值 。如果索引是非常有選擇性的 那麼每個索引條目只有很少幾行。

dba_indexs中的clustering_factor列可以找到聚簇因子的值：用來衡量表中資料的有序程度 如果聚簇因子很接近行數，那麼資料便會很隨機的排列 （並不能保證資料排列的隨機性）

---表的選擇性*聚簇因子=透過索引訪問表的成本

良好的聚簇因子接近表的塊數 而糟糕的聚簇因子接近表的行數

使用萬用字元的查詢，最佳化器可能會選擇忽略索引，而使用全表掃描，如果匹配模式中的前導字元不是%或則—— ，可能會使用索引。

在謂詞中引用空值：此時為了確保資料庫會使用索引的唯一方法便是保證此表兩列中至少有一列被定義為not null，如果某列的索引列都是空，oracle資料庫就不會把該行包括到索引中。