오라클 쿼리 속도 질문입니다. 0 9 1,258

by 겨울눈 [2016.06.02 11:32:01]


SELECT COL1
,COL2
,COL3
,COL4
,(SELECT SUM(QTY)
         FROM TABLE_B B
         WHERE B.COL1 = A.COL1
                AND B.COL2 = B.COL2
               ) AS QTY

FROM TABLE_A A

이런 쿼리가 있는데 데이터가 많을시 1분 이상 몇건밖에 없을시에도 10초 정도 걸리네요.

이것저것 시도를 해봤지만 어떻게 튜닝을 해야할까요 

참고로 테이블B에 등록되어있는 데이터는 30만건 정도 됩니다.

 

by jkson [2016.06.02 12:18:33]
SELECT COL1
,COL2
,COL3
,COL4
,(SELECT SUM(QTY)
         FROM TABLE_B B
         WHERE B.COL1 = A.COL1
                AND B.COL2 = B.COL2 -- (A.COL2 겠죠?)
               ) AS QTY
 
FROM TABLE_A A
TABLE_B COL1,COL2 컬럼으로 INDEX 생성 되어있는지 확인해보세요.

by 겨울눈 [2016.06.02 12:21:39]

네 A.COL2입니다

TABLE_B의 COL1,COL2는 PK라서 ASC로 인덱스 생성되어 있습니다.


by 마농 [2016.06.02 13:12:26]

정보가 부족하기도 하고 정확하지 않은 듯 하네요.
1. 쿼리 조건절 틀리게 올리신것도 그렇고
  - 정확하게 고치고서도 느린건가요?
2. col1, col2 가 PK 라고 하는 부분도 이상하구요.
  - b 의 PK 구성이 2개 컬럼 뿐인것은 아니겠죠?
3. 건수가 적을때랑 많을 때랑 비교한 부분도 이상하네요?
  - a 의 건수를 얘기하는 건가요?
  - 제시하신 쿼리에는 a 에 대한 조건절이 안보이는데요?
4, 정보가 부족합니다.
  - 그나마, 제시하신 정보마져도 신뢰도가 떨어집니다.


정확한 쿼리를 제시해 주세요.
TABLE 정보, PK, INDEX, 건수, 관계 등 정확한 정보를 제시해 주세요.


by 겨울눈 [2016.06.02 13:26:43]
SELECT B.CO_CD
                        ,B.CNTR_CD
                        ,B.IWH_REQS_NO
                        ,B.IWH_REQS_SEQ

                       
                        ,NVL((SELECT SUM(Z.PILUP_QTY)
                              FROM LE_IWH_DSGNTN X
                                  ,LE_IWH_DSGNTN_DTL Y
                                  ,LE_IWH_DSGNTN_LOC Z

                              WHERE X.CO_CD = Y.CO_CD
                                AND X.CNTR_CD = Y.CNTR_CD
                                AND X.IWH_DSGNTN_NO = Y.IWH_DSGNTN_NO
                                AND Y.CO_CD = Z.CO_CD
                                AND Y.CNTR_CD = Z.CNTR_CD
                                AND Y.IWH_DSGNTN_NO = Z.IWH_DSGNTN_NO
                                AND Y.IWH_DSGNTN_SEQ = Z.IWH_DSGNTN_SEQ
                               

                                AND Y.IWH_REQS_NO = B.IWH_REQS_NO
                                AND Y.IWH_REQS_SEQ = B.IWH_REQS_SEQ
                                ),0) PILUP_QTY

                    FROM LE_IWH_REQS A
                        ,LE_IWH_REQS_DTL B

                       
                      

                    WHERE A.CO_CD = B.CO_CD
                      AND A.CNTR_CD = B.CNTR_CD
                      AND A.IWH_REQS_NO = B.IWH_REQS_NO
                      AND A.IWH_REQS_PRSTAT_CD <> '99'
                     
      AND A.CO_CD = '0001'
      AND A.CNTR_CD = '0001'
                      
                      AND B.ITMS_DISTING_NO = '12345678'

쿼리 전문입니다. 

A,B 조인한 결과수는 150건 정도 됩니다.

서브쿼리로 합계 구해오는 쿼리는 

AND Y.IWH_REQS_NO = B.IWH_REQS_NO
AND Y.IWH_REQS_SEQ = B.IWH_REQS_SEQ

이 두개의 조건을 빼버리면 30만건 정도 됩니다.

애매한 질문 올려 죄송합니다.

릴레이션은 A->B 1:N B->Y 1:N X->Y 1:N Y->Z 1:N 입니다.

