Oracle PK 索引性能極低

有一個MYHIST包含 42M 行的表。

該表有一個主鍵列，上面MYHIST_ID NUMBER(22)有一個有效的索引PK_MYHIST，還有 50 個列（類型為 VARCHAR2、NUMBER 和 DATE），這似乎對這個主題不感興趣。

假設，該表的統計資訊（以及它的 PK 索引）已在 5 分鐘前收集。

select index_name, table_name, index_type, num_rows, distinct_keys, sample_size, status
 from user_indexes
where table_name = 'MYHIST';

INDEX_NAME | TABLE_NAME | INDEX_TYPE | NUM_ROWS | DISTINCT_KEYS | SAMPLE_SIZE | STATUS
----------------------------------------------------------------------------------------
 PK_MYHIST |    MYHIST  |    NORMAL  | 42297450 |    42297450   |    42297450 | VALID

出乎意料的是，最簡單的帶有過濾器的查詢以MYHIST_ID全掃描結束。保證表中不超過 1000 行具有MYHIST_ID > 4615200.

   select *
     from MYHIST t
    where MYHIST_ID > 4615200
    order by MYHIST_ID desc;

   Plan:

   --------------------------------------------------------------------------------------
   | Id  | Operation            | Name    | Rows     | Bytes       | Cost    | Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT     |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
   |   1 |   SORT ORDER BY      |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
   | * 2 |    TABLE ACCESS FULL | MYHIST  | 38067431 | 14655960935 |  746842 | 02:29:23 |
   --------------------------------------------------------------------------------------

   Predicate Information (identified by operation id):
   ------------------------------------------
   * 2 - filter("MYHIST_ID">4615200)

有了索引提示，該計劃似乎更糟。

   select /*+ index(t PK_MYHIST) */ *
     from MYHIST t
    where MYHIST_ID > 4615200
    order by MYHIST_ID desc;

   Plan:
   ---------------------------------------------------------------------------------------------------
   | Id  | Operation                      | Name       | Rows     | Bytes       | Cost    | Time     |
   ---------------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT               |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
   |   1 |   SORT ORDER BY                |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
   |   2 |    TABLE ACCESS BY INDEX ROWID | MYHIST     | 38067431 | 14655960935 | 6148331 | 20:29:40 |
   | * 3 |     INDEX RANGE SCAN           | PK_MYHIST  | 38067431 |             |   85356 | 00:17:05 |
   ---------------------------------------------------------------------------------------------------

   Predicate Information (identified by operation id):
   ------------------------------------------
   * 3 - access("MYHIST_ID">4615200)

當索引沒有給出預期的性能時，您能否解釋導致這種情況的原因？

由於有超過 10% 的行具有 access(“MYHIST_ID”>4615200)，因此全表掃描是選擇它們的最快方法。

如果您想嘗試更高的數字（例如“MYHIST_ID”>46152000），Oracle 的優化器可能更喜歡使用該索引。

引用自：https://dba.stackexchange.com/questions/214778