[20181114]一條sql語句的優化.txt

--//很久不看生產系統的sql語句,看這些東西心情會很不好,昨天看了一條sql語句.

--//這類錯誤很常見,自己寫出來:

1.環境:

SYSTEM@192.168.31.8:1521/hrp430> @ ver1

PORT_STRING VERSION BANNER

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

x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2.抽取語句生成執行指令碼如下:

--//生成執行指令碼如下:

$ cat 4vdargkxgmjqt.sql4

variable N1 NUMBER

variable N2 VARCHAR2(32)

variable N3 VARCHAR2(32)

variable SYS_B_0 VARCHAR2(32)

variable SYS_B_1 NUMBER

variable N4 NUMBER

variable N5 VARCHAR2(32)

variable N6 VARCHAR2(32)

variable SYS_B_2 VARCHAR2(32)

variable SYS_B_3 NUMBER

begin

:N1 := 18737588;

:N2 := '121';

:N3 := 'NULL';

:SYS_B_0 := '3865';

:SYS_B_1 := 0;

:N4 := 18737588;

:N5 := '121';

:N6 := 'NULL';

:SYS_B_2 := '3865';

:SYS_B_3 := 0;

end;

/

set termout off

set sqlblanklines on

alter session set current_schema=XXXXXX_YYY;

alter session set statistics_level=all;

SELECT /* test 4vdargkxgmjqt */

/*+ gather_plan_statistics */

SBXH

FROM MS_GHMX

WHERE (SELECT MAX (ghsj)

FROM ms_ghmx

WHERE BRID = :N1

AND (KSDM = :N2 OR KSDM = :N3 OR YSDM = :"SYS_B_0")

AND THBZ = :"SYS_B_1") = ghsj

AND BRID = :N4

AND (KSDM = :N5 OR KSDM = :N6 OR YSDM = :"SYS_B_2")

AND THBZ = :"SYS_B_3";

set termout on

set sqlblanklines off

@dpc '' ''

rollback;

Plan hash value: 4229624801

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

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

| 0 | SELECT STATEMENT | | 1 | | | 13 (100)| | 1 |00:00:00.01 | 40 |

|* 1 | TABLE ACCESS BY INDEX ROWID | MS_GHMX | 1 | 1 | 32 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 40 |

|* 2 | INDEX RANGE SCAN | I_MS_GHMX_GHSJ_YS_JZ | 1 | 1 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 39 |

| 3 | SORT AGGREGATE | | 1 | 1 | 26 | | | 1 |00:00:00.01 | 35 |

|* 4 | TABLE ACCESS BY INDEX ROWID| MS_GHMX | 1 | 1 | 26 | 9 (0)| 00:00:01 | 3 |00:00:00.01 | 35 |

|* 5 | INDEX RANGE SCAN | I_MS_GHMX_BRID_GHSJ | 1 | 6 | | 3 (0)| 00:00:01 | 41 |00:00:00.01 | 3 |

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

--//id=4,邏輯讀35,而且因為brid表示病人ID,這樣表ms_ghmx(掛號明細表)裡面的資訊關於這個欄位記錄的全部查詢出來,導致累積存在大量的物理讀.

--//當然我這裡執行多次,已經沒有物理讀.你想像一下假設一個老病號這樣來醫院看病,在這個表中記錄上百次一點不奇怪.我當前病人有41次記錄.

--//實際上查詢僅僅滿足條件的ghsj(掛號時間)最大的記錄,根本不需要遍歷相關記錄.

SELECT sbxh

FROM ( SELECT brid, ghsj, SBXH

FROM ms_ghmx

WHERE BRID = :N1

AND (KSDM = :N2 OR KSDM = :N3 OR YSDM = :"SYS_B_0")

AND THBZ = :"SYS_B_1"

ORDER BY ghsj DESC)

WHERE ROWNUM = 1;

Plan hash value: 1401260886

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

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

| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 1 |00:00:00.01 | 4 |

|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 4 |

| 2 | VIEW | | 1 | 1 | 13 | 9 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 3 | TABLE ACCESS BY INDEX ROWID | MS_GHMX | 1 | 1 | 32 | 9 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |

|* 4 | INDEX RANGE SCAN DESCENDING| I_MS_GHMX_BRID_GHSJ | 1 | 6 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |

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

--//你可以發現這樣查詢根本不需要查詢全部brid=:N1的記錄,也不需要取出全部相關記錄.大部分前面幾條基本滿足需求(一般病人可能一天掛幾個科室醫生的號).

--//直接返回了.這樣查詢基本不會有物理讀.

--//實際上這類的錯誤在開發中反反覆覆,這麼就一點不長進呢?可悲可嘆...

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2219892/，如需轉載，請註明出處，否則將追究法律責任。

[20181114]一條sql語句的優化.txt

相關文章