為Oracle分頁查詢選擇索引策略

按照此處的 Oracle 文件，我正在嘗試對具有近 100 萬行的表的查詢進行分頁。

問題是，最終，當表有數百萬行時，性能會變差。看來Oracle使用的索引（Fast Full Index Scan）沒有考慮排序後的索引，所以整個數據又被排序了。

舉個例子：

create table sample1(
      id int primary key,
      c1 varchar2(3000) not null,
      c2 varchar2(3000)  not null,
      c3 varchar2(10));

create unique index index_unique3 on sample1(c1, c2);

create sequence seq_id1
 minvalue 0
 maxvalue 999999999999
 start with 1
 increment by 1
 cache 20;

INSERT INTO sample1
 SELECT  seq_id1.nextval,
 dbms_random.string('U',trunc(dbms_random.value(1,3000))),
 dbms_random.string('U',trunc(dbms_random.value(1,3000))),
 dbms_random.string('U',trunc(dbms_random.value(1,1)))
 FROM  dual
 CONNECT BY level <= 5000;

analyze table sample1 compute statistics;

select * from (
 select a.*, rownum r
 from (
   select  c1, c2
   from sample1
   order by  c1, c2
 ) a
 where rownum <= 24 
)
where r >= 2;

對於第一行，執行計劃顯示“全索引掃描”，但在插入幾千行（並再次收集統計資訊）後，它開始使用“索引快速全掃描”。

---------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |   24 |   72408 | 1595 | 00:00:01 |
| * 1 |   VIEW                     |               |   24 |   72408 | 1595 | 00:00:01 |
| * 2 |    COUNT STOPKEY           |               |      |         |      |          |
|   3 |     VIEW                   |               | 2002 | 6014008 | 1595 | 00:00:01 |
| * 4 |      SORT ORDER BY STOPKEY |               | 2002 | 6072066 | 1595 | 00:00:01 |
|   5 |       INDEX FAST FULL SCAN | INDEX_UNIQUE3 | 2002 | 6072066 |  326 | 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("R">=2)
* 2 - filter(ROWNUM<=24)
* 4 - filter(ROWNUM<=24)

而且，如果列c1和c2被定義為整數，那麼執行計劃將轉向“索引全掃描”並且時間安排非常好。

-------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows    | Bytes    | Cost | Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |      24 |      936 |    3 | 00:00:01 |
| * 1 |   VIEW               |               |      24 |      936 |    3 | 00:00:01 |
| * 2 |    COUNT STOPKEY     |               |         |          |      |          |
|   3 |     VIEW             |               |      24 |      624 |    3 | 00:00:01 |
|   4 |      INDEX FULL SCAN | INDEX_UNIQUE1 | 1420002 | 14200020 |    3 | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("R">=2)
* 2 - filter(ROWNUM<=24)

如何消除 varchar 表中的排序操作以加快查詢速度？有很多文件解釋如何進行查詢，但沒有一個告訴您如何優化它。

select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

更新

對於 varchar 表，將參數optimizer_index_cost_adj修改為10 會使 Oracle 使用 Full Index Scan 而不是 Fast Full Index Scan，但它仍然根據執行計劃對數據進行排序。

SELECT STATEMENT, GOAL = ALL_ROWS
VIEW   Object owner=SYS
 COUNT STOPKEY
  VIEW
   SORT ORDER BY STOPKEY
    INDEX FULL SCAN

更新 1

好的，經過一番研究，我發現問題出在 NLS_SORT = binary 的值上。將值設置為一種語言，問題就消失了。

https://community.oracle.com/thread/2436621

不完全確定你的問題是什麼。索引快速全掃描無法消除後續排序，因為它按照它們在媒體上存在的順序讀取索引塊，不一定按照索引排序順序，如文件中所述：

索引快速全掃描以未排序的順序讀取索引塊，因為它們存在​​於磁碟上。這種掃描不使用索引來探查表，而是讀取索引而不是表，本質上是使用索引本身作為表。

和

與全掃描不同，快速全掃描不能消除排序操作，因為它不會按順序讀取索引。

現在關於為什麼Oracle 優化器選擇對VARCHAR2(3000)列進行快速全掃描但對整數（我假設你的意思是NUMBER）列進行全掃描，我只能推測在後一種情況下，優化器期望索引葉頁將更密集地填充值，需要更少的讀取。它無法知道您的VARCHAR2值的實際長度，因此它必須假設最壞的情況（我猜這可能是單字節字元最多 6002 個字節）。

我在 Oracle 中遇到了類似的問題，我通過向查詢添加提示來解決這個問題。提示有時會給您帶來麻煩，但您的查詢非常簡單。如果您確信最好的計劃總是利用索引的自然排序，那麼提示應該是非常安全的。

FIRST_ROWS(1)提示可能會有所幫助：

FIRST_ROWS(n) 提示指示 Oracle 優化單個 SQL 語句以實現快速響應，選擇最有效地返回前 n 行的計劃。

您還可以考慮INDEX_ASC提示：

INDEX_ASC 提示顯式選擇指定表的索引掃描。如果語句使用索引範圍掃描，那麼 Oracle 會按索引值的升序掃描索引條目。

以及NO_INDEX_FFS提示：

NO_INDEX_FFS 提示使優化器排除對指定表上指定索引的快速全索引掃描。

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