Å×À̺í Á¤º¸ V2_CHATROOM --------------------------------------------------------------------------- Æ©´×Àü Äõ¸® SELECT /*+ gather_plan_statistics 20180427 */ N.GBN, N.CHATROOMSEQ, N.CHATROOMNAME, N.ALARMYN, N.REGDT, N.TIMESTAMP, NVL( (SELECT TB.USERID FROM V2_USERPROFILE TB, V2_CHATROOM CR, V2_CHATROOMUSER CRU WHERE TB.USERID = N.REGID AND CR.REGID = TB.USERID AND CR.CHATROOMSEQ = N.CHATROOMSEQ AND CRU.CHATROOMSEQ = CR.CHATROOMSEQ AND CRU.USERID = CR.REGID AND CRU.DELYN = 'N' ) ,(SELECT USERID FROM V2_CHATROOMUSER WHERE CHATROOMSEQ = N.CHATROOMSEQ AND DELYN = 'N' AND ROWNUM = 1 ) ) AS REGID, N.USERID, N.DISPLAYNAME, N.PHOTO, N.PLUSPHOTO, N.PLUSROOMNAME FROM ( SELECT S.GBN, S.CHATROOMSEQ, (SELECT CHATROOMNAME FROM V2_CHATROOMNAME WHERE CHATROOMSEQ = S.CHATROOMSEQ AND REGID = S.REGID) AS CHATROOMNAME, S.ALARMYN, S.REGDT, S.TIMESTAMP, S.REGID, S.USERID, S.DISPLAYNAME, S.PHOTO, S.PLUSPHOTO, ( SELECT CHATROOMNAME FROM V2_CHATROOMNAME SCRN, V2_CHATROOM SCR WHERE 1=1 AND SCRN.CHATROOMSEQ = SCR.CHATROOMSEQ AND SCRN.CHATROOMSEQ = S.CHATROOMSEQ AND SCR.REGID = SCRN.REGID ) PLUSROOMNAME FROM ( SELECT B.GBN, A.CHATROOMSEQ, B.CHATROOMNAME, B.ALARMYN, TO_CHAR(B.REGDT, 'yyyy-MM-dd am hh12:mi') AS REGDT, TO_CHAR(B.REGDT, 'YYYYMMDDHH24MISS') AS TIMESTAMP, B.REGID, A.USERID, C.DISPLAYNAME, C.PHOTOPATH||C.PHOTONM AS PHOTO, row_number() over (partition BY B.CHATROOMSEQ ORDER BY B.CHATROOMSEQ DESC, B.REGDT DESC ) rnum, DECODE (P.FILEPATH, '', '/upload/Plus/room.png', P.FILEPATH||P.FILENM) PLUSPHOTO FROM V2_CHATROOMUSER A, (SELECT D.GBN, D.CHATROOMSEQ, CN.CHATROOMNAME, CN.ALARMYN, D.REGID, D.REGDT FROM (SELECT * FROM V2_CHATROOM WHERE GBN <> 'N' UNION ALL SELECT * FROM V2_CHATROOM WHERE GBN = 'N' AND REGID = 'skytown@korea.kr' ) D, V2_CHATROOMNAME CN, V2_CHATROOMUSER F WHERE D.DELYN = 'N' AND F.USERID = 'skytown@korea.kr' AND F.DELYN = 'N' AND D.CHATROOMSEQ = F.CHATROOMSEQ AND D.CHATROOMSEQ = CN.CHATROOMSEQ(+)) B, V2_USERPROFILE C, V2_PLUSFRIEND P WHERE A.CHATROOMSEQ = B.CHATROOMSEQ AND A.DELYN = 'N' AND A.USERID = C.USERID AND A.USERID <> 'skytown@korea.kr' AND A.CHATROOMSEQ = P.CHATROOMSEQ(+) ) S WHERE S.rnum < 5 UNION ALL SELECT X.GBN, X.CHATROOMSEQ CHATROOMSEQ, X.CHATROOMNAME CHATROOMNAME, X.ALARMYN ALARMYN, TO_CHAR(X.REGDT, 'YYYY-MM-DD AM HH12:MI') REGDT, TO_CHAR(X.REGDT, 'YYYYMMDDHH24MISS') TIMESTAMP, USERPROFILE.USERID REGID, DECODE(X.GBN, 'I', USERPROFILE.USERID, 'bababarotalk') AS USERID, DECODE(X.GBN,'I',X.CHATROOMNAME,(SELECT NVL((SELECT V2_USERINFO.DISPLAYNAME FROM V2_SNSUSER, V2_USERINFO, V2_CHATROOM WHERE V2_SNSUSER.CERTID = V2_USERINFO.CERTID AND V2_SNSUSER.USERID = V2_CHATROOM.LASTEXITID AND V2_CHATROOM.CHATROOMSEQ = X.CHATROOMSEQ), '´ëÈ­»ó´ë¾øÀ½') FROM DUAL)) DISPLAYNAME, DECODE(X.GBN, 'I', USERPROFILE.PHOTOPATH||USERPROFILE.PHOTONM, '/upload/Profile/user.png') AS PHOTO, DECODE (P.FILEPATH, '', '/upload/Plus/room.png', P.FILEPATH||P.FILENM) PLUSPHOTO, ( SELECT CHATROOMNAME FROM V2_CHATROOMNAME SCRN, V2_CHATROOM SCR WHERE 1=1 AND SCRN.CHATROOMSEQ = SCR.CHATROOMSEQ AND SCRN.CHATROOMSEQ = X.CHATROOMSEQ AND SCR.REGID = SCRN.REGID ) PLUSROOMNAME FROM (SELECT COUNT(*) CNT, MIN(K.GBN) GBN, K.CHATROOMSEQ, MIN(K.REGDT) REGDT, MIN(CHATROOMNAME) CHATROOMNAME, MIN(G.ALARMYN) ALARMYN, MIN(Z.USERID) USERID FROM (SELECT D.GBN, D.CHATROOMSEQ, D.REGDT FROM V2_CHATROOM D, V2_CHATROOMUSER F WHERE D.DELYN = 'N' AND F.USERID = 'skytown@korea.kr' AND F.DELYN = 'N' AND D.CHATROOMSEQ = F.CHATROOMSEQ ) K, V2_CHATROOMUSER Z, (SELECT * FROM V2_CHATROOMNAME WHERE REGID = 'skytown@korea.kr' ) G WHERE 1=1 AND K.CHATROOMSEQ = Z.CHATROOMSEQ AND K.CHATROOMSEQ = G.CHATROOMSEQ(+) AND Z.DELYN = 'N' GROUP BY K.CHATROOMSEQ HAVING COUNT(*) = 1 ) X , V2_USERPROFILE USERPROFILE, V2_PLUSFRIEND P WHERE 1 = 1 AND X.USERID = USERPROFILE.USERID AND X.CHATROOMSEQ = P.CHATROOMSEQ(+) ) N ORDER BY N.CHATROOMSEQ DESC, N.REGDT DESC; Æ©´×Àü ½ÇÇà°èȹ SQL_ID 3cb4d90w6ckbb, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics 20180504 */ N.GBN, N.CHATROOMSEQ, N.CHATROOMNAME, N.ALARMYN, N.REGDT, N.TIMESTAMP, NVL( (SELECT TB.USERID FROM V2_USERPROFILE TB, V2_CHATROOM CR, V2_CHATROOMUSER CRU WHERE TB.USERID = N.REGID AND CR.REGID = TB.USERID AND CR.CHATROOMSEQ = N.CHATROOMSEQ AND CRU.CHATROOMSEQ = CR.CHATROOMSEQ AND CRU.USERID = C Plan hash value: 460356803 ------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 505 |00:00:00.93 | 283K| | | | | 1 | NESTED LOOPS | | 209 | 167 |00:00:00.01 | 1118 | | | | | 2 | NESTED LOOPS | | 209 | 167 |00:00:00.01 | 786 | | | | |* 3 | INDEX RANGE SCAN | V2_IDX_CHRMUSER_USERDELCHSEQ | 209 | 175 |00:00:00.01 | 433 | | | | |* 4 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOM | 175 | 167 |00:00:00.01 | 353 | | | | |* 5 | INDEX UNIQUE SCAN | PK_CHATROOM | 175 | 175 |00:00:00.01 | 178 | | | | |* 6 | INDEX UNIQUE SCAN | PK_V2_USERPROFILE | 167 | 167 |00:00:00.01 | 332 | | | | |* 7 | COUNT STOPKEY | | 209 | 209 |00:00:00.01 | 834 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMUSER | 209 | 209 |00:00:00.01 | 834 | | | | |* 9 | INDEX RANGE SCAN | V2_IDX_CHROOMUSER_CHATROOMSEQ | 209 | 265 |00:00:00.01 | 624 | | | | | 10 | SORT ORDER BY | | 1 | 505 |00:00:00.93 | 283K| 124K| 124K| 110K (0)| | 11 | VIEW | | 1 | 505 |00:00:00.94 | 281K| | | | | 12 | UNION-ALL | | 1 | 505 |00:00:00.94 | 281K| | | | | 13 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMNAME | 197 | 11 |00:00:00.01 | 164 | | | | |* 14 | INDEX UNIQUE SCAN | PK_V2_CHATROOMNAME | 197 | 11 |00:00:00.01 | 153 | | | | | 15 | NESTED LOOPS | | 197 | 11 |00:00:00.01 | 565 | | | | | 16 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOM | 197 | 197 |00:00:00.01 | 401 | | | | |* 17 | INDEX UNIQUE SCAN | PK_CHATROOM | 197 | 197 |00:00:00.01 | 204 | | | | | 18 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMNAME | 197 | 11 |00:00:00.01 | 164 | | | | |* 19 | INDEX UNIQUE SCAN | PK_V2_CHATROOMNAME | 197 | 11 |00:00:00.01 | 153 | | | | |* 20 | VIEW | | 1 | 493 |00:00:00.94 | 278K| | | | |* 21 | WINDOW SORT PUSHED RANK | | 1 | 90021 |00:00:00.73 | 278K| 21M| 1721K| 19M (0)| | 22 | NESTED LOOPS | | 1 | 90021 |00:00:00.50 | 278K| | | | | 23 | NESTED LOOPS | | 1 | 90021 |00:00:00.38 | 188K| | | | |* 24 | HASH JOIN RIGHT OUTER | | 1 | 90021 |00:00:00.18 | 8020 | 1035K| 1035K| 1277K (0)| | 25 | TABLE ACCESS FULL | V2_PLUSFRIEND | 1 | 1086 |00:00:00.01 | 37 | | | | | 26 | NESTED LOOPS | | 1 | 90021 |00:00:00.13 | 7983 | | | | | 27 | NESTED LOOPS | | 1 | 122K|00:00:00.05 | 3759 | | | | | 28 | NESTED LOOPS OUTER | | 1 | 396 |00:00:00.03 | 2835 | | | | |* 29 | HASH JOIN | | 1 | 209 |00:00:00.06 | 2531 | 894K| 894K| 1242K (0)| |* 30 | INDEX RANGE SCAN | V2_IDX_CHRMUSER_USERDELCHSEQ | 1 | 211 |00:00:00.01 | 5 | | | | | 31 | VIEW | | 1 | 154K|00:00:00.10 | 2526 | | | | | 32 | UNION-ALL | | 1 | 154K|00:00:00.07 | 2526 | | | | |* 33 | TABLE ACCESS FULL | V2_CHATROOM | 1 | 154K|00:00:00.03 | 1263 | | | | |* 34 | TABLE ACCESS FULL | V2_CHATROOM | 1 | 2 |00:00:00.01 | 1263 | | | | | 35 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMNAME | 209 | 209 |00:00:00.01 | 304 | | | | |* 36 | INDEX RANGE SCAN | PK_V2_CHATROOMNAME | 209 | 209 |00:00:00.01 | 179 | | | | |* 37 | INDEX RANGE SCAN | V2_IDX_CHROOMUSER_CHATROOMSEQ | 396 | 122K|00:00:00.03 | 924 | | | | |* 38 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMUSER | 122K| 90021 |00:00:00.07 | 4224 | | | | |* 39 | INDEX UNIQUE SCAN | PK_V2_USERPROFILE | 90021 | 90021 |00:00:00.16 | 180K| | | | | 40 | TABLE ACCESS BY INDEX ROWID | V2_USERPROFILE | 90021 | 90021 |00:00:00.09 | 90620 | | | | | 41 | NESTED LOOPS | | 11 | 3 |00:00:00.01 | 43 | | | | | 42 | NESTED LOOPS | | 11 | 3 |00:00:00.01 | 32 | | | | |* 43 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOM | 11 | 3 |00:00:00.01 | 24 | | | | |* 44 | INDEX UNIQUE SCAN | PK_CHATROOM | 11 | 11 |00:00:00.01 | 13 | | | | |* 45 | INDEX RANGE SCAN | V2_IDX_SNSUSER_USERCERTID | 3 | 3 |00:00:00.01 | 8 | | | | | 46 | TABLE ACCESS BY INDEX ROWID | V2_USERINFO | 3 | 3 |00:00:00.01 | 11 | | | | |* 47 | INDEX UNIQUE SCAN | SYS_C0048669 | 3 | 3 |00:00:00.01 | 8 | | | | | 48 | FAST DUAL | | 11 | 11 |00:00:00.01 | 0 | | | | | 49 | NESTED LOOPS | | 12 | 2 |00:00:00.01 | 42 | | | | | 50 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOM | 12 | 12 |00:00:00.01 | 26 | | | | |* 51 | INDEX UNIQUE SCAN | PK_CHATROOM | 12 | 12 |00:00:00.01 | 14 | | | | | 52 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMNAME | 12 | 2 |00:00:00.01 | 16 | | | | |* 53 | INDEX UNIQUE SCAN | PK_V2_CHATROOMNAME | 12 | 2 |00:00:00.01 | 14 | | | | | 54 | NESTED LOOPS | | 1 | 12 |00:00:00.10 | 2177 | | | | | 55 | NESTED LOOPS | | 1 | 12 |00:00:00.10 | 2165 | | | | | 56 | NESTED LOOPS OUTER | | 1 | 12 |00:00:00.10 | 2151 | | | | | 57 | VIEW | | 1 | 12 |00:00:00.10 | 2137 | | | | |* 58 | FILTER | | 1 | 12 |00:00:00.10 | 2137 | | | | | 59 | HASH GROUP BY | | 1 | 211 |00:00:00.10 | 2137 | 700K| 700K| 1365K (0)| | 60 | NESTED LOOPS | | 1 | 71408 |00:00:00.08 | 2137 | | | | | 61 | NESTED LOOPS | | 1 | 99874 |00:00:00.03 | 1332 | | | | | 62 | NESTED LOOPS OUTER | | 1 | 211 |00:00:00.01 | 606 | | | | | 63 | NESTED LOOPS | | 1 | 211 |00:00:00.01 | 435 | | | | |* 64 | INDEX RANGE SCAN | V2_IDX_CHRMUSER_USERDELCHSEQ | 1 | 211 |00:00:00.01 | 5 | | | | |* 65 | TABLE ACCESS BY INDEX ROWID| V2_CHATROOM | 211 | 211 |00:00:00.01 | 430 | | | | |* 66 | INDEX UNIQUE SCAN | PK_CHATROOM | 211 | 211 |00:00:00.01 | 219 | | | | | 67 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMNAME | 211 | 10 |00:00:00.01 | 171 | | | | |* 68 | INDEX UNIQUE SCAN | PK_V2_CHATROOMNAME | 211 | 10 |00:00:00.01 | 161 | | | | |* 69 | INDEX RANGE SCAN | V2_IDX_CHROOMUSER_CHATROOMSEQ | 211 | 99874 |00:00:00.02 | 726 | | | | |* 70 | TABLE ACCESS BY INDEX ROWID | V2_CHATROOMUSER | 99874 | 71408 |00:00:00.04 | 805 | | | | | 71 | TABLE ACCESS BY INDEX ROWID | V2_PLUSFRIEND | 12 | 0 |00:00:00.01 | 14 | | | | |* 72 | INDEX RANGE SCAN | V2_IDX_PLUSFRIEND_CHATROOMSEQ | 12 | 0 |00:00:00.01 | 14 | | | | |* 73 | INDEX UNIQUE SCAN | PK_V2_USERPROFILE | 12 | 12 |00:00:00.01 | 14 | | | | | 74 | TABLE ACCESS BY INDEX ROWID | V2_USERPROFILE | 12 | 12 |00:00:00.01 | 12 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("CRU"."USERID"=:B1 AND "CRU"."DELYN"='N' AND "CRU"."CHATROOMSEQ"=:B2) 4 - filter("CR"."REGID"=:B1) 5 - access("CR"."CHATROOMSEQ"=:B1) 6 - access("TB"."USERID"=:B1) 7 - filter(ROWNUM=1) 8 - filter("DELYN"='N') 9 - access("CHATROOMSEQ"=:B1) 14 - access("CHATROOMSEQ"=:B1 AND "REGID"=:B2) 17 - access("SCR"."CHATROOMSEQ"=:B1) 19 - access("SCRN"."CHATROOMSEQ"=:B1 AND "SCR"."REGID"="SCRN"."REGID") filter("SCRN"."CHATROOMSEQ"="SCR"."CHATROOMSEQ") 20 - filter("S"."RNUM"<5) 21 - filter(ROW_NUMBER() OVER ( PARTITION BY "D"."CHATROOMSEQ" ORDER BY INTERNAL_FUNCTION("D"."REGDT") DESC )<5) 24 - access("A"."CHATROOMSEQ"="P"."CHATROOMSEQ") 29 - access("D"."CHATROOMSEQ"="F"."CHATROOMSEQ") 30 - access("F"."USERID"='skytown@korea.kr' AND "F"."DELYN"='N') 33 - filter(("GBN"<>'N' AND "V2_CHATROOM"."DELYN"='N')) 34 - filter(("REGID"='skytown@korea.kr' AND "GBN"='N' AND "V2_CHATROOM"."DELYN"='N')) 36 - access("D"."CHATROOMSEQ"="CN"."CHATROOMSEQ") 37 - access("A"."CHATROOMSEQ"="D"."CHATROOMSEQ") 38 - filter(("A"."DELYN"='N' AND "A"."USERID"<>'skytown@korea.kr')) 39 - access("A"."USERID"="C"."USERID") filter("C"."USERID"<>'skytown@korea.kr') 43 - filter("V2_CHATROOM"."LASTEXITID" IS NOT NULL) 44 - access("V2_CHATROOM"."CHATROOMSEQ"=:B1) 45 - access("V2_SNSUSER"."USERID"="V2_CHATROOM"."LASTEXITID") 47 - access("V2_SNSUSER"."CERTID"="V2_USERINFO"."CERTID") 51 - access("SCR"."CHATROOMSEQ"=:B1) 53 - access("SCRN"."CHATROOMSEQ"=:B1 AND "SCR"."REGID"="SCRN"."REGID") filter("SCRN"."CHATROOMSEQ"="SCR"."CHATROOMSEQ") 58 - filter(COUNT(*)=1) 64 - access("F"."USERID"='skytown@korea.kr' AND "F"."DELYN"='N') 65 - filter("D"."DELYN"='N') 66 - access("D"."CHATROOMSEQ"="F"."CHATROOMSEQ") 68 - access("D"."CHATROOMSEQ"="V2_CHATROOMNAME"."CHATROOMSEQ" AND "REGID"='skytown@korea.kr') 69 - access("D"."CHATROOMSEQ"="Z"."CHATROOMSEQ") 70 - filter("Z"."DELYN"='N') 72 - access("X"."CHATROOMSEQ"="P"."CHATROOMSEQ") 73 - access("X"."USERID"="USERPROFILE"."USERID")