쿼리문 튜닝즘 부탁드립니다. 0 16 1,424

by 캔디는맛있어 [2016.05.04 10:03:59]


결과값.png (46,421Bytes)

select 
 tmp.inv_qty,
barcode_sub||'-'||  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  >= TO_NUMBER('390')
                THEN TO_NUMBER('390')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  < TO_NUMBER('390')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )
        END)||'-'||un||decode(barcode2,000000,null,'-E') as BARCODE1,
decode(barcode2,000000,null,barcode2)as barcode2,
decode(barcode2_name,'-0',null,barcode2_name) as barcode2_name,
barcode_sub||'-'||  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  >= TO_NUMBER('390')
                THEN TO_NUMBER('390')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  < TO_NUMBER('390')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )
        END)||'-'||un||decode(barcode2,000000,null,'-E')||decode(barcode2,000000,null,'-'||barcode2)as barcode3,
mat_no,
bat_no,
Des,
  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  >= TO_NUMBER('390')
                THEN TO_NUMBER('390')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )  < TO_NUMBER('390')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('390') )
        END)||' '||un as qty,
add_date,
user_id,
user_name,
inv_no
FROM
(
select 
IVBH.BATCH_SEQ,
podt.item_no||'-'||ivbh.batch_no as barcode_sub ,
podt.SALES_NO||podt.sales_seq as barcode2 ,
podt.SALES_NO||'-'||


CASE WHEN TRIM(TRANSLATE(podt.sales_seq, '0123456789', '          ')) IS NULL

    THEN TO_CHAR(TO_NUMBER(podt.sales_seq))

    ELSE podt.sales_seq

    END           AS barcode2_name,
podt.item_no as mat_no,
ivbh.batch_no as bat_no,
ivdt.description_loc as Des    ,

substr(ivhd.VENDOR_GI_DATE,0,4)||'/'||substr(ivhd.VENDOR_GI_DATE,5,2)||'/'||substr(ivhd.VENDOR_GI_DATE,7,2) as add_date,
ivbh.add_user_id as user_id,
ivdt.add_user_name_loc as user_name,
ivdt.inv_no||'/'||


CASE WHEN TRIM(TRANSLATE(ivdt.inv_seq, '0123456789', '          ')) IS NULL

    THEN TO_CHAR(TO_NUMBER(ivdt.inv_seq))

    ELSE ivdt.inv_seq

    END           AS inv_no,    
    ivdt.unit_measure as un  ,
    ivdt.inv_qty
from 
icoypodt podt,
icomivbh ivbh,
icomivdt ivdt,
icomivhd ivhd


where 
 IVHD.HOUSE_CODE = '100'
AND podt.po_no=ivdt.po_no 
AND IVDT.VENDOR_CODE =  '100011'
and podt.po_seq= ivdt.po_seq
and ivdt.inv_no = ivbh.INV_NO
and ivdt.inv_seq=ivbh.inv_seq
and ivdt.inv_no=ivhd.inv_no
and podt.po_no=ivbh.po_no
and podt.po_seq=ivbh.po_seq
and podt.status !='D'
and ivbh.status !='D'
and ivdt.status !='D'
and ivhd.status !='D'

AND IVDT.INV_NO = '1800631513'
AND IVDT.INV_SEQ = '000010'
AND IVBH.BATCH_NO = '1408290206'
AND IVBH.BATCH_SEQ = '000001') TMP, COPY_T TT
where        
      
     rownum <='3'  
     
UNION   all

select 
 tmp.inv_qty,
barcode_sub||'-'||  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  >= TO_NUMBER('130')
                THEN TO_NUMBER('130')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  < TO_NUMBER('130')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )
        END)||'-'||un||decode(barcode2,000000,null,'-E') as BARCODE1,
decode(barcode2,000000,null,barcode2)as barcode2,
decode(barcode2_name,'-0',null,barcode2_name) as barcode2_name,
barcode_sub||'-'||  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  >= TO_NUMBER('130')
                THEN TO_NUMBER('130')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  < TO_NUMBER('130')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )
        END)||'-'||un||decode(barcode2,000000,null,'-E')||decode(barcode2,000000,null,'-'||barcode2)as barcode3,
