[20120131]函式索引與取max值的問題2.txt
1.建立測試例子:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM <= 99900
THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL <= 100000;
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.執行測試命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
同樣情況與10.2.0.3相同!如果access 沒有使用函式,執行計劃可以走INDEX RANGE SCAN (MIN/MAX),而如果查詢使用nvl(flag,'1')='1',執行計劃是INDEX RANGE SCAN.
3.做10053跟蹤(注意為了要再次硬分析,我修改max=>Max)
--忽略.cost=2也說明分析成本的時候是走INDEX RANGE SCAN (MIN/MAX),而實際的執行計劃是INDEX RANGE SCAN.
如果查詢使用count(id),也能說明問題,在這樣的情況下,同樣走INDEX RANGE SCAN,cost=63.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 63 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 63 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
4.在10g下如何最佳化上述sql語句呢?可以把索引反過來建立.
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
--情況依舊,刪除索引在測試if_t_flag_id.(或者使用hint).
SQL> drop index if_t_flag_id;
Index dropped.
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50036 | 342K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50036 | 342K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--對比10.2.0.3可以發現:
--使用索引IF_T_ID_FLAG,但是走的是INDEX FULL SCAN (MIN/MAX),實際上這個查詢是從id的最大值開始只要滿足nvl(flag,'1')='1'的條件停止.
--如果nvl(flag,'1')='1'的最大id很小,以上情況最壞的是掃描整個索引.
update t set flag='0' where id>=11;
commit;
SQL> set autot traceonly
SQL> select /*+ gather_plan_statistics index(t,if_t_id_flag) */ max(id) from t where nvl(flag,'1')='1';
執行計劃
----------------------------------------------------------
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("FLAG",'1')='1')
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
0 physical reads
9220 redo size
335 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以發現邏輯讀很多達到了378. 看來這個方法僅僅在滿足條件nvl(flag,'1')='1'最大id很大的情況下比較好.
--當然如果應該經常查詢id=:b 的情況下,並且查詢以上max(id)的邏輯讀很小,不失為一個好的選擇.
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM <= 99900
THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL <= 100000;
create index if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.執行測試命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
20 rows selected.
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3307844215
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
同樣情況與10.2.0.3相同!如果access 沒有使用函式,執行計劃可以走INDEX RANGE SCAN (MIN/MAX),而如果查詢使用nvl(flag,'1')='1',執行計劃是INDEX RANGE SCAN.
3.做10053跟蹤(注意為了要再次硬分析,我修改max=>Max)
--忽略.cost=2也說明分析成本的時候是走INDEX RANGE SCAN (MIN/MAX),而實際的執行計劃是INDEX RANGE SCAN.
如果查詢使用count(id),也能說明問題,在這樣的情況下,同樣走INDEX RANGE SCAN,cost=63.
SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';
COUNT(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 63 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 63 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
4.在10g下如何最佳化上述sql語句呢?可以把索引反過來建立.
create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'
Plan hash value: 2113784624
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 | 341K| 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='1')
--情況依舊,刪除索引在測試if_t_flag_id.(或者使用hint).
SQL> drop index if_t_flag_id;
Index dropped.
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50036 | 342K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50036 | 342K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."SYS_NC00004$"='1')
--對比10.2.0.3可以發現:
--使用索引IF_T_ID_FLAG,但是走的是INDEX FULL SCAN (MIN/MAX),實際上這個查詢是從id的最大值開始只要滿足nvl(flag,'1')='1'的條件停止.
--如果nvl(flag,'1')='1'的最大id很小,以上情況最壞的是掃描整個索引.
update t set flag='0' where id>=11;
commit;
SQL> set autot traceonly
SQL> select /*+ gather_plan_statistics index(t,if_t_id_flag) */ max(id) from t where nvl(flag,'1')='1';
執行計劃
----------------------------------------------------------
Plan hash value: 2133598614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 50000 | 341K| 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50000 | 341K| 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL("FLAG",'1')='1')
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
378 consistent gets
0 physical reads
9220 redo size
335 bytes sent via SQL*Net to client
346 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以發現邏輯讀很多達到了378. 看來這個方法僅僅在滿足條件nvl(flag,'1')='1'最大id很大的情況下比較好.
--當然如果應該經常查詢id=:b 的情況下,並且查詢以上max(id)的邏輯讀很小,不失為一個好的選擇.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-715314/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20120131]函式索引與取max值的問題3.txt函式索引
- [20120130]函式索引與取max值的問題1.txt函式索引
- [20120201][補充]函式索引與取max值的問題1.txt函式索引
- 函式索引的問題函式索引
- 二元函式的極值與最值問題函式
- 關於函式索引的問題?函式索引
- 0427建立Extended Statistics函式索引問題函式索引
- 0429建立Extended Statistics函式索引問題函式索引
- 利用函式索引解決複雜的約束問題函式索引
- 函式呼叫引數變數傳值的問題函式變數
- 取石子的幾個找sg函式的問題函式
- 練習 讀取函式裡的值函式
- [20180509]函式索引問題.txt函式索引
- dev_queue_xmit()函式返回值問題devMIT函式
- jquery的get()函式快取問題解決方案jQuery函式快取
- js中 函式引數的 傳值/傳引用 問題JS函式
- javascript建構函式的返回值問題介紹JavaScript函式
- jQuery獲取CSS樣式中的顏色值的問題jQueryCSS
- 【Analytic】分析函式之MAX函式函式
- [20180212]函式索引問題.txt函式索引
- 建立聯合函式索引解決top sql效能問題函式索引SQL
- （求教）關於js函式按值、引用傳遞的問題JS函式
- 函式索引使用細節——自定義函式的索引化函式索引
- ORACLE單行函式與多行函式之三：數值函式Oracle函式
- [20150803]使用函式索引注意的問題.txt函式索引
- Oracle-空值null和數字相加的問題-nvl函式OracleNull函式
- 函式呼叫棧的問題函式
- itoa函式的奇怪問題函式
- 複合索引與函式索引優化一例索引函式優化
- 關於Hash 函式 雜湊索引表 解決位置衝突的問題函式索引
- [20190918]關於函式索引問題.txt函式索引
- shell指令碼獲取函式返回值指令碼函式
- NULL 值與索引Null索引
- 基於函式的索引函式索引
- 函式索引的儲存函式索引
- 函式索引陷阱函式索引
- oracle函式索引Oracle函式索引
- MySQL中MAX函式如何使用？MySql函式