[20181114]一条sql语句的优化.txt

来源：这里教程网时间：2026-03-03 12:13:16 作者：

[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 ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------- | 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 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------ | 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的记录,也不需要取出全部相关记录.大部分前面几条基本满足需求(一般病人可能一天挂几个科室医生的号). --//直接返回了.这样查询基本不会有物理读. --//实际上这类的错误在开发中反反复复,这么就一点不长进呢?可悲可叹...