Oracle效能分析7：索引的使用，oracle效能索引

Oracle效能分析7：索引的使用，oracle效能索引

這一節主要講述索引的使用，首先介紹怎麼在查詢中避免使用索引，然後介紹最佳化器怎麼判斷是否使用索引，並介紹了強制使用索引的方法，最後介紹了Oracle的平行處理方法。

避免使用索引

雖然你建立了索引，但有些查詢你可能需要避免使用這些索引，或者你為了做一些測試，希望看看各種情況下查詢的情況，也希望能夠避免使用一些索引或者索引掃描方式。Oracle提供了方式來達到這些目地，就是在查詢中使用hint資訊，具體情況如下。

避免使用某個索引

如果索引的選擇性很差，那麼也許使用其它索引或者使用全表掃描的效率會更加高效，這時你可以考慮避免使用該索引，方法如下：

/*+ no_index(table_name index_name)*/

表示查詢不使用table_name的index_name索引，下面是一個執行個體：

select /*+ no_index(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

最佳化器不使用指定索引後，仍然可以使用表上的其它索引，你也可以指定忽略一組索引：

select /*+ no_index(historyalarm idx_historyalarm$clear,idx_historyalarm$cleargmt)*/ position1  from historyalarm

如果你只是指定了no_index提示，而沒有列出任何索引，最佳化器將忽略指定表的所有索引：

select /*+ no_index(historyalarm)*/ position1  from historyalarm

避免快速掃描

文法如下：

/*+ no_index_ffs(table_name index_name)*/

表示查詢不使用table_name的index_name索引的索引快速掃描，下面是一個執行個體：

select /*+ no_index_ffs(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

強製表掃描

強製表掃描也是避免使用索引的一個方法，如下：

/*+ full(table_name)*/

表示查詢表table_name時強制使用全表掃描，下面是一個執行個體：

select /*+ full(historyalarm)*/ position1  from historyalarm

使用索引

查詢時最佳化器會計算使用索引的成本資訊，以決定是否使用索引，表的選擇性（通過過濾）乘以索引聚蔟因子（clustering factor），決定了通過索引訪問表的成本。下面我們從聚蔟因子開始。

聚蔟因子

聚蔟因子對應user_indexes視圖的CLUSTERING_FACTOR列，用於表示索引的葉子項怎麼和表格的行對應關係，下面是聚蔟因子的計算方法：
 為在索引中的每一個條目，Oracle比較條目所在表的資料區塊和前一個的索引條目資料區塊，如果是不同的資料區塊，則聚蔟因子加1，否則繼續。
因此，聚蔟因子的最小值等於索引上的所有條目對應的資料區塊數量（由於在單列或多列上的索引都不包含空資料，因此這將等於在表中包含了資料的資料區塊的數量），而最大值等於索引上的條目的數量（每兩個相鄰的條目都在不同的資料區塊塊上）。
下面看一個例子，我們使用表T1和T2（請看“Oracle效能分析4：資料存取方法之全掃描”），下面是尋找表T1上的索引IDX_T1_ID和T2上的索引IDX_T2_ID的聚蔟因子：

select i.INDEX_NAME, i.CLUSTERING_FACTOR  from user_indexes i where index_name = 'IDX_T1_ID'    or index_name = 'IDX_T2_ID'INDEX_NAMECLUSTERING_FACTORIDX_T1_ID17IDX_T2_ID1700

由於表T1中相同的資料都按順序儲存，因此索引條目的順序和資料的儲存順序基本一致，因此T1表的聚蔟因子達到了最低值；而T2表中的資料相同值都存放在不同的資料區塊上，因此索引條目中任意兩個相鄰的條目對應的資料都在不同的塊上，因此聚蔟因子達到了最大值。因此T1表的IDX_T1_ID索引使用代價很低，而T2表的IDX_T2_ID索引使用的代價則很高。
需要注意的是聚蔟因子與表中資料而不是與索引有關，因此重建索引對它沒有任何影響。

強制使用索引

當Oracle的最佳化器無法選擇正確的索引時（最佳化器並不是完美的，它並不總能準確地瞭解資料的性質和分布），我們可以考慮強制使用索引，下面介紹使用hint資訊來強制使用索引的方法。
需要注意的是在使用提示之前，應先查看是否有對象的正確的統計資訊，並檢查SQL語句的構造方法，提示應該是無路可走時的選擇。

使用hint資訊

index提示指示最佳化器對特定的表使用索引掃描，例如：

select /*+ index(historyalarm idx_historyalarm$clear)*/ position1  from historyalarm

這裡相當於強制最佳化器使用idx_historyalarm$clear，也可以不指定索引，這時最佳化器就會選擇它認為最優的索引：

select /*+ index(historyalarm)*/ position1  from historyalarm

這樣做的缺陷在於最佳化器可能會選擇錯誤的索引，因此通常這並不是好的選擇。
如果你需要在index提示中指定多個索引，可以使用index_combine提示，如下；

select /*+ index_combine(historyalarm idx_historyalarm$01 idx_historyalarm$02)*/ position1  from historyalarm

