{"id":990,"date":"2015-12-30T04:13:14","date_gmt":"2015-12-30T04:13:14","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=990"},"modified":"2015-12-30T04:13:14","modified_gmt":"2015-12-30T04:13:14","slug":"%ed%92%80-%ed%85%8c%ec%9d%b4%eb%b8%94-%ec%8a%a4%ec%ba%94%ec%9c%bc%eb%a1%9c-%ec%8b%9c%ec%8a%a4%ed%85%9c-%eb%a6%ac%ec%86%8c%ec%8a%a4-%ec%a6%9d%ea%b0%80%ed%95%98%eb%8a%94-%ec%bf%bc%eb%a6%ac-%ed%8a%9c","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=990","title":{"rendered":"\ud480 \ud14c\uc774\ube14 \uc2a4\uce94\uc73c\ub85c \uc2dc\uc2a4\ud15c \ub9ac\uc18c\uc2a4 \uc99d\uac00\ud558\ub294 \ucffc\ub9ac \ud29c\ub2dd"},"content":{"rendered":"

SQL #1<\/span><\/p>\n

 <\/span><\/p>\n

SELECT *                                                  
\n<\/span><\/p>\n

FROM (SELECT T0.*,
\nFLOOR((ROWNUM – 1) \/ :1 + 1) PAGE      <\/span><\/p>\n

       FROM           (                                               <\/span><\/p>\n

                                        SELECT                                          <\/span><\/p>\n

                                                     a.entry_indexno,
\na.eventid,               <\/span><\/p>\n

                                                     a.userid,
\na.name, a.reg_date,                  <\/span><\/p>\n

                                                     b.phone,
\nb.address1, b.address2,               <\/span><\/p>\n

                                                     a.value1,                                    <\/span><\/p>\n

                                                     a.value2,                                    <\/span><\/p>\n

                                                     a.value3,                                    <\/span><\/p>\n

                                                     a.value4,                                    <\/span><\/p>\n

                                                     a.value5,                                    <\/span><\/p>\n

                                                     a.value6,                                    <\/span><\/p>\n

                                                     a.value7,                                    <\/span><\/p>\n

                                                     a.value8,                                    <\/span><\/p>\n

                                                     a.value9,                                    <\/span><\/p>\n

                                                     a.winner_status,                          <\/span><\/p>\n

                                                     a.event_goods_indexno,               <\/span><\/p>\n

                                                     e.goods_name
\n,                           <\/span><\/p>\n

                                                    (SELECT                                   <\/span><\/p>\n

                                                                 count(*)                                    <\/span><\/p>\n

                                                    FROM
\nDDH_EVENT_ENTRY            <\/span><\/p>\n

                                                    WHERE userid = a.userid              <\/span><\/p>\n

                                                    AND
\nwinner_status = ‘Y’              <\/span><\/p>\n

                                                      ) as
\nold_win_count                       <\/span><\/p>\n

                                       FROM     DDH_EVENT_ENTRY a                    <\/span><\/p>\n

                                                                                LEFT
\nJOIN DDH_USERMASTER b                    
\n<\/span><\/p>\n

                                                                               ON
\na.userid = b.userid                        
\n<\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_EVENT_MASTER c               <\/span><\/p>\n

                                                                               ON
\na.eventid = c.eventid             <\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_EVENT_GOODS d                    
\n<\/span><\/p>\n

                                                                               ON
\na.event_goods_indexno = d.event_goods_indexno                       <\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_GOODS_MASTER e               <\/span><\/p>\n

                                                                               ON
\nd.goods_indexno = e.goods_indexno            
\n<\/span><\/p>\n

                                       WHERE a.eventid = :2                       <\/span><\/p>\n

                                       ORDER BY a.reg_date ASC                        <\/span><\/p>\n

                                        )
\nT0                                                 <\/span><\/p>\n

             )                                                       
\n<\/span><\/p>\n

WHERE PAGE = :3<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

\ubc14\uc778\ub4dc \ubcc0\uc218 \uac12 \uc5c6\uc74c.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

\uc218\ud589 \uc2dc\uac04 \/ <\/span>\uc77c \ub7c9 \uacb0\uacfc<\/span><\/p>\n

 <\/span><\/p>\n

elapsed          disk     
\nquery     current    
\n rows<\/span><\/p>\n

———-     ———-   ———-   ———-   ———-<\/span><\/p>\n

3.157774         0       241757         
\n0       4078<\/span><\/p>\n

 <\/span><\/p>\n

\uc2e4\uc81c \uc218\ud589 \ud50c\ub79c \ubc0f
\n\uac74\uc218<\/span><\/p>\n

 <\/span><\/p>\n

Row
\nRow_Source_Operation<\/span><\/p>\n

———
\n————————————————————————————————————————<\/span><\/p>\n

     4078 
\nSORT AGGREGATE  (cr=90324 pr=0
\npw=0 time=468228)<\/span><\/p>\n

        0  
\nTABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=90324 pr=0 pw=0
\ntime=459254)<\/span><\/p>\n

    90121   
