[20151221]sql語句優化.txt

--自從發現開發亂用distinct以後，連結http://blog.itpub.net/267265/viewspace-1871989/

--我看sql語句特别注意連接配接多個表,但是顯示僅僅一個表的情況,上個星期五，發現一條：

sql_id=dpdk3xfd6cvky

SELECT EMR_DJMX.ZSFL

FROM MS_YJ01, L_LIS_SQDMX, EMR_DJMX

WHERE MS_YJ01.YJXH IN ( :"SYS_B_00"

, :"SYS_B_01"

, :"SYS_B_02"

, :"SYS_B_03"

, :"SYS_B_04"

, :"SYS_B_05"

, :"SYS_B_06"

, :"SYS_B_07"

, :"SYS_B_08"

, :"SYS_B_09"

, :"SYS_B_10"

, :"SYS_B_11"

, :"SYS_B_12"

, :"SYS_B_13"

, :"SYS_B_14"

, :"SYS_B_15"

, :"SYS_B_16"

, :"SYS_B_17"

, :"SYS_B_18"

, :"SYS_B_19")

AND MS_YJ01.KDRQ >= TO_DATE ( :"SYS_B_20", :"SYS_B_21")

AND EMR_DJMX.ZSFL IS NOT NULL

AND EMR_DJMX.ZSFL <> :"SYS_B_22"

AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID

AND MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO

AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = :"SYS_B_23")

AND MS_YJ01.ZFPB = :"SYS_B_24"

AND MS_YJ01.ZXPB = :"SYS_B_25"

GROUP BY EMR_DJMX.ZSFL

UNION ALL

FROM (SELECT DISTINCT MS_YJ01.sqid

FROM ms_yj01

WHERE MS_YJ01.YJXH IN ( :"SYS_B_26"

, :"SYS_B_27"

, :"SYS_B_28"

, :"SYS_B_29"

, :"SYS_B_30"

, :"SYS_B_31"

, :"SYS_B_32"

, :"SYS_B_33"

, :"SYS_B_34"

, :"SYS_B_35"

, :"SYS_B_36"

, :"SYS_B_37"

, :"SYS_B_38"

, :"SYS_B_39"

, :"SYS_B_40"

, :"SYS_B_41"

, :"SYS_B_42"

, :"SYS_B_43"

, :"SYS_B_44"

, :"SYS_B_45")

AND MS_YJ01.KDRQ >= TO_DATE ( :"SYS_B_46", :"SYS_B_47")

AND MS_YJ01.ZFPB = :"SYS_B_48"

AND MS_YJ01.ZXPB = :"SYS_B_49"

AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = :"SYS_B_50"))

,L_LIS_SQDMX

,EMR_DJMX

WHERE EMR_DJMX.ZSFL = :"SYS_B_51"

AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID

AND SQID = L_LIS_SQDMX.DOCTREQUESTNO;

--僅僅關注union all上部分，下面一段也一樣。為了測試友善，我帶入參數改寫如下：

SELECT EMR_DJMX.ZSFL

FROM MS_YJ01,

L_LIS_SQDMX,

EMR_DJMX

WHERE MS_YJ01.YJXH in ('10591737','10591736') and

MS_YJ01.KDRQ >= TO_DATE('19-12-2015 00:00:00','DD-MM-YYYY HH24:MI:SS') AND

EMR_DJMX.ZSFL is not null AND

EMR_DJMX.ZSFL <>9 AND

EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID AND

MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO AND

( MS_YJ01.FPHM is null or MS_YJ01.FPHM = 'NULL' ) and

MS_YJ01.ZFPB = 0 and

MS_YJ01.ZXPB = 0

group by EMR_DJMX.ZSFL;

--in 我僅僅寫兩個。

--這個明明是exists的經典應用案例，開發為了避免重複，破天荒的使用group by(這次沒有使用distinct^_^) 。正确的寫法如下：

FROM EMR_DJMX

WHERE EXISTS

