一個同事問起如果獲取一個資料列最大取值所在的一行記錄，感覺是一個有趣的問題。一般看起來很簡單的問題，解法可能有多樣，我們選擇的路徑可能有多種方式。

環境準備

原始問題是：獲取資料表中指定一個資料列中最大的日期記錄，一條即可。如此，我們利用dba_objects的last_ddl_time資料列來構建實驗環境。

SQL> create table t (id number(10), da date);

Table created

SQL> insert into t select object_id, last_ddl_time from dba_objects;

50351 rows inserted

SQL> commit;

Commit complete

獲取最大last_ddl_time列對應的記錄，我們可以使用多種方法。

利用rownum篩選最大資料集合

想到只需要一條最大的記錄，只管想到了rownum。利用rownum偽劣，可以進行刪選結果的剔除。（這個問題在筆者之前的系列中有所涉及，可以參考http://space.itpub.net/17203031/viewspace-687124）

設計的查詢和結果。

SQL> select * from (select * from t order by da desc) where rownum<2;

IDS DA

----------- -----------

53876 2011-3-11 2

此種方法的執行效率方面，是如果呢？我們透過autotrace工具進行檢視。

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select * from (select * from t order by da desc) where rownum<2;

已用時間: 00: 00: 00.07

執行計劃

----------------------------------------------------------

Plan hash value: 3299198703

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 22 | 43 (5)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | VIEW | | 50351 | 1081K| 43 (5)| 00:00:01 |

|* 3 | SORT ORDER BY STOPKEY| | 50351 | 590K| 43 (5)| 00:00:01 |

| 4 | TABLE ACCESS FULL | T | 50351 | 590K| 43 (5)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<2)

3 - filter(ROWNUM<2)

統計資訊

----------------------------------------------------------

1 recursive calls

0 db block gets

157 consistent gets

0 physical reads

0 redo size

465 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

從執行計劃中，我們看出幾個問題。首先在沒有索引的情況下，查詢整體的效率還是可以接受的（5萬餘條紀錄消耗0.07s）。這個顯然是透過stopkey操作，將不必要的排序和返回列加以遮蔽的結果。效能最大的消耗是進行全表掃描和排序（存在一次memory內部的排序），同時存在一些邏輯讀情況。

這種方法存在一些缺陷，是需要注意的。首先，如果da列存在重複值，而需求要求返回最大的多列時，這樣rownum的取定就存在問題了。同時，方法中的FTS，將來也會是一個比較大的缺陷方面。即使在da列加入了索引，這部分FTS的負擔也較難進行最佳化。

利用子查詢實現功能

思路：先利用子查詢獲取到取值最大的具體值，再進行查詢。這樣的方法比較中規中矩，但是能夠解決rownum方案的缺陷。

SQL> select * from t where da=(select max(da) from t );

IDS DA

----------- -----------

53876 2011-3-11 2

SQL> select * from t where da=(select max(da) from t );

已用時間: 00: 00: 00.02

執行計劃

----------------------------------------------------------

Plan hash value: 1287471683

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 88 | 1056 | 86 (5)| 00:00:02 |

|* 1 | TABLE ACCESS FULL | T | 88 | 1056 | 43 (5)| 00:00:01 |

| 2 | SORT AGGREGATE | | 1 | 8 | | |

| 3 | TABLE ACCESS FULL| T | 50351 | 393K| 43 (5)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DA"= (SELECT MAX("DA") FROM "T" "T"))

統計資訊

----------------------------------------------------------

1 recursive calls

0 db block gets

315 consistent gets

0 physical reads

0 redo size

465 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

這種方法雖然沒有用到很高階的語法和技巧，但是還是獲取的很好的效能效率。首先，執行時間較使用rownum有所縮短（只有0.02s）。其次就是排序操作sort被避免，因為sort操作會大量消耗PGA的空間。

唯一缺點就是進行邏輯讀的數量較高，達到了315。那麼，有無最佳化的空間呢？可以藉助索引進行最佳化。

//建立索引

SQL> create index idx_t_da on t(da);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

//進行查詢

SQL> select * from t where da=(select max(da) from t );

已用時間: 00: 00: 00.01

執行計劃

----------------------------------------------------------

Plan hash value: 3678432891

---------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 87 | 1044 | 46 (5)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T | 87 | 1044 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T_DA | 87 | | 1 (0)| 00:00:01 |

| 3 | SORT AGGREGATE | | 1 | 8 | | |

| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_DA | 50351 | 393K| | |

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DA"= (SELECT MAX("DA") FROM "T" "T"))

統計資訊

----------------------------------------------------------

1 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

465 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

在資料表T的da列上，我們加入了normal型別的索引idx_t_da。這樣在進行max值獲取的過程中，Oracle是有選擇不進行FTS操作來獲取最大值，而是直接讀取索引樹的兩端葉節點。

從結果看，這是我們目前取得的最好執行路徑。首先，執行時間縮短為0.01s，消除了沒有索引方案時進行FTS的瓶頸。其次，沒有進行sort操作佔用過大的排序空間。最後是大幅度減少了邏輯讀數量，只有5（沒有索引時要消耗315），提升近60倍。

這裡，我們注意到Oracle提供了一種操作“INDEX FULL SCAN (MIN/MAX)”，相當於只對於索引樹進行兩端搜尋，獲取到最大值/最小值的rowid之後，就立刻停止。

最大取值資料列行獲取

相關文章