這時，最佳化器會使用兩個指定索引成本最低的組合，如果不提供索引列表，則最佳化器將根據其成本估計使用索引的最佳組合。
除了上面介紹的hint資訊，還有下面的常用的跟索引相關的hint資訊：
 1）index_asc：指定按升序做索引範圍掃描；
 2）index_desc：指定按降序順序掃描升序索引，或者按升序掃描降序索引；
 3）index_join：當兩個索引包含返回查詢結果所需的所有列時，使用該提示串連索引，從而從索引直接擷取所有資料，文法類似index_combine；
 4）index_ss：執行索引跳躍式掃描；
 5）index_ffs：以索引快速全掃描的方式訪問資料。

hint失效

導致hint失效的主要原因如下：
 1）最佳化器認為使用hint會導致錯誤的結果。這往往由於資料中包含許多空值，因此有一些不會出現在索引中的行，導致索引掃描最後可能會導致錯誤的結果；
 2）SQL語句中的表指定了別名，這時hint中也必須使用別名，否則hint會被忽略，如：

select /*+ index(h idx_historyalarm$raise)*/ position1  from historyalarm h

3）查詢涉及表串連，基於最佳化器選額的串連方法，無法使用索引。這時往往可以通過添加更多的提示資訊來解決（如指定表的串連方式等）。

並行

在Oracle中支援平行處理操作，下面介紹和平行處理相關的概念。

parallel

指定SQL執行的並行度，這個值會覆蓋表自身設定的並行度，如：

select /*+ parallel(h 4)*/ * from historyalarm h

parallel有以下幾種層級：
 1）parallel：總是並存執行，資料庫計算並行度，值大於等於2；
 2）parallel(default)：和parallel相同；
 3）parallel(auto)：資料庫計算並行度，值大於等於1，當並行度為1時表示串列執行；
 4）parallel(manual)：最佳化器使用表的並行度；
 5）parallel(integer)：最佳化器使用指定的並行度。

並行度

在建立表時指定並行度

create table t1 parallel 4 as select trunc((rownum - 1) / 100) id, rownum value  from dba_source where rownum <= 10000

查看錶的並行度

select degree from user_tables where table_name = 'T1'

修改表的並行度

alter table t1 parallel(degree 3)

取消表的並行度

alter table t1 noparallel

上面的方法也同樣適用於索引。

no_parallel

在SQL中禁止使用並行。

select /*+ no_parallel(h)*/ * from historyalarm h

parallel_index

在處理分區索引時，可以指示最佳化器使用多個並發的並行伺服器，來並行化劑中類型的索引操作。可以並行化的操作包括索引範圍掃描、全索引掃描和索引快速全掃描。
下面是通過parallel_index提示來指定分區索引的並行掃描操作：

select /*+ parallel_index(h idx_historyalarm$06 4)*/ alarmclearedtime, alarmkey  from historyalarm h

整數4表示索引掃描的並行度。

no_parallel_index

禁止使用索引並行掃描。

oracle索引問題，刪除再重建索引與索引分析

1. 應該是可行的， 具體 會不會節省時間 試一下就可以了。

2. 大概每個月儲存四五十萬的資料，裡面只儲存最新四個月的資料

每次create這7個索引用時都特別長，大概需要三四個小時；

200萬的資料，重建索引花費的時間太長了；很奇怪。

3. 估計之前的 先drop掉索引，然後插入資料完畢後create索引 也是為了避免 插入資料時，索引對插入效率的影響。
 
對於oracle的索引的問題

對於你這個問題，不是簡單就能說明白為什麼不變的。首先你的建立合適的索引，索引不只是b-tree一種。
索引分類：
1.b-tree
2.唯一索引 unique
3.複合
4.函數
5.位元影像
6.反鍵
7.本地和全域
1、表的主鍵、外鍵必須有索引；
2、資料量超過300的表應該有索引；
3、經常與其他表進行串連的表，在串連欄位上應該建立索引；
4、經常出現在Where子句中的欄位，特別是大表的欄位，應該建立索引；
5、索引應該建在選擇性高的欄位上；
6、索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引；
7、複合索引的建立需要進行仔細分析；盡量考慮用單欄位索引代替：
A、正確選擇複合索引中的主欄欄位，一般是選擇性較好的欄位；
B、複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位查詢是否極少甚至沒有？如果是，則可以建立複合索引；否則考慮單欄位索引；
C、如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位索引；
D、如果複合索引所包含的欄位超過3個，那麼仔細考慮其必要性，考慮減少複合的欄位；
E、如果既有單欄位索引，又有這幾個欄位上的複合索引，一般可以刪除複合索引；
8、頻繁進行資料操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對執行計畫造成負面影響；
以上是一些普遍的建立索引時的判斷依據。一言以蔽之，索引的建立必須謹慎，對每個索引的必要性都應該經過仔細分析，要有建立的依據。因為太多的索引與不充分、不正確的索引對效能都毫無益處：在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來說，負面影響更大 。

另外想要知道為什麼，需要你把你的sql貼出來？還要看執行計畫在能做到最好的最佳化。