ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [오라클 질문] 스칼라서브쿼리 사용한 쿼리의 parallel 쿼리 수행 문제
    기술이야기/Ask 엑셈 2010. 2. 23. 13:05
     
    2010-02-10 12:48:43 에 등록된 질문입니다. 강정우 님께서 질문해 주셨습니다.
    Q:
    안녕하세요^^

    아래 쿼리를 보시면스칼라서브쿼리를 주석으로 막았습니다.


    SELECT D.NM ,
           d.jumin_no ,
           c.in_dt ,
           c.adpt_fr_yyyymm ,
           c.adpt_to_yyyymm
    --     , (select x.chg_emp_no from tbbhab10 x
    --        where d.jumin_no  = x.jumin_no
    --          and d.jumin_seq = x.jumin_seq
    --          and x.tax_item_cd = '21'
    --          and x.adpt_to_yyyymm = '201001'
    --          and x.amt > 0
    --          and rownum = 1)
    FROM   (
           select /*+ no_merge full(z) parallel(z 16) */ z.jumin_no, z.jumin_seq, z.amt ,
    z.tax_item_cd, bugwa_seq_no, z.in_dt, z.adpt_fr_yyyymm , z.adpt_to_yyyymm
           from   tbbhab10 z
           where  z.TAX_ITEM_CD       = '21'
              AND z.ADPT_FR_YYYYMM   <= '201001'
              AND z.ADPT_TO_YYYYMM   >= '201001'
              and z.amt               = 1
              and exists ( select /*+ use_nl(d cc) index(cc ix_tbbhab10_03) */ 'Y' from
    tbbhab10 cc
                            where cc.jumin_no  = z.jumin_no
                              AND cc.ADPT_FR_YYYYMM   = '201002'
                              AND cc.ADPT_TO_YYYYMM   >= '201002'
                              and cc.tax_item_cd = '21'
                              and cc.amt > 1 )
           ) c ,
           tbjgba20 d   -- Rows=340,962,300  Blocks=9,620,360
    WHERE  d.jumin_no          = c.jumin_no
    AND    d.jumin_seq         = c.jumin_seq

    Execution Plan
    --------------------------------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22K Card=10K Bytes=780K)
       1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TBJGBA20' (Cost=6 Card=9 Bytes=351)
    (PARALLEL_COMBINED_WITH_CHILD)
       2    1     NESTED LOOPS (Cost=22K Card=10K Bytes=780K) (PARALLEL_TO_SERIAL) (QC
    (RANDOM))
       3    2       VIEW (Cost=21K Card=1K Bytes=43K) (PARALLEL_COMBINED_WITH_PARENT)
       4    3         NESTED LOOPS (SEMI) (Cost=21K Card=1K Bytes=105K)
    (PARALLEL_COMBINED_WITH_PARENT)
       5    4           PARTITION RANGE (ALL) (PARALLEL_COMBINED_WITH_PARENT)
       6    5             TABLE ACCESS (FULL) OF 'TBBHAB10' (Cost=10K Card=42K Bytes=2M)
    (PARALLEL_COMBINED_WITH_PARENT)
       7    4           PARTITION RANGE (ITERATOR) (PARALLEL_COMBINED_WITH_PARENT)
       8    7             TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TBBHAB10' (Cost=4 Card=28
    Bytes=1K) (PARALLEL_COMBINED_WITH_PARENT)
       9    8               INDEX (RANGE SCAN) OF 'IX_TBBHAB10_03' (NON-UNIQUE) (Cost=3
    Card=1) (PARALLEL_COMBINED_WITH_PARENT)
      10    2       PARTITION RANGE (ITERATOR) (PARALLEL_COMBINED_WITH_PARENT)
      11   10         INDEX (RANGE SCAN) OF 'IX_TBJGBA20_02' (NON-UNIQUE) (Cost=3 Card=2)
    (PARALLEL_COMBINED_WITH_PARENT)


    아래는 스칼라서브쿼리가 있고 인라인뷰에서 parallel을 사용하였습니다.

    SELECT D.NM ,
           d.jumin_no ,
           c.in_dt ,
           c.adpt_fr_yyyymm ,
           c.adpt_to_yyyymm
         , (select x.chg_emp_no from tbbhab10 x
            where d.jumin_no  = x.jumin_no
              and d.jumin_seq = x.jumin_seq
              and x.tax_item_cd = '21'
              and x.adpt_to_yyyymm = '201001'
              and x.amt > 0
              and rownum = 1)
    FROM   (
           select /*+ no_merge full(z) parallel(z 16) */ z.jumin_no, z.jumin_seq, z.amt ,
    z.tax_item_cd, bugwa_seq_no, z.in_dt, z.adpt_fr_yyyymm , z.adpt_to_yyyymm
           from   tbbhab10 z
           where  z.TAX_ITEM_CD       = '21'
              AND z.ADPT_FR_YYYYMM   <= '201001'
              AND z.ADPT_TO_YYYYMM   >= '201001'
              and z.amt               = 1
              and exists ( select /*+ use_nl(d cc) index(cc ix_tbbhab10_03) */ 'Y' from
    tbbhab10 cc
                            where cc.jumin_no  = z.jumin_no
                              AND cc.ADPT_FR_YYYYMM   = '201002'
                              AND cc.ADPT_TO_YYYYMM   >= '201002'
                              and cc.tax_item_cd = '21'
                              and cc.amt > 1 )
           ) c ,
           tbjgba20 d   -- Rows=340,962,300  Blocks=9,620,360
    WHERE  d.jumin_no          = c.jumin_no
    AND    d.jumin_seq         = c.jumin_seq

    Execution Plan
    --------------------------------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22K Card=10K Bytes=780K)
       1    0   COUNT (STOPKEY)
       2    1     PARTITION RANGE (SINGLE)
       3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TBBHAB10' (Cost=5 Card=1
    Bytes=45)
       4    3         INDEX (RANGE SCAN) OF 'PK_TBBHAB10' (UNIQUE) (Cost=4 Card=1)
       5    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TBJGBA20' (Cost=6 Card=9 Bytes=351)
       6    5     NESTED LOOPS (Cost=22K Card=10K Bytes=780K)
       7    6       VIEW (Cost=21K Card=1K Bytes=43K)
       8    7         NESTED LOOPS (SEMI) (Cost=21K Card=1K Bytes=105K)
       9    8           PARTITION RANGE (ALL)
      10    9             TABLE ACCESS (FULL) OF 'TBBHAB10' (Cost=10K Card=42K Bytes=2M)
      11    8           PARTITION RANGE (ITERATOR)
      12   11             TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TBBHAB10' (Cost=4 Card=28
    Bytes=1K)
      13   12               INDEX (RANGE SCAN) OF 'IX_TBBHAB10_03' (NON-UNIQUE) (Cost=3
    Card=1)
      14    6       PARTITION RANGE (ITERATOR)
      15   14         INDEX (RANGE SCAN) OF 'IX_TBJGBA20_02' (NON-UNIQUE) (Cost=3 Card=2)


    스칼라서브쿼리와 함께 parallel 을 사용하면 parallel 처리가 안되는되왜 그런가요? 스칼라서브쿼리를 주석처리하고 사용하면 parallel 처리가 잘 됩니다.

    A:                        엑셈 답변 보기
     
    위 글은 (주) 엑셈 온라인 서비스 ASK EXEM 에서 발췌한 것임을 알려 드립니다.

    댓글 0

© Copyright 2001 ~ 2021 EXEM CO., LTD. All Rights Reserved