(SELECT 1

FROM MS_YJ01, L_LIS_SQDMX

WHERE MS_YJ01.YJXH IN ('10591737', '10591736')

AND MS_YJ01.KDRQ >=

TO_DATE

(

'19-12-2015 00:00:00'

,'DD-MM-YYYY HH24:MI:SS'

)

AND EMR_DJMX.XMID = L_LIS_SQDMX.PREHYID

AND MS_YJ01.SQID = L_LIS_SQDMX.DOCTREQUESTNO

AND (MS_YJ01.FPHM IS NULL OR MS_YJ01.FPHM = 'NULL')

AND MS_YJ01.ZFPB = 0

AND MS_YJ01.ZXPB = 0)

AND EMR_DJMX.ZSFL IS NOT NULL

AND EMR_DJMX.ZSFL <> 9 ;

--但是改寫後執行計劃如下：(補充也許可能出現重複，加入distinct)

Plan hash value: 2311536074

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

| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 0 |00:00:00.01 | 7108 | | | |

| 1 | HASH GROUP BY | | 1 | 1 | 51 | 11 (10)| 00:00:01 | 0 |00:00:00.01 | 7108 | 2053K| 2053K| |

| 2 | NESTED LOOPS | | 1 | 1 | 51 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |

| 3 | NESTED LOOPS | | 1 | 1 | 51 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |

| 4 | NESTED LOOPS | | 1 | 1 | 44 | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |

|* 5 | TABLE ACCESS BY INDEX ROWID| MS_YJ01 | 1 | 1 | 33 | 5 (0)| 00:00:01 | 0 |00:00:00.01 | 7108 | | | |

|* 6 | INDEX RANGE SCAN | IDX_MS_YJ01_KDRQ | 1 | 2 | | 3 (0)| 00:00:01 | 12647 |00:00:00.01 | 104 | 1025K| 1025K| |

| 7 | TABLE ACCESS BY INDEX ROWID| L_LIS_SQDMX | 0 | 5 | 55 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

|* 8 | INDEX RANGE SCAN | IF_L_LIS_SQDMX_DOCREQ_NO | 0 | 5 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

|* 9 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

|* 10 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

5 - SEL$1 / MS_YJ01@SEL$1

6 - SEL$1 / MS_YJ01@SEL$1

7 - SEL$1 / L_LIS_SQDMX@SEL$1

8 - SEL$1 / L_LIS_SQDMX@SEL$1

9 - SEL$1 / EMR_DJMX@SEL$1

10 - SEL$1 / EMR_DJMX@SEL$1

Predicate Information (identified by operation id):

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

5 - filter(("MS_YJ01"."SQID" IS NOT NULL AND ("MS_YJ01"."FPHM" IS NULL OR "MS_YJ01"."FPHM"='NULL') AND "MS_YJ01"."ZFPB"=0 AND "MS_YJ01"."ZXPB"=0 AND

INTERNAL_FUNCTION("MS_YJ01"."YJXH")))

