데이터베이스 질문과 답변
by 캔디는맛있어 [2016.05.04 10:03:59]
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초나 걸리는데 너무 오래걸리는 것 같아서요 ㅠㅠ
COPY_T 는 무었이지요.
아마 CROSS JOIN 걸려서 상당히 양이 많아질듯한데요.
COPY_T 의미없으시면 빼시는게..
COPY_T 가 있어야 나머지 값을 구할 수 있어서요
출력물을 예로 보면
1000/130을 하면 130이 7개고 90남는거잔아요
이렇게 나오게하기위해서 COPY_T 를 써서 나머지를 구하는 방법입니다.
두개 Union 쿼리를 보면 TMP 부분은 동일하고
- TO_NUMBER('390') 과 rownum <='3'
- TO_NUMBER('130') 과 rownum <='8'
이렇게 차이가 나네요?
COPT_T 는 어차피 Rownum 으로 제한 했으니 큰 무리는 없을 듯 하고
TMP 자체가 느릴 듯 하네요? 5초 정도 걸릴 듯?
5초 짜리 두번 돌리고 있네요.
TMP 가 1건 가져오는게 아닐까 생각되는데? 맞나요?
1건 가져오는데 5초 걸린다면? 적절한 인덱스가 없는듯 한데요?
TMP 가 1건 가져오는게 아닐까 생각되는데? 맞나요?
네 맞습니다. 1건의 정보를4개의 테이블에 저장되어있어 이걸 함친 다음에 수량을 노나서 출력하는 것입니다.
1건 가져오는데 5초 걸린다면? 적절한 인덱스가 없는듯 한데요?
해당 테이블의 인덱스들은 존재 합니다. 각각의 대표 PK키들이 인덱스인데...
두개 Union 쿼리를 보면 TMP 부분은 동일하고
- TO_NUMBER('390') 과 rownum <='3'
- TO_NUMBER('130') 과 rownum <='8'
이렇게 차이가 나네요?
TMP 가 5초 걸리는거 맞나요?
각 테이블의 PK 가 어찌 되나요?
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
입니다.
HOUSE_CODE 에 대한 조인 조건들이 죄다 누락되어 있네요.
근데 어짜피 house_code는 싹다 값이 100이라서 안써도 조회되는데는 상관없긴해요 ;;;
그래서 그런지 house_code를 지워봤자 전체적으로 11초 걸리네요 ;;; 조건문이 길어지면 길어질수록 속도는 더느려지는거 아닌가요?
혹시 비슷한 문법같은거 묶어서 불러주는 방법은 SQL에는 그런거 없겠죠;;;
자바의 함수사용하듯이요
짐작으로만 하지 마시고 실제로 해보세요.
실제로 해보시고 짐작이 맞았구나 틀렸구나는 나중에 판단하시면 됩니다.
house_code = '100' 조건을 모든 테이블에 걸어보세요.
조건절을 다음과 같이 바꿔 보세요.
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' ;
앵간한걸 bh로 해결하니 엄청빨라지네요 ^^ 감사합니다.
5가지 조건을 모두 키로 가진 테이블이 ivbh 이므로
조건을 이 테이블에 걸어 인덱스 유니크 스캔 유도하구요.
나머지 테이블은 이 테이블을 기준으로 조인합니다.
조인 키로 house_code 모두 추가한거구요.
올려주신 컬럼 순서가 인덱스 컬럼 순서가 맞나요?
제 생각에는 house_code 가 선두 컬럼이 아닐까? 생각되네요?
ㄷㄷㄷ 소~~오 름
어떻게.. 선두컬럼까지... 해킹하신거아니시죠;;;;
아무튼 이해하기쉬운 답변 감사합니다.
인덱스의 선두컬럼 조건이 누락된채로 중간컬럼부터 조건을 걸면?
인덱스를 제대로 활용하지 못합니다.
테이블 설계시 공통적으로 회사코드 등이 선두로 오게 되는데...
이게 한 회사에서 사용하는 시스템이다보니 값이 모두 동일하죠.(글로벌 회사가 아닌 이상)
무의미하게 조건을 줘야 하고
조인 할때마다 조건을 줘야 하니
여간 불편한게 아니죠.
그렇다고 이 조건을 뺏다가는 위와 같은 상황을 맞이하게 됩니다.
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 ;
) 버튼을 클릭하여 작성 하시면 됩니다.