\nINDEX RANGE SCAN DDH_EVENT_ENTRY_I1 (cr=8496 pr=0 pw=0 time=65121)<\/span><\/p>\n

       10 
\nVIEW   (cr=151433 pr=0 pw=0
\ntime=2689546)<\/span><\/p>\n

    23364  
\nCOUNT   (cr=151433 pr=0 pw=0
\ntime=2647019)<\/span><\/p>\n

    23364   
\nVIEW   (cr=151433 pr=0 pw=0
\ntime=2623644)<\/span><\/p>\n

    23364    
\nSORT ORDER BY  (cr=151433 pr=0
\npw=0 time=2623636)<\/span><\/p>\n

    23364     
\nHASH JOIN RIGHT OUTER  (cr=61109
\npr=0 pw=0 time=2071387)<\/span><\/p>\n

       87      
\nTABLE ACCESS FULL DDH_GOODS_MASTER (cr=7 pr=0 pw=0 time=40) <– <\/span><\/b>\ud480 \uc2a4\uce94<\/span><\/b><\/p>\n

    23364      
\nNESTED LOOPS OUTER  (cr=61102 pr=0
\npw=0 time=2047065)<\/span><\/p>\n

    23364       
\nHASH JOIN RIGHT OUTER  (cr=61102
\npr=0 pw=0 time=2000328)<\/span><\/p>\n

        1         INDEX UNIQUE SCAN DDH_EVENT_MASTER_PK
\n(cr=1 pr=0 pw=0 time=13)<\/span><\/p>\n

    23364         HASH JOIN OUTER  (cr=61101 pr=0 pw=0 time=1941106)<\/span><\/p>\n

    23364          TABLE
\nACCESS FULL DDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=23393) <– <\/span><\/b><\/span>\ud480 \uc2a4\uce94<\/span><\/b><\/p>\n

  1461816          TABLE ACCESS FULL DDH_USERMASTER#
\n(cr=58331 pr=0 pw=0 time=1723)<– <\/span><\/b>\ud480 \uc2a4\uce94<\/span><\/b><\/p>\n

        0       
\nTABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=43401)<\/span><\/p>\n

        0         INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK
\n(cr=0 pr=0 pw=0 time=18149)<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

2. <\/span>\ubb38\uc81c\uc810 \ubc0f \uc6d0\uc778<\/span><\/p>\n

–    <\/span><\/span>\ud14c\uc774\ube14\uc744 \ud480 \uc2a4\uce94 \ub9ac\ub4dc \ub418\uba70 \uc2dc\uac04 \uc18c\uc694\uac00 \uc99d\uac00
\n\ubc0f \ud53c\uc9c0\uceec I\/O <\/span>\uc218\ud589\uc774 \uc99d\uac00 \ub428<\/span><\/p>\n

 <\/span><\/p>\n

–    <\/span><\/span>\uc55e\uc73c\ub85c\uc758 Tuning Guide<\/span>\uc5d0\uc11c \uc81c\uc2dc\ud558\ub294 \uac1c\uc120\uc548\uc744
\n\uc2e4\uc81c \uc6b4\uc601\uc911\uc778 \uc2dc\uc2a4\ud15c\uc5d0 \uc801\uc6a9 \uc2dc \ubc18\ub4dc\uc2dc \uc6d0\ubb38SQL<\/span>\uacfc \uad00\ub828 \uc788\ub294 \ub2f4\ub2f9\uc790\ub098 \uac1c\ubc1c\uc790 \ubd84\uc774 \uac1c\uc120\uc804\uc758 \uc2e4\ud589\uacb0\uacfc\uac12\uacfc
\n\uac1c\uc120\ubc29\uc548\uc758 \uc2e4\ud589\uacb0\uacfc\uac12\uc744 \ub2e4\ubc29\uba74\uc73c\ub85c \uba74\ubc00\ud788 \uc870\uc0ac\ud558\uc5ec \uacb0\uacfc\uac12\uc758 \ucc28\uc774\uac00 \uc5c6\ub294\uc9c0 \ud655\uc778\ud55c \ub4a4 \uc801\uc6a9\ud558\uc5ec\uc57c \ud558\uba70 \uac1c\ubc1c\ud658\uacbd\uc774\ub098 \uc5c5\ubb34 \ud2b9\uc774\uc0ac\ud56d\uc73c\ub85c \ubb38\uc81c\uac00 \ub420 \uc218
\n\uc788\ub294\uc9c0 \ub4f1\uc5d0 \ub300\ud55c \uc885\ud569\uc801\uc778 \ud310\ub2e8\uc744 \ud558\uc5ec \uc6b4\uc601\uc2dc\uc2a4\ud15c \uc801\uc6a9 \uc2dc \ubb38\uc81c\uac00 \ubc1c\uc0dd\ub418\uc9c0 \uc54a\ub3c4\ub85d \uc8fc\uc758\ud558\uc5ec\uc57c \ud55c\ub2e4.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

