[20211220]关于标量子查询问题.txt

来源：这里教程网时间：2026-03-03 17:20:49 作者：

[20211220]关于标量子查询问题.txt --//上个星期发现一条翻页查询语句,感觉很奇怪出现在程序中.在看执行计划过程中,遇到标量子查询的starts很高的情况. --//自己展开做一个分析,顺便说一下优化这条语句毫无意义,学习与了解并优化标量子查询倒是可以作为案例. 1.环境： xxxxx> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.问题分析: --//注语句很长，我仅仅显示where条件部分,原始代码很乱,而且我很佩服写代码的人很长的代码就一行没带换行的。我做了一些格式化 --//处理，里面还有7处标量子查询。 --//sql_id=367u7411u9krd. SELECT * FROM (SELECT ROWNUM AS NO ,A.ZYH WATENUMBER ,B.MZHM BRID ... , (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.KZYS) DOCTNAME ... , (SELECT DMMC FROM GY_XTPZ WHERE DMLB = :"SYS_B_13" AND PZBH = A.YZZT AND DMSB <> :"SYS_B_14") THESTATE ... , :"SYS_B_21" RESERVE3 FROM EMR_YZB A, ZY_BRRY B WHERE A.ZYH = B.ZYH AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23") AND ROWNUM < :"SYS_B_24") WHERE NO >= :"SYS_B_25"; --//很简单的翻页语句。不过已经翻到814页。 --//获得绑定变量值: xxxxx> @ bind_cap 367u7411u9krd '' SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- ---------- -------- ---------- ------------------- --------------- ------------ 367u7411u9krd 0 YES :SYS_B_09 10 22 2021-12-17 02:00:35 NUMBER 301 YES :SYS_B_10 11 22 2021-12-17 02:00:35 NUMBER 0 YES :SYS_B_13 14 22 2021-12-17 02:00:35 NUMBER 302 YES :SYS_B_14 15 22 2021-12-17 02:00:35 NUMBER 0 YES :SYS_B_22 23 22 2021-12-17 02:00:35 NUMBER 0 YES :SYS_B_23 24 22 2021-12-17 02:00:35 NUMBER 1 YES :SYS_B_24 25 22 2021-12-17 02:00:35 NUMBER 814000 YES :SYS_B_25 26 22 2021-12-17 02:00:35 NUMBER 813000 8 rows selected. --//执行计划如下: Plan hash value: 2094450556 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:59.21 | 481K| 43608 | | | | | 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 1076 | 1 | 21 | 2 (0)| 00:00:01 | 1076 |00:00:00.01 | 1365 | 0 | | | | |* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 1076 | 1 | | 1 (0)| 00:00:01 | 1076 |00:00:00.01 | 272 | 0 | 1025K| 1025K| | | 3 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 40517 | 1 | 12 | 2 (0)| 00:00:01 | 40517 |00:00:00.22 | 82323 | 0 | | | | |* 4 | INDEX UNIQUE SCAN | PK_GY_YGDM | 40517 | 1 | | 1 (0)| 00:00:01 | 40517 |00:00:00.13 | 39153 | 0 | 1025K| 1025K| | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |* 5 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | | |* 6 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 0 | 1025K| 1025K| | | 7 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1260 | 1 | 13 | 1 (0)| 00:00:01 | 1259 |00:00:00.01 | 1263 | 0 | | | | |* 8 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1260 | 1 | | 0 (0)| | 1259 |00:00:00.01 | 4 | 0 | 1025K| 1025K| | | 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | 0 | | | | |* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 0 | 1025K| 1025K| | | 11 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | 0 | | | | |* 12 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 0 | 1025K| 1025K| | | 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 13985 | 1 | 21 | 2 (0)| 00:00:01 | 7789 |00:00:00.05 | 15464 | 0 | | | | |* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 13985 | 1 | | 1 (0)| 00:00:01 | 7789 |00:00:00.02 | 4425 | 0 | 1025K| 1025K| | |* 15 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | | |* 16 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 0 | 1025K| 1025K| | |* 17 | VIEW | | 1 | 549K| 333M| 232K (1)| 00:46:30 | 1000 |00:00:59.21 | 481K| 43608 | | | | |* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:57.50 | 381K| 43608 | | | | | 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:57.39 | 381K| 43608 | | | | | 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.85 | 20382 | 487 | | | | | 21 | INLIST ITERATOR | | 1 | | | | | 5126 |00:00:00.01 | 2377 | 0 | | | | | 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5126 |00:00:00.01 | 2377 | 0 | | | | |* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5126 |00:00:00.01 | 119 | 0 | 1025K| 1025K| | |* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5126 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.75 | 18005 | 487 | 1025K| 1025K| | | 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:56.28 | 360K| 43121 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//看执行计划时,发现id=4的循环次数达到40517次,id=3的buffers数量达到82323,还有一个细节,最后buffers的数量实际上不算标量子 --//查询的buffers,甚至包括cost成本.我这里buffers已经481K,但是id=3的buffers达到8XK,明显没有计入. --//开始有点不理解,不就是取1000行吗?循环次数怎么能达到这个高,仔细一想很容易理解,因为先取814000记录然后截取后面的1000条, --//这样标量子查询的循环次数达到这么高就很正常了,因为存在大量的hash table冲突. 3.优化方案1: --//如果不修改代码,增加hash table缓存的大小,就可以减少hash冲突. SYS@192.168.99.46:1521/dyhis> @ hide _query_execution_cache_max_size NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------------------- --------------------------------- ------------- ------------- ------------ ----- --------- _query_execution_cache_max_size max size of query execution cache TRUE 131072 131072 TRUE DEFERRED --//我的测试标量子查询的返回的是字符串YGXM,数据类型定义为,我估计能保存1024,增加3倍看看. --//131072 * 3 = 393216 xxxxx> @ calc 131072 * 3 DEC HEX ----------------------------------- -------------------- 393216.000000 60000 xxxxx> alter session set "_query_execution_cache_max_size"=393216; Session altered. Plan hash value: 2094450556 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:57.29 | 417K| 44359 | | | | | 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 367 | 1 | 21 | 2 (0)| 00:00:01 | 367 |00:00:00.01 | 578 | 0 | | | | |* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 367 | 1 | | 1 (0)| 00:00:01 | 367 |00:00:00.01 | 206 | 0 | 1025K| 1025K| | | 3 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 15893 | 1 | 12 | 2 (0)| 00:00:01 | 15893 |00:00:00.09 | 30972 | 0 | | | | |* 4 | INDEX UNIQUE SCAN | PK_GY_YGDM | 15893 | 1 | | 1 (0)| 00:00:01 | 15893 |00:00:00.06 | 14788 | 0 | 1025K| 1025K| | |* 5 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | | |* 6 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 0 | 1025K| 1025K| | | 7 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1270 | 1 | 13 | 1 (0)| 00:00:01 | 1269 |00:00:00.01 | 1275 | 0 | | | | |* 8 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1270 | 1 | | 0 (0)| | 1269 |00:00:00.01 | 6 | 0 | 1025K| 1025K| | | 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 53 | 1 | 8 | 1 (0)| 00:00:01 | 52 |00:00:00.01 | 56 | 0 | | | | |* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 53 | 1 | | 0 (0)| | 52 |00:00:00.01 | 4 | 0 | 1025K| 1025K| | | 11 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 73 | 1 | 13 | 1 (0)| 00:00:01 | 72 |00:00:00.01 | 76 | 0 | | | | |* 12 | INDEX UNIQUE SCAN | PK_GY_SYPC | 73 | 1 | | 0 (0)| | 72 |00:00:00.01 | 4 | 0 | 1025K| 1025K| | | 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 2255 | 1 | 21 | 2 (0)| 00:00:01 | 2196 |00:00:00.01 | 3651 | 0 | | | | |* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 2255 | 1 | | 1 (0)| 00:00:01 | 2196 |00:00:00.01 | 1406 | 0 | 1025K| 1025K| | |* 15 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | 0 | | | | |* 16 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 0 | 1025K| 1025K| | |* 17 | VIEW | | 1 | 549K| 414M| 232K (1)| 00:46:30 | 1000 |00:00:57.29 | 417K| 44359 | | | | |* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:55.67 | 380K| 44359 | | | | | 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:55.56 | 380K| 44359 | | | | | 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.95 | 20751 | 459 | | | | | 21 | INLIST ITERATOR | | 1 | | | | | 5188 |00:00:00.01 | 2489 | 0 | | | | | 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5188 |00:00:00.01 | 2489 | 0 | | | | |* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5188 |00:00:00.01 | 119 | 0 | 1025K| 1025K| | |* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5188 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.85 | 18262 | 459 | 1025K| 1025K| | | 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:54.36 | 360K| 43900 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//可以发现id=4的循环次数减少到15893,还是很不理想。 4.优化方案2: --//最容易想到的方法就是把标量子查询放在外层,这样取出1000条,然后在外层做标量子查询.修改如下测试看看. SELECT a.* , (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.DOCTCODE) DOCTNAME FROM (SELECT ROWNUM AS NO ,A.ZYH WATENUMBER ,B.MZHM BRID ,'00' SERIAL ,A.YZBXH DAID ,DECODE (YYSX, 00, '00', 00, '00', 00, '00', 00, '00') THETYPE ,A.KZKS DEPTCODE , (SELECT KSMC FROM GY_KSDM WHERE KSDM = A.KZKS) DEPTNAME ,A.KZYS DOCTCODE -- , (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.KZYS) DOCTNAME .... ... , :"SYS_B_21" RESERVE3 FROM EMR_YZB A, ZY_BRRY B WHERE A.ZYH = B.ZYH AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23") AND ROWNUM < :"SYS_B_24") a WHERE NO >= :"SYS_B_25"; Plan hash value: 2805972862 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:03.54 | 394K| | | | | 1 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 930 | 1 | 21 | 2 (0)| 00:00:01 | 930 |00:00:00.01 | 1210 | | | | |* 2 | INDEX UNIQUE SCAN | PK_GY_KSDM | 930 | 1 | | 1 (0)| 00:00:01 | 930 |00:00:00.01 | 263 | 1025K| 1025K| | |* 3 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | | |* 4 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 1025K| 1025K| | | 5 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1256 | 1 | 13 | 1 (0)| 00:00:01 | 1255 |00:00:00.01 | 1259 | | | | |* 6 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1256 | 1 | | 0 (0)| | 1255 |00:00:00.01 | 4 | 1025K| 1025K| | | 7 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | | |* 8 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| | | 9 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | | |* 10 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| | | 11 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 18135 | 1 | 21 | 2 (0)| 00:00:01 | 5578 |00:00:00.05 | 11399 | | | | |* 12 | INDEX UNIQUE SCAN | PK_GY_KSDM | 18135 | 1 | | 1 (0)| 00:00:01 | 5578 |00:00:00.03 | 4683 | 1025K| 1025K| | |* 13 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | | |* 14 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 1025K| 1025K| | | 15 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 62 | 1 | 12 | 2 (0)| 00:00:01 | 62 |00:00:00.01 | 138 | | | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |* 16 | INDEX UNIQUE SCAN | PK_GY_YGDM | 62 | 1 | | 1 (0)| 00:00:01 | 62 |00:00:00.01 | 73 | 1025K| 1025K| | |* 17 | VIEW | | 1 | 549K| 405M| 232K (1)| 00:46:30 | 1000 |00:00:03.54 | 394K| | | | |* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:02.10 | 380K| | | | | 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:01.99 | 380K| | | | | 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.35 | 20499 | | | | | 21 | INLIST ITERATOR | | 1 | | | | | 5140 |00:00:00.01 | 2435 | | | | | 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5140 |00:00:00.01 | 2435 | | | | |* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5140 |00:00:00.01 | 116 | 1025K| 1025K| | |* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5140 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.24 | 18064 | 1025K| 1025K| | | 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:01.40 | 359K| | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//如果我把id=12的出现也遇到外层。 SELECT a.* , (SELECT YGXM FROM GY_YGDM WHERE YGDM = A.DOCTCODE) DOCTNAME , (SELECT KSMC FROM GY_KSDM WHERE KSDM = A.DEPTCODE) DEPTNAME , (SELECT KSMC FROM GY_KSDM WHERE KSDM = A.EXECDEPTCODE) EXECDEPTNAME FROM (SELECT ROWNUM AS NO, .. ... , :"SYS_B_21" RESERVE3 FROM EMR_YZB A, ZY_BRRY B WHERE A.ZYH = B.ZYH AND B.CYPB IN ( :"SYS_B_22", :"SYS_B_23") AND ROWNUM < :"SYS_B_24") a WHERE NO >= :"SYS_B_25"; Plan hash value: 2236731721 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 232K(100)| | 1000 |00:00:02.88 | 381K| | | | |* 1 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | | |* 2 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 54 |00:00:00.01 | 8 | 1025K| 1025K| | | 3 | TABLE ACCESS BY INDEX ROWID | ZY_YPYF | 1254 | 1 | 13 | 1 (0)| 00:00:01 | 1253 |00:00:00.01 | 1257 | | | | |* 4 | INDEX UNIQUE SCAN | PK_ZY_YPYF | 1254 | 1 | | 0 (0)| | 1253 |00:00:00.01 | 4 | 1025K| 1025K| | | 5 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 8 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | | |* 6 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| | | 7 | TABLE ACCESS BY INDEX ROWID | GY_SYPC | 70 | 1 | 13 | 1 (0)| 00:00:01 | 69 |00:00:00.01 | 73 | | | | |* 8 | INDEX UNIQUE SCAN | PK_GY_SYPC | 70 | 1 | | 0 (0)| | 69 |00:00:00.01 | 4 | 1025K| 1025K| | |* 9 | TABLE ACCESS BY INDEX ROWID | GY_XTPZ | 6 | 1 | 19 | 2 (0)| 00:00:01 | 6 |00:00:00.01 | 9 | | | | |* 10 | INDEX RANGE SCAN | IDX_GY_XTPZ_DMLB | 6 | 5 | | 1 (0)| 00:00:01 | 42 |00:00:00.01 | 8 | 1025K| 1025K| | | 11 | TABLE ACCESS BY INDEX ROWID | GY_YGDM | 55 | 1 | 12 | 2 (0)| 00:00:01 | 55 |00:00:00.01 | 122 | | | | |* 12 | INDEX UNIQUE SCAN | PK_GY_YGDM | 55 | 1 | | 1 (0)| 00:00:01 | 55 |00:00:00.01 | 66 | 1025K| 1025K| | | 13 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 10 | 1 | 21 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 32 | | | | |* 14 | INDEX UNIQUE SCAN | PK_GY_KSDM | 10 | 1 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 19 | 1025K| 1025K| | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | 15 | TABLE ACCESS BY INDEX ROWID | GY_KSDM | 40 | 1 | 21 | 2 (0)| 00:00:01 | 31 |00:00:00.01 | 82 | | | | |* 16 | INDEX UNIQUE SCAN | PK_GY_KSDM | 40 | 1 | | 1 (0)| 00:00:01 | 31 |00:00:00.01 | 45 | 1025K| 1025K| | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |* 17 | VIEW | | 1 | 549K| 377M| 232K (1)| 00:46:30 | 1000 |00:00:02.88 | 381K| | | | |* 18 | COUNT STOPKEY | | 1 | | | | | 813K|00:00:01.88 | 380K| | | | | 19 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:01.79 | 380K| | | | | 20 | NESTED LOOPS | | 1 | 549K| 66M| 232K (1)| 00:46:30 | 813K|00:00:00.29 | 20504 | | | | | 21 | INLIST ITERATOR | | 1 | | | | | 5140 |00:00:00.01 | 2433 | | | | | 22 | TABLE ACCESS BY INDEX ROWID| ZY_BRRY | 2 | 4932 | 83844 | 419 (0)| 00:00:06 | 5140 |00:00:00.01 | 2433 | | | | |* 23 | INDEX RANGE SCAN | IDX_BRRY_CYPB | 2 | 4932 | | 18 (0)| 00:00:01 | 5140 |00:00:00.01 | 115 | 1025K| 1025K| | |* 24 | INDEX RANGE SCAN | I_EMR_YZB_ZYH | 5140 | 111 | | 3 (0)| 00:00:01 | 813K|00:00:00.20 | 18071 | 1025K| 1025K| | | 25 | TABLE ACCESS BY INDEX ROWID | EMR_YZB | 813K| 111 | 12099 | 48 (0)| 00:00:01 | 813K|00:00:01.28 | 359K| | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//这样标量子查询的starts减少不少。

编辑推荐：

返回列表

[20211220]关于标量子查询问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推