mat_no,
bat_no,
Des,
  (CASE
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  >= TO_NUMBER('130')
                THEN TO_NUMBER('130')
            WHEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )  < TO_NUMBER('130')
                THEN NVL(TMP.inv_qty, 0) - ( TT.NO * TO_NUMBER('130') )
        END)||' '||un as qty,
add_date,
user_id,
user_name,
inv_no
FROM
(
select 
IVBH.BATCH_SEQ,
podt.item_no||'-'||ivbh.batch_no as barcode_sub ,
podt.SALES_NO||podt.sales_seq as barcode2 ,
podt.SALES_NO||'-'||


CASE WHEN TRIM(TRANSLATE(podt.sales_seq, '0123456789', '          ')) IS NULL

    THEN TO_CHAR(TO_NUMBER(podt.sales_seq))

    ELSE podt.sales_seq

    END           AS barcode2_name,
podt.item_no as mat_no,
ivbh.batch_no as bat_no,
ivdt.description_loc as Des    ,

substr(ivhd.VENDOR_GI_DATE,0,4)||'/'||substr(ivhd.VENDOR_GI_DATE,5,2)||'/'||substr(ivhd.VENDOR_GI_DATE,7,2) as add_date,
ivbh.add_user_id as user_id,
ivdt.add_user_name_loc as user_name,
ivdt.inv_no||'/'||


CASE WHEN TRIM(TRANSLATE(ivdt.inv_seq, '0123456789', '          ')) IS NULL

    THEN TO_CHAR(TO_NUMBER(ivdt.inv_seq))

    ELSE ivdt.inv_seq

    END           AS inv_no,    
    ivdt.unit_measure as un  ,
    ivdt.inv_qty
from 
icoypodt podt,
icomivbh ivbh,
icomivdt ivdt,
icomivhd ivhd


where 
 IVHD.HOUSE_CODE = '100'
AND podt.po_no=ivdt.po_no 
AND IVDT.VENDOR_CODE =  '100011'
and podt.po_seq= ivdt.po_seq
and ivdt.inv_no = ivbh.INV_NO
and ivdt.inv_seq=ivbh.inv_seq
and ivdt.inv_no=ivhd.inv_no
and podt.po_no=ivbh.po_no
and podt.po_seq=ivbh.po_seq
and podt.status !='D'
and ivbh.status !='D'
and ivdt.status !='D'
and ivhd.status !='D'

AND IVDT.INV_NO = '1800631513'
AND IVDT.INV_SEQ = '000010'
AND IVBH.BATCH_NO = '1408290206'
AND IVBH.BATCH_SEQ = '000001') TMP, COPY_T TT
where        
      
     rownum <='8'

 

오라클 쿼리문입니다.

그리고 첨부한 그림이 결과 값이고요

노란색의 빨간글씨같이 갯수가 저렇게 나오게 쿼리문튜닝즘

부탁드립니다.

 

오라클에 돌리는 11초나 걸리는데 너무 오래걸리는 것 같아서요 ㅠㅠ

 

 

by 우리집아찌 [2016.05.04 10:11:55]

COPY_T 는 무었이지요.

아마 CROSS JOIN 걸려서 상당히 양이 많아질듯한데요.

COPY_T 의미없으시면 빼시는게..


by 캔디는맛있어 [2016.05.04 10:23:48]

COPY_T 가 있어야 나머지 값을 구할 수 있어서요 

출력물을 예로 보면 

1000/130을 하면 130이 7개고 90남는거잔아요

이렇게 나오게하기위해서 COPY_T 를 써서 나머지를 구하는 방법입니다.


by 마농 [2016.05.04 10:38:43]

두개 Union 쿼리를 보면 TMP 부분은 동일하고
 - TO_NUMBER('390') 과 rownum <='3'
 - TO_NUMBER('130') 과 rownum <='8'
이렇게 차이가 나네요?


COPT_T 는 어차피 Rownum 으로 제한 했으니 큰 무리는 없을 듯 하고
TMP 자체가 느릴 듯 하네요? 5초 정도 걸릴 듯?
5초 짜리 두번 돌리고 있네요.
TMP 가 1건 가져오는게 아닐까 생각되는데? 맞나요?
1건 가져오는데 5초 걸린다면? 적절한 인덱스가 없는듯 한데요?


by 캔디는맛있어 [2016.05.04 10:51:09]