A의 PK는 CO_CD,CNTR_CD,IWH_REQS_NO

B의 PK는 CO_CD,CNTR_CD,IWH_REQS_NO,IWH_REQS_SEQ

인덱스는 PK 이외에 따로 지정해 준건 없습니다.

Y의 IWH_REQS_NO,IWH_REQS_SEQ는 PK가 아닌 FK입니다.


by 마농 [2016.06.02 14:18:48]

서브쿼리에서
  - y 의 iwh_reqs_no, iwh_reqs_seq 에 인덱스가 있는지요?
  - y 와 연결되는 x, z 의 컬럼에 인덱스가 있는지요?
  - x 는 꼭 조인해야 하나요? 안해도 결과가 같을 듯?
메인쿼리에서
  - B.ITMS_DISTING_NO = '12345678' 이 조건의 선택도는?
  - 이 컬럼의 선택도가 좋다면 인덱스가 있어야 할 듯.
 


by 겨울눈 [2016.06.02 14:27:48]

y의 iwh_reqs_no, iwh_reqs_seq에는 인덱스가 없습니다.

y와 연결되는 x,y,z의 컬럼들은 모두 pk로 인덱스가 있습니다.

x는 조인 안해도 되겠네요

B.ITMS_DISTING_NO의 선택도는 높은편입니다.

참고로 

 

AND Y.IWH_REQS_NO = B.IWH_REQS_NO
AND Y.IWH_REQS_SEQ = B.IWH_REQS_SEQ
 
이 조건을 빼버리면 몇십초 걸리던 쿼리가 금방 출력됩니다.

by 마농 [2016.06.02 14:40:36]

인덱스가 없다면? 당연히 느리구요.
인덱스가 있어야 합니다.
혹시 Y 의 PK 구성이 어찌 되나요?
해당 두개 항목을 후행으로 포함하고 있다면?
선행 컬럼으로 co_cd, cntr_cd 가 있을 듯 하네요.
선행 컬럼 조건이 누락되어 인덱스를 제대로 타지 못할 듯
 

AND y.co_cd          = b.co_cd         -- 추가
AND y.cntr_cd        = b.cntr_cd       -- 추가
AND y.iwh_reqs_no    = b.iwh_reqs_no
AND y.iwh_reqs_seq   = b.iwh_reqs_seq

해당 조건을 빼면 빠른 것은?
서브쿼리가 한번만 수행되기 때문입니다.
가변 조건이 빠졌기 때문에 동일한 조건으로 계속 수행하게 되는 것이고
동일한 조건으로 반복 수행하면 재수행 안하고 캐시된 값을 이용합니다.

 


by 마농 [2016.06.02 14:54:24]

우선 인덱스가 없다면 만들어야만 할 상황인 듯 합니다.
PK 뿐 아니라 FK 에도 인덱스를 만들도록 하는게 권장사항 입니다.
인덱스를 만들 수 없는 상황이라면?
반복수행되는 서브쿼리를 조인 형태로 변경해야 합니다.
 

SELECT b.co_cd
     , b.cntr_cd
     , b.iwh_reqs_no
     , b.iwh_reqs_seq
     , NVL(SUM(z.pilup_qty), 0) pilup_qty
  FROM le_iwh_reqs       a
     , le_iwh_reqs_dtl   b
     , le_iwh_dsgntn_dtl y
     , le_iwh_dsgntn_loc z
 WHERE a.co_cd           = b.co_cd
   AND a.cntr_cd         = b.cntr_cd
   AND a.iwh_reqs_no     = b.iwh_reqs_no
   AND a.iwh_reqs_prstat_cd <> '99'
   AND b.co_cd           = '0001'
   AND b.cntr_cd         = '0001'
   AND b.itms_disting_no = '12345678'
   AND y.co_cd           = z.co_cd
   AND y.cntr_cd         = z.cntr_cd
   AND y.iwh_dsgntn_no   = z.iwh_dsgntn_no
   AND y.iwh_dsgntn_seq  = z.iwh_dsgntn_seq
   AND y.co_cd           = b.co_cd
   AND y.cntr_cd         = b.cntr_cd
   AND y.iwh_reqs_no     = b.iwh_reqs_no
   AND y.iwh_reqs_seq    = b.iwh_reqs_seq
 GROUP BY b.co_cd, b.cntr_cd, b.iwh_reqs_no, b.iwh_reqs_seq
;

 


by 겨울눈 [2016.06.02 15:00:53]

감사합니다! 

많은 도움되었습니다. 인덱스는 추가할 수 없는 상황이라 조인으로 해보겠습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입