min/max函數的index問題
?
??? 在查詢某表時同時使用了min和max函數,結果查詢很慢，但是單獨執行min或者max時速度很快，建個環境模擬一下:
?
create table t1_t(a int,b varchar2(50));
?
insert into t1_t(a,b) (select mod(rownum,50) rn,object_name from user_objects);
insert into t1_t(a,b) (select * from t1_t);
insert into t1_t(a,b) (select * from t1_t);
insert into t1_t(a,b) (select * from t1_t);
insert into t1_t(a,b) (select * from t1_t);
?
commit;
?
create index t1_a_index on t1_t(a);
create index t1_b_index on t1_t(b);
?
?
select min(a) from t1_t;
?
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3242 Card=1 Bytes=3)
?? 1??? 0?? SORT (AGGREGATE)
?? 2??? 1???? INDEX (FULL SCAN (MIN/MAX)) OF 'T1_T_INDEX' (INDEX)
?
--只有min函數時是走索引的
?
select min(a),max(a) from t1_t;
Execution Plan
----------------------------------------------------------
?? 0????? SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3242 Card=1 Bytes=3)
?? 1??? 0?? SORT (AGGREGATE)
?? 2??? 1???? TABLE ACCESS (FULL) OF 'T1_T' (TABLE) (Cost=3242 Card=3591122 Bytes=10773366)
?
--兩個一起的時候就全表掃描了
?
?
??? INDEX (FULL SCAN (MIN/MAX))算法：如果是Max,首先沿著最右邊的Root-Branch Node-Leaf Node，發現最右邊的Leaf Block是空的，于是沿著逆向指針往左走，一直走到最左邊發現都是空的，于是掃描了所有的Leaf Blocks。如果是Min,首先沿著最左邊的Root-Branch Node-Leaf Node，發現最左邊的Leaf Block是空的，于是沿著順向指針往右走，走到最右邊發現都是空的，掃描了所有的Leaf blocks。
?
?
?
??? 但是如何讓min、max同時查詢時走索引？似乎不可以，以下轉載一篇非常詳細的說明文檔：
???
=============================================================================================
?
Table sbfi_ctry_flow_curve_wheel有大約1500萬條記錄，運行下面的sql需要4秒鐘左右，developer認為時間太長，想優化。
SQL>select min(trade_dt), max(trade_dt) from sbfi_ctry_flow_curve_wheel;
developer很奇怪，trade_dt列上建有一個索引，但是執行的時候，oracle總是選擇走primary key，而不選擇那個索引。
?
經檢查，發現表sbfi_ctry_flow_curve_wheel的索引情況如下：
SQL> list
? 1? select index_name,column_name,column_position from user_ind_columns
? 2? where table_name=upper('sbfi_ctry_flow_curve_wheel')
? 3? order by 1
? 4* ,3
SQL> /
?
INDEX_NAME???????????????????? COLUMN_NAME? COLUMN_POSITION
------------------------------ ------------ ---------------
SBFI_CTRY_FLOW_CURVE_WHEEL_PK? TRADE_DT?????????????????? 1
SBFI_CTRY_FLOW_CURVE_WHEEL_PK? CTRY_CODE????????????????? 2
SBFI_CTRY_FLOW_CURVE_WHEEL_PK? MONTH????????????????????? 3
TRADE_DT_INDEX???????????????? TRADE_DT?????????????????? 1
?
嘗試加hint /*+ index(sbfi_ctry_flow_curve_wheel TRADE_DT_INDEX) */，讓oracle選擇走索引TRADE_DT_INDEX。結果發現運行時間沒有縮短，反而從4秒增加到7秒。
SQL> select /*+ index(sbfi_ctry_flow_curve_wheel TRADE_DT_INDEX) */ min(trade_dt), max(trade_dt) from bfi_ctry_flow_curve_wheel;
?
MIN(TRADE MAX(TRADE
--------- ---------
01-JAN-01 07-SEP-07
?
Elapsed: 00:00:06.91
?
為什么會出現這種情況？我們嘗試用TRACE去跟蹤執行過程。
==session 1, don't use hint
alter session set timed_statistics=true
/
alter session set max_dump_file_size=unlimited
/
alter session set tracefile_identifier='PRIMARY_KEY'
/
alter session set events '10046 trace name context forever, level 12'
/
select min(trade_dt), max(trade_dt) from sbfi_ctry_flow_curve_wheel
/
alter session set events '10046 trace name context off'
/
?
==session 2, use hint
alter session set timed_statistics=true
/
alter session set max_dump_file_size=unlimited
/
alter session set tracefile_identifier='TRADE_DT_INDEX'
/
alter session set events '10046 trace name context forever, level 12'
/
select /*+ index(sbfi_ctry_flow_curve_wheel TRADE_DT_INDEX) */ min(trade_dt), max(trade_dt) from sbfi_ctry_flow_curve_wheel
/
alter session set events '10046 trace name context off'
/
?
兩種情況的trace用tkprof分析后，主要部分結果如下:
==session1, don't use hint
********************************************************************************
select min(trade_dt), max(trade_dt)
from
sbfi_ctry_flow_curve_wheel
?
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 1????? 0.00?????? 0.34????????? 0????????? 0????????? 0?????????? 0
Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 2????? 2.14?????? 3.84????? 26044????? 26067????????? 0?????????? 1
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 4????? 2.14?????? 4.18????? 26044????? 26067????????? 0?????????? 1
?
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89?
?
Rows???? Row Source Operation
-------? ---------------------------------------------------
????? 1? SORT AGGREGATE
7538400?? INDEX FAST FULL SCAN SBFI_CTRY_FLOW_CURVE_WHEEL_PK (object id 35844)
?
Elapsed times include waiting on following events:
? Event waited on???????????????????????????? Times?? Max. Wait? Total Waited
? ----------------------------------------?? Waited? ----------? ------------
? SQL*Net message to client?????????????????????? 2??????? 0.00????????? 0.00
? db file scattered read?????????????????????? 1649??????? 0.14????????? 2.26
? SQL*Net message from client???????????????????? 2??????? 4.15????????? 4.15
********************************************************************************
?
==session2, use hint
********************************************************************************
select /*+ index(sbfi_ctry_flow_curve_wheel TRADE_DT_INDEX) */ min(trade_dt),
? max(trade_dt)
from
sbfi_ctry_flow_curve_wheel
?
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 2????? 3.21?????? 8.84????? 19945????? 19945????????? 0?????????? 1
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 4????? 3.21?????? 8.85????? 19945????? 19945????????? 0?????????? 1
?
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89?
?
Rows???? Row Source Operation
-------? ---------------------------------------------------
????? 1? SORT AGGREGATE
7538400?? INDEX FULL SCAN TRADE_DT_INDEX (object id 35830)
?
Elapsed times include waiting on following events:
? Event waited on???????????????????????????? Times?? Max. Wait? Total Waited
? ----------------------------------------?? Waited? ----------? ------------
? SQL*Net message to client?????????????????????? 2??????? 0.00????????? 0.00
? db file sequential read???????????????????? 19945??????? 0.05????????? 5.93
? SQL*Net message from client???????????????????? 2??????? 6.10????????? 6.10?
********************************************************************************
?
對比后主要的不同羅列如下：
走primary key, consistent read，即query值為 26067，fetch時間為3.84秒，訪問主鍵索引的方法為INDEX FAST FULL SCAN。
走TRADE_DT_INDEX索引，consistent read值為19945, fetch時間為8.84秒，訪問索引TRADE_DT_INDEX的方法為INDEX FULL SCAN。
?
關于INDEX FAST FULL SCAN，oracle文檔中解釋如下：
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
?
關于INDEX FAST FULL SCAN：
A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:
All of the columns in the table referenced in the query are included in the index.
At least one of the index columns is not null.
A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.
?
上面明確講到了fast full scan比full scan要快，因為它用multiblock I/O，而且可以parallelized。
順便也注意到要調優的這句sql，只返回trade_dt列的值，滿足index (fast) full scan的條件，即返回結果的列全都包含在索引里，非空。所以該sql只要掃描索引就能返回需要的結果，不需要再根據rowid去訪問表。
?
既然要掃描整個索引，FAST FULL SCAN 比 FULL SCAN 快，TRADE_DT_INDEX 的 size 比 PK 的 size 小，那對 RADE_DT_INDEX 做FFS應該是最快的訪問路徑。用index_ffs hint:
SQL> select /*+ index_ffs(sbfi_ctry_flow_curve_wheel TRADE_DT_INDEX) */ min(trade_dt), max(trade_dt) from bfi_ctry_flow_curve_wheel;
?
MIN(TRADE MAX(TRADE
--------- ---------
01-JAN-01 07-SEP-07
?
Elapsed: 00:00:02.61
?
相比上面的4秒和7秒是快了一些。
?
但是根據一般的理解，象min(),max()這樣的函數，Oracle應該直接訪問索引的最左邊或者最右邊，這樣的訪問速度才是最快的。嘗試SQL>select min(trade_dt) from sbfi_ctry_flow_curve_wheel并生成10046 trace文件，用tkprof格式化后結果如下：
?
==session 3, single function
********************************************************************************
select max(trade_dt)
from
sbfi_ctry_flow_curve_wheel
?
call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
------- ------? -------- ---------- ---------- ---------- ----------? ----------
Parse??????? 2????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Execute????? 2????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
Fetch??????? 4????? 0.00?????? 0.00????????? 0????????? 6????????? 0?????????? 2
------- ------? -------- ---------- ---------- ---------- ----------? ----------
total??????? 8????? 0.00?????? 0.01????????? 0????????? 6????????? 0?????????? 2
?
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 89?
?
Rows???? Row Source Operation
-------? ---------------------------------------------------
????? 1? SORT AGGREGATE
????? 1?? INDEX FULL SCAN (MIN/MAX) SBFI_CTRY_FLOW_CURVE_WHEEL_PK (object id 35844)
?
Elapsed times include waiting on following events:
? Event waited on???????????????????????????? Times?? Max. Wait? Total Waited
? ----------------------------------------?? Waited? ----------? ------------
? SQL*Net message to client?????????????????????? 4??????? 0.00????????? 0.00
? SQL*Net message from client???????????????????? 4????? 494.34??????? 501.34
********************************************************************************
一些重要的信息: consistent read值為6， 相比以前的26067(PK)/19945(index)，fetch時間<0.01秒，相比3.84s(PK)/8.84s(index)。訪問索引的方法為INDEX FULL SCAN (MIN/MAX)。這是oracle文檔庫里沒有提到的訪問方法，但是
http://www.juliandyke.com/Optimisation/Operations/IndexFullScanMinMax.html
有一些介紹：Returns the first or last entry in the index。
?
看來oracle對于單個的min(),max()函數，能直接訪問索引的最左邊或者最右邊取到結果，但是如果兩個函數同時出現在一個sql里，oracle就只能掃描整個索引。這一點上還是不夠智能。
=============================================================================================
?
?