TMP 가 1건 가져오는게 아닐까 생각되는데? 맞나요?

네 맞습니다. 1건의 정보를4개의 테이블에 저장되어있어 이걸 함친 다음에 수량을 노나서 출력하는 것입니다.
1건 가져오는데 5초 걸린다면? 적절한 인덱스가 없는듯 한데요?

해당 테이블의 인덱스들은 존재 합니다. 각각의 대표 PK키들이 인덱스인데... 

두개 Union 쿼리를 보면 TMP 부분은 동일하고
 - TO_NUMBER('390') 과 rownum <='3'
 - TO_NUMBER('130') 과 rownum <='8'
이렇게 차이가 나네요?

네 맞습니다. 결과 값이 390 390 220 130 130 130 130 130 130 130 90 이렇게
390이 2개 220 1개 130이 7개 90이 1개로 나오면됩니다.
순서는 상관없습니다.

by 마농 [2016.05.04 11:03:44]

TMP 가 5초 걸리는거 맞나요?

각 테이블의 PK 가 어찌 되나요?


by 캔디는맛있어 [2016.05.04 11:19:22]

TMP만 따로 돌려보니 5초가 딱이네요 ㄷㄷㄷ

각 테이블의 PK키는

icomivhd = inv_no ,HOUSE_CODE

icomivdt = inv_no,inv_seq ,HOUSE_CODE

icoypodt = po_no,po_seq,HOUSE_CODE

icomivbh = BATCH_SEQ,BATCH_NO,INV_SEQ,INV_NO,HOUSE_CODE

입니다.


by 마농 [2016.05.04 11:23:20]

HOUSE_CODE 에 대한 조인 조건들이 죄다 누락되어 있네요.


by 캔디는맛있어 [2016.05.04 11:32:15]

근데 어짜피 house_code는 싹다 값이 100이라서 안써도 조회되는데는 상관없긴해요 ;;;

그래서 그런지 house_code를 지워봤자 전체적으로 11초 걸리네요 ;;; 조건문이 길어지면 길어질수록 속도는 더느려지는거 아닌가요?


by 캔디는맛있어 [2016.05.04 12:12:47]

혹시 비슷한 문법같은거 묶어서 불러주는 방법은 SQL에는 그런거 없겠죠;;;

 

자바의 함수사용하듯이요


by 마농 [2016.05.04 12:23:31]

짐작으로만 하지 마시고 실제로 해보세요.

실제로 해보시고 짐작이 맞았구나 틀렸구나는 나중에 판단하시면 됩니다.

house_code = '100' 조건을 모든 테이블에 걸어보세요.


by 마농 [2016.05.04 12:38:09]

조건절을 다음과 같이 바꿔 보세요.

SELECT *
  FROM icoypodt podt
     , icomivbh ivbh
     , icomivdt ivdt
     , icomivhd ivhd
 WHERE ivbh.inv_no      = ivdt.inv_no 
   AND ivbh.inv_seq     = ivdt.inv_seq
   AND ivbh.house_code  = ivdt.house_code
   AND ivbh.po_no       = podt.po_no
   AND ivbh.po_seq      = podt.po_seq
   AND ivbh.house_code  = podt.house_code
   AND ivbh.inv_no      = ivhd.inv_no 
   AND ivbh.house_code  = ivhd.house_code
   AND ivbh.house_code  = '100'
   AND ivbh.inv_no      = '1800631513'
   AND ivbh.inv_seq     = '000010'
   AND ivbh.batch_no    = '1408290206'
   AND ivbh.batch_seq   = '000001'
   AND podt.status     != 'D'
   AND ivbh.status     != 'D'
   AND ivdt.status     != 'D'
   AND ivhd.status     != 'D'
;

 


by 캔디는맛있어 [2016.05.04 13:04:57]

앵간한걸 bh로 해결하니 엄청빨라지네요 ^^ 감사합니다.


by 마농 [2016.05.04 13:17:30]

5가지 조건을 모두 키로 가진 테이블이 ivbh 이므로
조건을 이 테이블에 걸어 인덱스 유니크 스캔 유도하구요.
나머지 테이블은 이 테이블을 기준으로 조인합니다.
조인 키로 house_code 모두 추가한거구요.


올려주신 컬럼 순서가 인덱스 컬럼 순서가 맞나요?
제 생각에는 house_code 가 선두 컬럼이 아닐까? 생각되네요?


by 캔디는맛있어 [2016.05.04 13:31:48]

ㄷㄷㄷ 소~~오 름

어떻게.. 선두컬럼까지... 해킹하신거아니시죠;;;;

아무튼 이해하기쉬운 답변 감사합니다.


by 마농 [2016.05.04 13:36:48]

인덱스의 선두컬럼 조건이 누락된채로 중간컬럼부터 조건을 걸면?
인덱스를 제대로 활용하지 못합니다.


테이블 설계시 공통적으로 회사코드 등이 선두로 오게 되는데...
이게 한 회사에서 사용하는 시스템이다보니 값이 모두 동일하죠.(글로벌 회사가 아닌 이상)
무의미하게 조건을 줘야 하고
조인 할때마다 조건을 줘야 하니
여간 불편한게 아니죠.
그렇다고 이 조건을 뺏다가는 위와 같은 상황을 맞이하게 됩니다.


by 마농 [2016.05.04 16:26:14]
SELECT a.inv_qty
     , barcode_sub ||'-'
       || CASE WHEN a.inv_qty >= b.x * c.no THEN b.x ELSE MOD(a.inv_qty, b.x) END ||'-'|| un
       || barcode1
       AS barcode1
     , barcode2
     , barcode2_name
     , barcode_sub ||'-'
       || CASE WHEN a.inv_qty >= b.x * c.no THEN b.x ELSE MOD(a.inv_qty, b.x) END ||'-'|| un
       || barcode1
       || barcode2
       AS barcode3
     , mat_no
     , bat_no
     , Des
     , CASE WHEN a.inv_qty >= b.x * c.no THEN b.x ELSE MOD(a.inv_qty, b.x) END ||' '|| un AS qty
     , add_date
     , user_id
     , user_name
     , inv_no
  FROM (SELECT ivbh.batch_seq
             , podt.item_no  ||'-'|| ivbh.batch_no                                       AS barcode_sub
             , NVL2(podt.sales_no, '-E-'                                           , '') AS barcode1
             , NVL2(podt.sales_no, podt.sales_no || podt.sales_seq                 , '') AS barcode2
             , NVL2(podt.sales_no, podt.sales_no ||'-'|| LTRIM(podt.sales_seq, '0'), '') AS barcode2_name
             , podt.item_no                                   AS mat_no
             , ivbh.batch_no                                  AS bat_no
             , ivdt.description_loc                           AS Des
             , TO_CHAR(TO_DATE(ivhd.vendor_gi_date
                              , 'yyyymmdd'), 'yyyy/mm/dd')    AS add_date
             , ivbh.add_user_id                               AS user_id
             , ivdt.add_user_name_loc                         AS user_name
             , ivdt.inv_no   ||'/'|| LTRIM(ivdt.inv_seq, '0') AS inv_no
             , ivdt.unit_measure                              AS un
             , ivdt.inv_qty
          FROM icoypodt podt
             , icomivbh ivbh
             , icomivdt ivdt
             , icomivhd ivhd
         WHERE ivbh.inv_no      = ivdt.inv_no
           AND ivbh.inv_seq     = ivdt.inv_seq
           AND ivbh.house_code  = ivdt.house_code
           AND ivbh.po_no       = podt.po_no
           AND ivbh.po_seq      = podt.po_seq
           AND ivbh.house_code  = podt.house_code
           AND ivbh.inv_no      = ivhd.inv_no
           AND ivbh.house_code  = ivhd.house_code
           AND ivbh.house_code  = '100'
           AND ivbh.inv_no      = '1800631513'
           AND ivbh.inv_seq     = '000010'
           AND ivbh.batch_no    = '1408290206'
           AND ivbh.batch_seq   = '000001'
           AND podt.status     != 'D'
           AND ivbh.status     != 'D'
           AND ivdt.status     != 'D'
           AND ivhd.status     != 'D'
        ) a
     , (SELECT 1 no, 390 x FROM dual
        UNION ALL SELECT 2, 130 FROM dual
        ) b
     , copy_t c
 WHERE c.no <= CEIL(a.inv_qty / b.x)
 ORDER BY b.no, c.no
;

 

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