[20140210]一條sql語句的優化(11g).txt
[20140210]一條sql語句的優化(11g).txt
今天下午看生產系統資料庫，無意中發現一個錯誤，同時優化也有點小問題，寫一個測試指令碼。
1.建立測試環境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t pctfree 99 as
select rownum id ,mod(rownum,200) idx,trunc(sysdate)-dbms_random.value(0,200) cr_date ,rpad(rownum,1000,'x') vc from dual connect by level<=1e4;
create unique index pk_t on t (id);
create index i_t_idx on t(idx);
create index i_t_cr_date on t(cr_date);
exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true) ;
2.有問題語句：
SCOTT@test> column vc noprint
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42);
ID IDX CR_DATE
---------- ---------- -------------------
7842 42 2014-02-06 09:57:35
--很明顯這樣查詢結果目前是正確的，但是實際上如果我修改如下：
update t set cr_date='2014-02-06 09:57:35' where id=1e4;
commit ;
--注意我這裡取了一個巧，直接使用cr_date='2014-02-06 09:57:35',只要nls*相關引數正確，一般沒有問題。
--再次執行：
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42);
ID IDX CR_DATE
---------- ---------- -------------------
7842 42 2014-02-06 09:57:35
10000 0 2014-02-06 09:57:35
--很明顯，開發需要是IDX=42的記錄，而不需要第2條。只不過日期相重的概率很少罷了。BTW，我已經不止一次發現這樣類似的錯誤。
--至少這樣寫才正確：
SCOTT@test> alter session set statistics_level=all;
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42) and idx=42;
ID IDX CR_DATE
---------- ---------- -------------------
7842 42 2014-02-06 09:57:35
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 77hz50d71h2s9, child number 0
-------------------------------------
select * from t where cr_date = ( select max(cr_date) from t where
idx=42) and idx=42
Plan hash value: 2661465193
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 32 (100)| 1 |00:00:00.06 | 39 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 2 (0)| 1 |00:00:00.06 | 39 | | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | 1 |00:00:00.06 | 38 | | | |
| 3 | BITMAP AND | | 1 | | | 1 |00:00:00.06 | 38 | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | 1 |00:00:00.06 | 36 | | | |
|* 5 | INDEX RANGE SCAN | I_T_CR_DATE | 1 | 1 | 1 (0)| 2 |00:00:00.04 | 36 | | | |
| 6 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.04 | 34 | | | |
|* 7 | VIEW | index$_join$_002 | 1 | 50 | 30 (4)| 50 |00:00:00.04 | 34 | | | |
|* 8 | HASH JOIN | | 1 | | | 50 |00:00:00.04 | 34 | 1096K| 1096K| 1580K (0)|
|* 9 | INDEX RANGE SCAN | I_T_IDX | 1 | 50 | 1 (0)| 50 |00:00:00.01 | 2 | | | |
| 10 | INDEX FAST FULL SCAN | I_T_CR_DATE | 1 | 50 | 35 (0)| 10000 |00:00:00.01 | 32 | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | 1 |00:00:00.01 | 2 | | | |
|* 12 | INDEX RANGE SCAN | I_T_IDX | 1 | 1 | 1 (0)| 50 |00:00:00.01 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CR_DATE"=)
7 - filter("IDX"=42)
8 - access(ROWID=ROWID)
9 - access("IDX"=42)
12 - access("IDX"=42)
--這樣寫，執行計劃看上去很複雜。邏輯讀39，實際上我的索引很小，生產系統不會選擇INDEX FAST FULL SCAN I_T_CR_DATE 。
--生產系統實際上是這樣
SCOTT@test> select /*+ index(t i_t_idx) */ * from t where cr_date = ( select /*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42;
ID IDX CR_DATE
---------- ---------- -------------------
7842 42 2014-02-06 09:57:35
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 60p18ha8myc2j, child number 0
-------------------------------------
select /*+ index(t i_t_idx) */ * from t where cr_date = ( select /*+
index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42
Plan hash value: 1551695814
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 102 (100)| 1 |00:00:00.01 | 105 | 59 |
|* 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 51 (0)| 1 |00:00:00.01 | 105 | 59 |
|* 2 | INDEX RANGE SCAN | I_T_IDX | 1 | 50 | 1 (0)| 50 |00:00:00.01 | 3 | 0 |
| 3 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 52 | 58 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 50 | 51 (0)| 50 |00:00:00.01 | 52 | 58 |
|* 5 | INDEX RANGE SCAN | I_T_IDX | 1 | 50 | 1 (0)| 50 |00:00:00.01 | 2 | 0 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CR_DATE"=)
2 - access("IDX"=42)
5 - access("IDX"=42)
--很明顯這樣邏輯讀有點高，到達105.主要的問題要使用索引範圍掃描2次(I_T_IDX).
==更正如下：實際這樣執行：
SCOTT@test> select /*+ index(t i_t_cr_date) */ * from t where cr_date = ( select /*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a8kx1xu0c7cmh, child number 1
-------------------------------------
select /*+ index(t i_t_cr_date) */ * from t where cr_date = ( select
/*+ index(t i_t_idx) */ max(cr_date) from t where idx=42) and idx=42
Plan hash value: 2838288168
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 54 (100)| 1 |00:00:00.01 | 57 | 181 |
|* 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 57 | 181 |
|* 2 | INDEX RANGE SCAN | I_T_CR_DATE | 1 | 1 | 1 (0)| 2 |00:00:00.01 | 55 | 180 |
| 3 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 52 | 164 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 50 | 51 (0)| 50 |00:00:00.01 | 52 | 164 |
|* 5 | INDEX RANGE SCAN | I_T_IDX | 1 | 50 | 1 (0)| 50 |00:00:00.01 | 2 | 16 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("IDX"=42)
2 - access("CR_DATE"=)
5 - access("IDX"=42)
===========================
select * from t where (idx,cr_date ) in ( select idx, max(cr_date) from t where idx=42 group by idx ) ;
--也不是很好。
select * from (select * from t where idx=42 order by cr_date desc) where rownum<=1 ;
--建立索引
create index i_t_idx_cr_date on t(idx,cr_date);
SCOTT@test> select * from t where cr_date = ( select max(cr_date) from t where idx=42) and idx=42;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 77hz50d71h2s9, child number 0
-------------------------------------
select * from t where cr_date = ( select max(cr_date) from t where
idx=42) and idx=42
Plan hash value: 3430031104
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 1 |00:00:00.01 | 6 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 6 | 1 |
|* 2 | INDEX RANGE SCAN | I_T_IDX_CR_DATE | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 5 | 1 |
| 3 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 2 | 1 |
| 4 | FIRST ROW | | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 | 1 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| I_T_IDX_CR_DATE | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IDX"=42 AND "CR_DATE"=)
5 - access("IDX"=42)
SCOTT@test> select * from (select * from t where idx=42 order by cr_date desc) where rownum<=1 ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 19pf4scday9pv, child number 0
-------------------------------------
select * from (select * from t where idx=42 order by cr_date desc)
where rownum<=1
Plan hash value: 2332835607
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 1 |00:00:00.01 | 3 |
|* 1 | COUNT STOPKEY | | 1 | | | 1 |00:00:00.01 | 3 |
| 2 | VIEW | | 1 | 2 | 4 (0)| 1 |00:00:00.01 | 3 |
| 3 | TABLE ACCESS BY INDEX ROWID | T | 1 | 50 | 4 (0)| 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_T_IDX_CR_DATE | 1 | 2 | 2 (0)| 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
4 - access("IDX"=42)
--這樣寫最好，當然前提是不會返回多行，好像實際不會出現這種情況。
--這樣邏輯讀確實下降不少，但是不是我需要，因為建立的索引有些冗餘了。
drop index i_t_idx_cr_date ;
SCOTT@test> select * from (select * from t where idx=42 order by cr_date desc) where rownum<=1 ;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 19pf4scday9pv, child number 0
-------------------------------------
select * from (select * from t where idx=42 order by cr_date desc)
where rownum<=1
Plan hash value: 587900075
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 52 (100)| 1 |00:00:00.02 | 52 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | 1 |00:00:00.02 | 52 | | | |
| 2 | VIEW | | 1 | 50 | 52 (2)| 1 |00:00:00.02 | 52 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 50 | 52 (2)| 1 |00:00:00.02 | 52 | 73728 | 73728 | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 50 | 51 (0)| 50 |00:00:00.02 | 52 | | | |
|* 5 | INDEX RANGE SCAN | I_T_IDX | 1 | 50 | 1 (0)| 50 |00:00:00.01 | 2 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - filter(ROWNUM<=1)
5 - access("IDX"=42)
--這樣僅僅掃描i_t_idx一次。比原來邏輯讀少一半。
總結：
1.修改語句如下：
select * from (select * from t where idx=42 order by cr_date desc) where rownum<=1 ;
2.比較好的解決方式是建立idx，cr_date的複合索引。刪除idx的索引。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1079340/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條sql語句的優化SQL優化
- 一條SQL語句的優化過程SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- [20151209]一條sql語句的優化(續).txtSQL優化
- SQL語句優化－－十條經驗SQL優化
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 一個SQL語句的優化SQL優化
- 一條update語句的優化探索優化
- SQL語句的優化SQL優化
- 一條sql語句的建議調優分析SQL
- 一條簡單的SQL語句優化-新年新氣象SQL優化
- SQL語句優化SQL優化
- 總結出10條SQL語句優化精髓SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- SQL 語句的優化方法SQL優化
- 一次sql語句優化的反思SQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 求助：SQL語句優化SQL優化
- [20170104]一條sql優化.txtSQL優化
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條SQL語句的旅行之路SQL
- SQL語句優化的34條建議(轉-浪花七八朵 )SQL優化
- 優化 SQL 語句的步驟優化SQL
- 關於sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20201210]sql語句優化.txtSQL優化
- MySQL之SQL語句優化MySql優化