3. <\/span>\uac1c\uc120\ubc29\uc548<\/span><\/p>\n

 <\/span><\/p>\n

–   
\n<\/span><\/span>\ud480 \uc2a4\uce94\uc744
\n\ub9c9\uace0 \uc778\ub371\uc2a4 \uc2a4\uce94\uc73c\ub85c \ud558\ub3c4\ub85d \uc218\uc815\ud569\ub2c8\ub2e4.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/b><\/p>\n

\uac1c\uc120
\n\uc548.<\/span><\/span><\/b><\/p>\n

SQL HINT <\/span><\/b>\uc870\uc815\uc73c\ub85c JOIN <\/span>\ubc29\uc2dd \ubc0f \uc778\ub371\uc2a4 \uc218\ud589 \ub418\ub3c4\ub85d \ubcc0\uacbd\ud568.<\/span><\/span><\/b><\/p>\n

 <\/span><\/p>\n

SELECT *                                                  
\n<\/span><\/p>\n

FROM (SELECT T0.*,
\nFLOOR((ROWNUM – 1) \/ :1 + 1) PAGE      <\/span><\/p>\n

       FROM           (                                               <\/span><\/p>\n

                                        SELECT \/*+ leading(a b c d e) use_nl(a b c d e)
\nindex(b DDH_USERMASTER_I1) index(e DDH_GOODS_MASTER_PK) *\/<\/span><\/b>         a.entry_indexno,
\na.eventid,               <\/span><\/p>\n

                                                     a.userid,
\na.name, a.reg_date,                  <\/span><\/p>\n

                                                     b.phone,
\nb.address1, b.address2,               <\/span><\/p>\n

                                                     a.value1,                                    <\/span><\/p>\n

                                                     a.value2,                                    <\/span><\/p>\n

                                                     a.value3,                                    <\/span><\/p>\n

                                                     a.value4,                                    <\/span><\/p>\n

                                                     a.value5,                                    <\/span><\/p>\n

                                                     a.value6,                                    <\/span><\/p>\n

                                                     a.value7,                                    <\/span><\/p>\n

                                                     a.value8,                                    <\/span><\/p>\n

                                                     a.value9,                                    <\/span><\/p>\n

                                                     a.winner_status,                          <\/span><\/p>\n

                                                     a.event_goods_indexno,               <\/span><\/p>\n

                                                     e.goods_name
\n,                           <\/span><\/p>\n

                                                    (SELECT                                   <\/span><\/p>\n

                                                                 count(*)                                    <\/span><\/p>\n

                                                    FROM
\nDDH_EVENT_ENTRY            <\/span><\/p>\n

                                                    WHERE userid = a.userid              <\/span><\/p>\n

                                                    AND
\nwinner_status = ‘Y’              <\/span><\/p>\n

                                                      ) as
\nold_win_count                       <\/span><\/p>\n

                                       FROM     DDH_EVENT_ENTRY a                    <\/span><\/p>\n

                                                                                LEFT
\nJOIN DDH_USERMASTER b                    
\n<\/span><\/p>\n

                                                                               ON
\na.userid = b.userid                        
\n<\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_EVENT_MASTER c               <\/span><\/p>\n

                                                                               ON
\na.eventid = c.eventid             <\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_EVENT_GOODS d                    
\n<\/span><\/p>\n

                                                                               ON
\na.event_goods_indexno = d.event_goods_indexno                       <\/span><\/p>\n

                                                                               LEFT
\nJOIN DDH_GOODS_MASTER e               <\/span><\/p>\n

                                                                               ON
\nd.goods_indexno = e.goods_indexno            
\n<\/span><\/p>\n

                                       WHERE a.eventid = :2                       <\/span><\/p>\n

                                       ORDER BY a.reg_date ASC                        <\/span><\/p>\n

                                        )
\nT0                                                
\n<\/span><\/p>\n

             )                                                       
\n<\/span><\/p>\n

WHERE
\nPAGE = :3<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

\uc218\ud589 \uc2dc\uac04 \/ <\/span>\uc77c \ub7c9 \uacb0\uacfc<\/span><\/p>\n

 <\/span><\/p>\n

elapsed          disk      query  
\n  current      
\nrows<\/span><\/p>\n

———-
\n    ———-   ———-   ———-   ———-<\/span><\/p>\n

 10.847647     
\n     0   
\n1949657          0      18159<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

\uc2e4\uc81c \uc218\ud589 \ud50c\ub79c \ubc0f
\n\uac74\uc218<\/span><\/p>\n

 <\/span><\/p>\n

Row  Row_Source_Operation<\/span><\/p>\n

——— ————————————————————————————————————————<\/span><\/p>\n

   
\n18159  SORT AGGREGATE  (cr=938445 pr=0 pw=0 time=5027074)<\/span><\/p>\n

        0  
\nTABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=938445 pr=0 pw=0 time=4986103)<\/span><\/p>\n

  
\n989129    INDEX RANGE SCAN
\nDDH_EVENT_ENTRY_I1 (cr=39979 pr=0 pw=0 time=488034)<\/span><\/p>\n

       10 
\nVIEW   (cr=1011212 pr=0 pw=0
\ntime=5820573)<\/span><\/p>\n

   
\n23364   COUNT   (cr=1011212 pr=0 pw=0 time=5771581)<\/span><\/p>\n

   
\n23364    VIEW   (cr=1011212 pr=0 pw=0 time=5748205)<\/span><\/p>\n

   
\n23364     SORT ORDER BY  (cr=1011212 pr=0 pw=0 time=5748197)<\/span><\/p>\n

   
\n23364      NESTED LOOPS OUTER  (cr=72767 pr=0 pw=0 time=607526)<\/span><\/p>\n

   
\n23364       NESTED LOOPS
\nOUTER  (cr=72767 pr=0 pw=0 time=537432)<\/span><\/p>\n

   
\n23364        NESTED LOOPS
\nOUTER  (cr=72767 pr=0 pw=0 time=467339)<\/span><\/p>\n

   
\n23364         NESTED LOOPS
\nOUTER  (cr=72765 pr=0 pw=0 time=373878)<\/span><\/p>\n

   
\n23364          TABLE ACCESS FULL
\nDDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=46768)<\/span><\/p>\n

   
\n23265          TABLE ACCESS BY
\nINDEX ROWID DDH_USERMASTER# (cr=69995 pr=0 pw=0 time=296150)<\/span><\/p>\n

   
\n23265           INDEX UNIQUE SCAN
\nDDH_USERMASTER_I1 (cr=46730 pr=0 pw=0 time=173375)<\/span><\/p>\n

   
\n23364         INDEX UNIQUE SCAN
\nDDH_EVENT_MASTER_PK (cr=2 pr=0 pw=0 time=68697)<\/span><\/p>\n

        0       
\nTABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=47826)<\/span><\/p>\n

        0         INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK
\n(cr=0 pr=0 pw=0 time=18914)<\/span><\/p>\n

        0      
\nTABLE ACCESS BY INDEX ROWID DDH_GOODS_MASTER (cr=0 pr=0 pw=0 time=44078)<\/span><\/p>\n

        0       
\nINDEX UNIQUE SCAN DDH_GOODS_MASTER_PK (cr=0 pr=0 pw=0 time=18043)<\/span><\/p>\n

 <\/span><\/p>\n<\/p>\n

<- <\/span>\ud480 \uc2a4\uce94 \ud14c\uc774\ube14\uc744 \uc81c\uac70 \ud558\uc600\uc73c\ub098
\nselect <\/span>\uc808\uc5d0 count <\/span>\ubb38\uc73c\ub85c \uc778\ud574 DDH_EVENT_ENTRY
\n<\/span>\ud14c\uc774\ube14\uc744 \uc218\ud589 \uac74\uc218 \ub9cc\ud07c \ub2e4\uc2dc \uc11c\uce58\ud558\ubbc0\ub85c \uc774\ub97c \uc81c\uac70<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

SQL #1   SELECT *                                                   FROM (SELECT T0.*, FLOOR((ROWNUM – 1) \/ :1 + 1) PAGE              FROM           (                                                                                       SELECT                                                                                               a.entry_indexno, a.eventid,                                                                    a.userid, a.name, a.reg_date,                                                                       b.phone, b.address1, b.address2,                                                                     a.value1,                                                                                         a.value2,                                                                                         a.value3,                                                                                         a.value4,                                                                                         a.value5,                                                                                          a.value6,                                                                                         a.value7,                                                                                         a.value8,                                                                                         a.value9,                                                                                         […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/990"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=990"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/990\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=990"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=990"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=990"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}