[20211220]關於標量子查詢問題.txt
[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減少不少。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2848592/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200325]慎用標量子查詢.txt
- [20211214]18c標量子查詢unnest.txt
- [20180626]函式與標量子查詢14.txt函式
- [20180612]函式與標量子查詢10.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- [20180602]函式與標量子查詢4.txt函式
- [20180602]函式與標量子查詢3.txt函式
- 20180601]函式與標量子查詢2.txt函式
- [20211220]記錄使用sqlplus的小問題.txtSQL
- [20210202]計算標量子查詢快取數量2.txt快取
- 關於 mysql 中的 rand () 查詢問題MySql
- [20210201]19c計算標量子查詢快取數量.txt快取
- [20180819]關於父子游標問題(11g).txt
- [20210418]查詢v$檢視問題.txt
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- Java中關於二分查詢的問題Java
- [20191202]關於hugepages相關問題.txt
- [20191129]關於hugepages的問題.txt
- [20181123]關於降序索引問題.txt索引
- [20180403]關於時區問題.txt
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- [20210208]lob欄位與查詢的問題.txt
- 【redis】關於查詢和分析redis中的bigkeys問題Redis
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- 關於並查集問題並查集
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引
- [20190918]關於函式索引問題.txt函式索引
- [20181229]關於字串的分配問題.txt字串
- [20180625]函式與標量子查詢13(補充)函式
- [20230308]12c以上版本模糊查詢問題.txt
- MySQL鎖表相關問題查詢思路MySql
- [20190910]關於降序索引問題5.txt索引
- [20200711]關於左右連線的問題.txt
- [20200416]關於軟軟解析的問題.txt
- [20181124]關於降序索引問題4.txt索引