6 - access("MS_YJ01"."KDRQ">=TO_DATE(' 2015-12-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

8 - access("MS_YJ01"."SQID"="L_LIS_SQDMX"."SYS_NC00021$")

9 - access("EMR_DJMX"."XMID"="L_LIS_SQDMX"."PREHYID")

10 - filter(("EMR_DJMX"."ZSFL" IS NOT NULL AND "EMR_DJMX"."ZSFL"<>9))

--邏輯讀并沒有明顯減少。我才發現oracle錯誤了選擇了索引IDX_MS_YJ01_KDRQ。實際上"MS_YJ01"."YJXH"是主鍵，應該選擇這個索引。

--問題在于這個表很長時間沒有分析(2015/11/23号分析過1次)，

--oracle錯誤的認為("MS_YJ01"."KDRQ">=TO_DATE(' 2015-12-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))的記錄很少。選擇該索引。

--重新分析後問題執行如下：

Plan hash value: 2730452773

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

| 0 | SELECT STATEMENT | | 1 | | | 12 (100)| | 0 |00:00:00.01 | 7 | | | |

| 1 | NESTED LOOPS | | 1 | 1 | 20 | 12 (9)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |

| 2 | NESTED LOOPS | | 1 | 1 | 20 | 12 (9)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |

| 3 | VIEW | VW_SQ_1 | 1 | 1 | 13 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |

| 4 | HASH UNIQUE | | 1 | 1 | 44 | | | 0 |00:00:00.01 | 7 | 2124K| 2124K| |

| 5 | NESTED LOOPS | | 1 | 1 | 44 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |

| 6 | INLIST ITERATOR | | 1 | | | | | 0 |00:00:00.01 | 7 | | | |

|* 7 | TABLE ACCESS BY INDEX ROWID| MS_YJ01 | 2 | 1 | 33 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 7 | | | |

|* 8 | INDEX UNIQUE SCAN | PK_MS_YJ01 | 2 | 2 | | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 6 | 1025K| 1025K| |

| 9 | TABLE ACCESS BY INDEX ROWID | L_LIS_SQDMX | 0 | 5 | 55 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

|* 10 | INDEX RANGE SCAN | IF_L_LIS_SQDMX_DOCREQ_NO | 0 | 5 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

|* 11 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |

|* 12 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 0 | 1 | 7 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |

--像這種語句優化存在一定的不穩定性，我聽過一些人建議删除列MS_YJ01.KDRQ的統計資訊。

--随手建立一個腳本清除日期字段資訊：

$ cat del_date_stat.sql

prompt del_date_stat.sql owner table_name column_name

DECLARE

distcnt NUMBER;

density NUMBER;

nullcnt NUMBER;

srec DBMS_STATS.statrec;

avgclen NUMBER;

BEGIN

DBMS_STATS.get_column_stats

(

ownname => '&&1'

,tabname => '&&2'

,colname => '&&3'

,distcnt => distcnt

,density => density

,nullcnt => nullcnt

,srec => srec

,avgclen => avgclen

);

DBMS_STATS.delete_column_stats

DBMS_STATS.set_column_stats

,srec => NULL

END;

--通過以上處理後發現邏輯讀明顯下降到XX。即使in裡面很多邏輯讀最大180.(而原來基本3XXXX多)

[email protected]:1521/dbcn> @tab_lh portal_his MS_YJ01 KDRQ

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.

INPUT OWNER TABLE_NAME COLUMN

SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]

IF NOT INPUT COLUMN_NAME ,USE "" .

COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT

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

KDRQ DATE 7 Y 4620980 2.1640E-07 7871866 2012-06-22 01:20:31 2015-12-21 08:43:36 0 1 2015-12-21 08:45:45 NONE

1 row selected.^_^!!!

--按照上面清除日期的最大與最小值後如下:

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

KDRQ DATE 7 Y 4620980 2.1640E-07 -- :: -- :: 0 1 2015-12-21 09:21:15 NONE

--以後如何控制呢？自己還給仔細考慮，不過開發确實在編寫sql語句上存在問題，我基本可以猜測把需要的表列出來寫出連接配接條件，發現

--重複，不是distinct，就是使用group by避免重複。難道不會用exists嗎？

--我自己也在思考這個問題，如果我沒有即使發現，團隊的其他人應該發現這種情況，我已經發現開發存在這個毛病，人家寫好的語句，

--根本不考慮合理不合理，直接借用過來。

--我沒什麼沒及時發現？我給自己的理由是我自己出現點總是從第3方的角度考慮優化，這些不外乎建立索引，使用sql profile以及spm

--來穩定執行計劃。而沒有上來考慮sql語句的執行效率與算法，有點失策。。。

[20151221]sql語句優化.txt

繼續閱讀

windows不能在本地計算機上運作oracleDbConsoleorcl

241 Different Ways to Add Parentheses（C代碼版）

【趨高機器視覺】機器視覺技術原了解析及解決方案

關于SQL語言

SQL語言基礎：常用的資料查詢語句

CSMA/CD1． CSMA/CD的概述2． CSMA 的工作原理3． CSMA/CD控制規程及特點4． CSMA/CD協定5． CSMA/CD的優點6．結束語

極大似然法(ML)與最大期望法(EM)

C++ 第十五周報告1--《冒泡法排序》

筆試面試題目：滑動視窗(二)

資料結構與算法（27）——排序（二）

neo4j之cypher使用文檔

Dijkstra--簡易版（最短路徑）

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List

hdu7108哈希