|
SELECT SUBSTR(original,1,16), CASE WHEN openid LIKE 'BUK%' THEN 'YT'
WHEN openid LIKE 'SUP%' THEN 'SUPT'
ELSE openid
END soldid,
SUM(water) water
FROM MWP a,
TAS b,
(
SELECT DISTINCT a.agreeid, a.signed, a.resigned
FROM MSP a
(
SELECT soldid FROM MSAGE WHERE stageid IN ('TTOT','SKOT')
) b
WHERE CASE WHEN a.agreeid LIKE 'BUK%' THEN 'YT'
WHEN a.agreeid LIKE 'SUP%' THEN 'SUPT'
ELSE a.agreeid
END = b.soldid
) c
WHERE a.pkid = '201136'
AND a.img LIKE 'K%'
AND a.water > 0
AND a.agreeid IN (SELECT soldid FROM MSAGE WHERE group2 IN ('TST','STK'))
AND a.original = b.asscd
AND a.openid = c.agreeid
AND a.otp = c.signed
AND SUBSTR(a.original,16,1) NOT IN ('M','P')
AND c.resigned > CASE WHEN b.ftf ='MBL' THEN 'TZEZ' ELSE 'TZNF' END
GROUP BY
SUBSTR(original,1,16),
CASE WHEN openid LIKE 'BUK%' THEN 'YT'
WHEN openid LIKE 'SUP%' THEN 'SUPT'
ELSE openid
END
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 50 |00:00:01.69 | 74565 | 256 | 725K| 725K| |
| 2 | VIEW | | 1 | 1 | 786 |00:00:01.68 | 74565 | 256 | | | |
| 3 | HASH UNIQUE | | 1 | 1 | 786 |00:00:01.68 | 74565 | 256 | 676K| 676K| 1168K (0)|
| 4 | NESTED LOOPS | | 1 | 1 | 30467 |00:00:00.52 | 74565 | 256 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 30467 |00:00:00.24 | 13631 | 256 | | | |
| 6 | NESTED LOOPS | | 1 | 1 | 2972 |00:00:03.25 | 10455 | 256 | | | |
| 7 | NESTED LOOPS SEMI | | 1 | 1 | 2972 |00:00:03.21 | 4491 | 249 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| MWP | 1 | 1 | 5287 |00:00:00.58 | 4462 | 249 | | | |
|* 9 | INDEX RANGE SCAN | PK_MWP | 1 | 1 | 9321 |00:00:00.54 | 139 | 138 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| MSAGE | 26 | 64 | 7 |00:00:00.01 | 29 | 0 | | | |
|* 11 | INDEX RANGE SCAN | PK_MSAGE | 26 | 1 | 25 |00:00:00.01 | 2 | 0 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TAS | 2972 | 1 | 2972 |00:00:00.11 | 5964 | 7 | | | |
|* 13 | INDEX RANGE SCAN | PK_TAS | 2972 | 1 | 2972 |00:00:00.09 | 2992 | 7 | | | |
|* 14 | INDEX RANGE SCAN | PK_MSP | 2972 | 1 | 30467 |00:00:00.03 | 3176 | 0 | | | |
| 15 | INLIST ITERATOR | | 30467 | | 30467 |00:00:00.26 | 60934 | 0 | | | |
|* 16 | INDEX UNIQUE SCAN | PK_MSAGE | 60934 | 1 | 30467 |00:00:00.18 | 60934 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
위 쿼리에서 힌트를 추가해 15,16번 실행계획부분이 HASH JOIN이 되도록 하고 싶은데 (4번에서 바뀌는게 맞죠) 어떻게 힌트를 써야 하나요?
몇가지 해봤더니 순서도 바뀌고 11번의 INDEX RANGE SCAN이 아닌 FULL TABLE SCAN으로 바뀌어 버리더군요.
|
