程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 有關Oracle Index 的三個問題(一)

有關Oracle Index 的三個問題(一)

編輯:Oracle數據庫基礎

索引 ( Index ) 是常見的數據庫對象,它的設置好壞、使用是否得當,極大地影響數據庫應用程序和Database 的性能。雖然有許多資料講索引的用法,DBA 和Developer們也經常與它打交道,但筆者發現,還是有不少的人對它存在誤解,因此針對使用中的常見問題,講三個問題。此文所有示例所用的數據庫是Oracle 8.1.7 OPS on HP N serIEs , 示例全部是真實數據,讀者不需要注意具體的數據大小,而應注意在使用不同的方法後,數據的比較。本文所講基本都是陳詞濫調,但是筆者試圖通過實際的例子,來真正讓您明白事情的關鍵。

第一講、索引並非總是最佳選擇

如果發現 Oracle 在有索引的情況下,沒有使用索引,這並不是Oracle 的優化器出錯。在有些情況下,Oracle 確實會選擇全表掃描( Full Table Scan ) , 而非索引掃描(Index Scan )。這些情況通常有:

1. 表未做 statistics, 或者statistics 陳舊,導致Oracle 判斷失誤。

2. 根據該表擁有的記錄數和數據塊數,實際上全表掃描要比索引掃描更快。

對第 1 種情況,最常見的例子,是以下這句sql 語句:

select count(*) from mytable;

在未作statistics 之前,它使用全表掃描,需要讀取 6000 多個數據塊(一個數據塊是8k ) , 做了statistics 之後,使用的是INDEX (FAST FULL SCAN) ,只需要讀取450 個數據塊。但是,statistics 做得不好,也會導致Oracle 不使用索引。

第2 種情況就要復雜得多。一般概念上都認為索引比表快,比較難以理解什麼情況下全表掃描要比索引掃描快。為了講清楚這個問題,這裡先介紹一下Oracle 在評估使用索引的代價(cost )時兩個重要的數據:CF(Clustering factor) 和 FF(Filtering factor).

CF: 所謂CF, 通俗地講,就是每讀入一個索引塊,要對應讀入多少個數據塊。

FF: 所謂FF, 就是該sql 語句所選擇的結果集,占總的數據量的百分比。

大約的計算公式是:FF * (CF + 索引塊個數 ) ,由此估計出,一個查詢, 如果使用某個索引,會需要讀入的數據塊塊數。需要讀入的數據塊越多,則cost 越大,Oracle 也就越可能不選擇使用 index. (全表掃描需要讀入的數據塊數等於該表的實際數據塊數)

其核心就是,CF 可能會比實際的數據塊數量大。CF 受到索引中數據的排列方式影響,通常在索引剛建立時,索引中的記錄與表中的記錄有良好的對應關系,CF 都很小;在表經過大量的插入、修改後,這種對應關系越來越亂,CF 也越來越大。此時需要DBA 重新建立或者組織該索引。

如果某個sql 語句以前一直使用某索引,較長時間後不再使用,一種可能就是CF 已經變得太大,需要重新整理該索引了。

FF 則是Oracle 根據statistics 所做的估計。比如, mytables 表有32 萬行,其主鍵myid 的最小值是 1 ,最大值是409654 ,考慮以下sql 語句:

Select * from mytables where myid>=1; 和 
Select * from mytables where myid>=400000

這兩句看似差不多的sql 語句,對Oracle 而言,卻有巨大的差別。因為前者的FF是 100% , 而後者的FF可能只有 1% 。如果它的CF大於實際的數據塊數,則Oracle 可能會選擇完全不同的優化方式。而實際上,在我們的數據庫上的測試驗證了我們的預測 . 以下是在HP上執行時它們的explain plan:

第一句:

SQL> select * from mytables where myid>=1;

已選擇 325917 行。

Execution Plan  
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3132 Card=318474 Byt es=141402456) 
1 0 TABLE Access (FULL) OF 'MYTABLES' (Cost=3132 Card=318474 Byt es=141402456) 
Statistics 
7 recursive calls 
89 db block gets 
41473 consistent gets 
19828 physical reads 
0 redo size 
131489563 bytes sent via SQL*Net to client 
1760245 bytes received via SQL*Net from client 
21729 SQL*Net roundtrips to/from clIEnt 
1 sorts (memory) 
0 sorts (disk) 
325917 rows processed

第二句:

Execution Plan 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=663 Bytes=2 94372) 
1 0 TABLE Access (BY INDEX ROWID) OF 'MYTABLES' (Cost=346 Card=663 
Bytes=294372) 
2 1 INDEX (RANGE SCAN) OF 'PK_MYTABLES' (UNIQUE) (Cost=5 Card=663) 
Statistics 
1278 recursive calls 
0 db block gets 
6647 consistent gets 
292 physical reads 
0 redo size 
3544898 bytes sent via SQL*Net to client 
42640 bytes received via SQL*Net from client 
524 SQL*Net roundtrips to/from clIEnt 
1 sorts (memory) 
0 sorts (disk) 
7838 rows processed

顯而易見,第1句沒有使用索引,第2句使用了主鍵索引 pk_mytables. FF 的巨大影響由此可見一斑。由此想到,我們在寫sql 語句時,如果預先估計一下FF, 你就幾乎可以預見到Oracle 會否使用索引。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved