Oracle Index 的三個問題(一)
2024-08-29 13:39:18
供稿：網友
致自動啟用了此目標 (前提是備用目標的屬性必須有效)。
log_archive_dest_state_9:
說明: 標識特定日志歸檔目標的最近的用戶定義狀態。
值范圍: ENABLE--假如目標屬性有效, 則啟用歸檔日志目標; DEFER--即使目標屬性有效, 也要延遲處理歸檔日志目標; 或者是 ALTERNATE--延遲處理歸檔日志目標, 直到另一個目標的失敗導致自動啟用了此目標 (前提是備用目標的屬性必須有效)。
Log_archive_start:
說明: n只在數據庫處于“歸檔日志”模式的情況下適用。它指定重做日志是自動還是手動復制。建議值是 TRUE, 即執行自動歸檔;
否則就需要手動干預, 使用索引( Index )是常見的數據庫對象，它的設置好壞、使用是否得當，極大地影響數據庫應用程序和Database 的性能。雖然有許多資料講索引的用法， DBA 和 Developer 們也經常與它打交道 。
一、索引并非總是最佳選擇
假如發現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 會否使用索引索引( Index )是常見的數據庫對象，它的設置好壞、使用是否得當，極大地影響數據庫應用程序和Database 的性能。雖然有許多資料講索引的用法， DBA 和 Developer 們也經常與它打交道 。
一、索引并非總是最佳選擇
假如發現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
