데이터베이스 질문과 답변
header에는 약 10만건 정도의 자료가 있습니다.
detail에는 약 30만간 정도의 자료가 있습니다.
SELECT
SUM (b.qty) qty,
SUM (b.qty * b.price) price,
FROM header a, detail b
WHERE 1=1
AND a.st_no = b.st_no
AND a.ymd BETWEEN '20120301' AND '20120923'
AND a.ymd = b.ymd
AND a.seq_no = b.seq_no
AND a.cancel = 'N'
쿼리 속도가 너무나 느립니다. ㅜㅜ
각각 테이블의 st_no, ymd, seq_no 는 primary key 입니다.
쿼리를 잘못 만든건가요 ?
도움 부탁드립니다.
by 부쉬맨
[2012.09.24 17:46:22]
이렇게해보심이..
SELECT
SUM (b.qty) qty,
SUM (b.qty * b.price) price,
FROM header a, detail b
HEADER,DETAIL 데이터 건수가 10만건,30만건이 맞나요??
점차 늘어나고는 있지만 현재는 그정도 입니다.
혹시 키값이 아닌 다른것도 인덱스에 잡혀계신건 아닌지요??
키값 이외의 인덱스는 없습니다.
header 테이블의 키값이 st_no(각각의 컴퓨터 번호), ymd(날짜), seq_no(순번)
detail 테이블의 키값이 st_no(각각의 컴퓨터 번호), ymd(날짜), seq_no(순번), order_no(주문순번)
detail 테이블의 값은 각각의 주문이 정보들이 들어가 있으며
header 테이블의 값은 각 주문의 합 정보 및 정상/취소(cancel) 의 플래그 값이 들어있습니다.
그래서 header의 값이 예를들어 1건일때 detail의 값은 여러개가 되는 형식입니다.
deatil table 데이터
corea, 20120925, 1, 1, 부품1, 2, 1000, 2000
corea, 20120925, 1, 2, 부품2, 3, 1000, 3000
corea1, 20120925, 2, 1, 부품1, 3, 1000, 3000
corea1, 20120925, 2, 2, 부품2, 3, 1000, 3000
(st_no, ymd, seq_no, order_no, order_name, qty, price, tot_price)
(컴퓨터번호, 일자, 시퀀스넘버, 주문번호, 주문명, 수량, 단가, 총금액)
header table 데이터
corea, 20120925, 1, 5000, N
corea1, 20120925, 2, 6000, Y
(st_no, ymd, seq_no, amt, cancel)
(컴퓨터번호, 일자, 시퀀스번호, 총금액, 결제취소여부)
이렇게 되어 있습니다.
혹시 SUM이 아닌 데이터 추출시에도 속도가 안나오시나요?
비슷하게 80만건이랑 400만건 가지고 테스트 하는데 동일증상인가해서요??
/*+ ordered use_nl(A) full(B) use_nl(B) */
A = 데이터 양이 작은 테이블
B = 데이터 양이 많은 테이블
저랑 동일 증상이시면 전 힌트를 이렇게 주니깐 1분걸리던게 10초로 줄어드네요....
HASH JOIN => NESTED LOOP 으로 변경했습니다.
SUM이 아니면 속도는 괜찮은 편입니다.
//완전초짜 님
답변 감사합니다.
위에 적어주신대로
/*+ ordered use_nl(header) full(detail) use_nl(detail) */
넣고 plan을 보았는데
HASH JOIN => NESTED LOOP 로 변경이 안되네요.
아.. 제가 설명이 부족했네요.. 테이블 명 아닌 alias 적어주시면됩니다.. ㅜ.ㅜ그대로 a,b로사용하시면 되실듯싶네요.. ㅜ.ㅜ 죄송합니다.
//완전초짜 님
답변 감사합니다.
근데 혹시 Group by도 하고 해보신건가요 ?
/*+ ordered use_nl(a) full(b) use_nl(b) */
넣고 했더니 속도가 더 안나옵니다. ㅜㅜ
아.. 저도 그룹바이를 넣고 돌린건데요... 실행계획보면 데이터추출만 조회하면 NESTED LOOP 로 조회해서 빨리나오는데 sum 하면 hash join으로 돌더라구요... 그래서 방식을 바꾸면 되지 않을까 해서 적용했더니 저는 되었습니다... 혹시 제가 저걸 알고쓴건아니라서요.. a,b자리도 한번 바꿔보심이..
ㅜ.ㅜ 다른 방법을 더 찾아볼께요...
//완전초짜 님
답변 감사합니다.
ㅜ.ㅜ 저도 여러가지 방법을 찾는 중입니다. ㅜ.ㅜ
WHERE a.st_no = b.st_no
AND a.seq_no = b.seq_no
AND a.ymd BETWEEN '20120301' AND '20120923'
AND a.cancel = 'N'
by 손님
[2012.09.24 17:56:47]
답변 감사합니다.
Group By를 빼먹어서 다시 씁니다.
SELECT
SUM (b.qty) qty,
SUM (b.qty * b.price) price,
FROM header a, detail b
WHERE 1=1
AND a.st_no = b.st_no
AND a.ymd BETWEEN '20120301' AND '20120923'
AND a.ymd = b.ymd
AND a.seq_no = b.seq_no
AND a.cancel = 'N'
GROUP BY a.st_no, a.ymd
참고로 seq_no는 날짜(ymd)가 바뀔때마다 1부터 다시 시작됩니다.
//부쉬맨 님이 답변 주신것 처럼 ymd를 아래로 옮긴 후 테스트 했는데 마찬가지 입니다. ㅜㅜ
by 손님
[2012.09.24 18:05:09]
--참조하세요--
Explain Plan은
SELECT STATEMENT ALL_ROWS
COST:24154 BYTES:64 CARDINALITY:1
4.SORT AGGREGATE
BYTES:64 CARDINALITY:1
3. HASH JOIN
COST:24154 BYTES:97662848 CARDINALITY:1525357
1. INDEX FAST FULL SCAN INDEX HEADER_IDX
COST:6404 BYTES:26042450 CARDINALITY:1041698
2. INDEX FAST FULL SCAN INDEX DETAIL_IDX
COST:11121 BYTES:77062596 CARDINALITY:1975964
입니다.
Explain Plan은
SELECT STATEMENT ALL_ROWS
COST:24154 BYTES:64 CARDINALITY:1
4.SORT AGGREGATE
BYTES:64 CARDINALITY:1
3. HASH JOIN
COST:24154 BYTES:97662848 CARDINALITY:1525357
1. INDEX FAST FULL SCAN INDEX HEADER_IDX
COST:6404 BYTES:26042450 CARDINALITY:1041698
2. INDEX FAST FULL SCAN INDEX DETAIL_IDX
COST:11121 BYTES:77062596 CARDINALITY:1975964
입니다.
by 손님
[2012.09.24 18:13:05]
HEADER,DETAIL 데이터 건수가 10만건,30만건이 맞나요??
by 손님
[2012.09.24 18:14:38]
점차 늘어나고는 있지만 현재는 그정도 입니다.
by 손님
[2012.09.24 18:15:41]
에고고.. HEADER, DETAIL100만건 300만건 이네요
by 완전초짜
[2012.09.25 10:15:15]
혹시 키값이 아닌 다른것도 인덱스에 잡혀계신건 아닌지요??
by 손님
[2012.09.25 10:34:24]
키값 이외의 인덱스는 없습니다.
by 부쉬맨
[2012.09.25 11:14:05]
master 테이블의 키값이 st_no(시작번호), ymd(날짜), seq_no(순번)
detail 테이블의 키값이 st_no(시작번호), ymd(날짜), seq_no(순번)
mater 테이블의
1 , 20120925, 1
1 , 20120925, 2
1 , 20120925, 3
detail 테이블의
1 , 20120925, 1
1 , 20120925, 2
1 , 20120925, 3
이런형태의 데이터가맞나요?
모델상 master와 detail의 개념이 좀이상해서(머 업무적으로는 잘몰라서)
시작일자, 날짜 요두개만 마스터에서는 존재하고
시작일자, 날짜, 순번 세개는 디테일에 존재해야되고
detail 테이블의 키값이 st_no(시작번호), ymd(날짜), seq_no(순번)
mater 테이블의
1 , 20120925, 1
1 , 20120925, 2
1 , 20120925, 3
detail 테이블의
1 , 20120925, 1
1 , 20120925, 2
1 , 20120925, 3
이런형태의 데이터가맞나요?
모델상 master와 detail의 개념이 좀이상해서(머 업무적으로는 잘몰라서)
시작일자, 날짜 요두개만 마스터에서는 존재하고
시작일자, 날짜, 순번 세개는 디테일에 존재해야되고
by 손님
[2012.09.25 13:54:47]
header 테이블의 키값이 st_no(각각의 컴퓨터 번호), ymd(날짜), seq_no(순번)
detail 테이블의 키값이 st_no(각각의 컴퓨터 번호), ymd(날짜), seq_no(순번), order_no(주문순번)
detail 테이블의 값은 각각의 주문이 정보들이 들어가 있으며
header 테이블의 값은 각 주문의 합 정보 및 정상/취소(cancel) 의 플래그 값이 들어있습니다.
그래서 header의 값이 예를들어 1건일때 detail의 값은 여러개가 되는 형식입니다.
deatil table 데이터
corea, 20120925, 1, 1, 부품1, 2, 1000, 2000
corea, 20120925, 1, 2, 부품2, 3, 1000, 3000
corea1, 20120925, 2, 1, 부품1, 3, 1000, 3000
corea1, 20120925, 2, 2, 부품2, 3, 1000, 3000
(st_no, ymd, seq_no, order_no, order_name, qty, price, tot_price)
(컴퓨터번호, 일자, 시퀀스넘버, 주문번호, 주문명, 수량, 단가, 총금액)
header table 데이터
corea, 20120925, 1, 5000, N
corea1, 20120925, 2, 6000, Y
(st_no, ymd, seq_no, amt, cancel)
(컴퓨터번호, 일자, 시퀀스번호, 총금액, 결제취소여부)
이렇게 되어 있습니다.
by 부쉬맨
[2012.09.25 14:42:44]
추출하는 목적음 그러면
현재결재취소된 컴퓨터 구매내역을 추출할려고하는데
만약에 A라는 구매자가
1번 예시) 결재취소여부 'Y'
=> 이러한 케이스는 추출되지않을껀데, 쿼리내용만보았을때 만약 주문순번이 3인데 1번째는 주문, 취소, 주문 하는케이스도 존재할수있겟네요?
2번 예시) 결재취소여부 'N'
=> 이게 문제인데 , 1번예시와 같이 실제로 저러한 장난(?) 으로 인한 현재시점 의 정상적으로 취소된 내역들만 나온다면 그렇게 1~2건만존재할꺼니깐...
그래서 만약 지금 추출해야되는 내역이
마지막의 취소금액등을 추출하는거라면
어차피 N인게 마지막 주문순번을 가져올테니
주문순번을 인라인뷰형태로
max로 추출하면 해당 디테일의 대상건이 한건이될꺼입니다.
그럼 그걸 조인하게된다면 1=1 조인이므로 속도는 빠르겠지요.
근데 오라클버전이 높은건가??index fast full이 흰트없이도타긴하는군요 통계정보가 수집이되었나...
똑똑한놈인듯
그리고 그게 아니다...라면~!!
결국은 A에서 하는역활은 해당일에 결재취소여부인거인데
그럼 이때는 detail자체에서 일단 계산식을 만들어냅니다.]
SUM (b.qty) qty,
현재결재취소된 컴퓨터 구매내역을 추출할려고하는데
만약에 A라는 구매자가
1번 예시) 결재취소여부 'Y'
=> 이러한 케이스는 추출되지않을껀데, 쿼리내용만보았을때 만약 주문순번이 3인데 1번째는 주문, 취소, 주문 하는케이스도 존재할수있겟네요?
2번 예시) 결재취소여부 'N'
=> 이게 문제인데 , 1번예시와 같이 실제로 저러한 장난(?) 으로 인한 현재시점 의 정상적으로 취소된 내역들만 나온다면 그렇게 1~2건만존재할꺼니깐...
그래서 만약 지금 추출해야되는 내역이
마지막의 취소금액등을 추출하는거라면
어차피 N인게 마지막 주문순번을 가져올테니
주문순번을 인라인뷰형태로
max로 추출하면 해당 디테일의 대상건이 한건이될꺼입니다.
그럼 그걸 조인하게된다면 1=1 조인이므로 속도는 빠르겠지요.
근데 오라클버전이 높은건가??index fast full이 흰트없이도타긴하는군요 통계정보가 수집이되었나...
똑똑한놈인듯
그리고 그게 아니다...라면~!!
결국은 A에서 하는역활은 해당일에 결재취소여부인거인데
그럼 이때는 detail자체에서 일단 계산식을 만들어냅니다.]
SUM (b.qty) qty,
SUM (b.qty * b.price) price,
이계산식을 만들어내고 단순히 detail만 한번돌려보세요.
속도차이를 확인을하기위해서..
그리고 이렇게 만든정보+ 주문순번을 뺀 키값들을 나열합니다.다 max처리 하시면될꺼같네요.
그렇게되면 detail의 형태는 corea1, 20120925, 2, 2, 부품2, 3, qty,price
이렇게되면 결국 마스터와 1=1의 관계가되겠지요.
그냥글로만보았을때 될꺼같은데-_-;;근데 한달의 데이터많네요 ㅎㅎ(한달도안되내,,,ㄷㄷㄷ)
이계산식을 만들어내고 단순히 detail만 한번돌려보세요.
속도차이를 확인을하기위해서..
그리고 이렇게 만든정보+ 주문순번을 뺀 키값들을 나열합니다.다 max처리 하시면될꺼같네요.
그렇게되면 detail의 형태는 corea1, 20120925, 2, 2, 부품2, 3, qty,price
이렇게되면 결국 마스터와 1=1의 관계가되겠지요.
그냥글로만보았을때 될꺼같은데-_-;;근데 한달의 데이터많네요 ㅎㅎ(한달도안되내,,,ㄷㄷㄷ)
by 손님
[2012.09.25 15:29:40]
답변 감사합니다.
오라클은 11g 입니다.
1번 예시) 결재취소여부 'Y'
=> 이러한 케이스는 추출되지않을껀데, 쿼리내용만보았을때 만약 주문순번이 3인데 1번째는 주문, 취소, 주문 하는케이스도 존재할수있겟네요?
-- > 네 해당케이스 존재합니다.
2번 예시) 결재취소여부 'N'
=> 이게 문제인데 , 1번예시와 같이 실제로 저러한 장난(?) 으로 인한 현재시점 의 정상적으로 취소된 내역들만 나온다면 그렇게 1~2건만존재할꺼니깐...
header 테이블의 역할은 detail 테이블에서 정상 주문된것만 합하여 한건의 데이터로 취합하는 역할입니다.
음.. 더욱 간단히 설명하자면..
온라인 구매를 할 시 주문을 넣고 결제를 한 후 결제 취소도 할 수가 있잖아요?
토탈 금액 정보와 정상/취소 여부를 header에서 가지고 있는 겁니다. (정상->N, 취소->Y)
detail은 주문한 상세 정보를 가지고 있는 것이고요.
group by st_no, ymd 를 하여 컴퓨터, 일자별 데이터를 정상 데이터의 취합 금액을 확인하려고 하는 거에요,.
조회 확장을 하면 아마 group by 에 detail 테이블의 order_name 필드도 들어갈꺼 같습니다.
데이터는 점점 늘어나는데 속도는 안나오고 ㅜㅜ
오라클은 11g 입니다.
1번 예시) 결재취소여부 'Y'
=> 이러한 케이스는 추출되지않을껀데, 쿼리내용만보았을때 만약 주문순번이 3인데 1번째는 주문, 취소, 주문 하는케이스도 존재할수있겟네요?
-- > 네 해당케이스 존재합니다.
2번 예시) 결재취소여부 'N'
=> 이게 문제인데 , 1번예시와 같이 실제로 저러한 장난(?) 으로 인한 현재시점 의 정상적으로 취소된 내역들만 나온다면 그렇게 1~2건만존재할꺼니깐...
header 테이블의 역할은 detail 테이블에서 정상 주문된것만 합하여 한건의 데이터로 취합하는 역할입니다.
음.. 더욱 간단히 설명하자면..
온라인 구매를 할 시 주문을 넣고 결제를 한 후 결제 취소도 할 수가 있잖아요?
토탈 금액 정보와 정상/취소 여부를 header에서 가지고 있는 겁니다. (정상->N, 취소->Y)
detail은 주문한 상세 정보를 가지고 있는 것이고요.
group by st_no, ymd 를 하여 컴퓨터, 일자별 데이터를 정상 데이터의 취합 금액을 확인하려고 하는 거에요,.
조회 확장을 하면 아마 group by 에 detail 테이블의 order_name 필드도 들어갈꺼 같습니다.
데이터는 점점 늘어나는데 속도는 안나오고 ㅜㅜ
by 완전초짜
[2012.09.25 15:55:24]
혹시 SUM이 아닌 데이터 추출시에도 속도가 안나오시나요?
비슷하게 80만건이랑 400만건 가지고 테스트 하는데 동일증상인가해서요??
by 완전초짜
[2012.09.25 16:23:35]
/*+ ordered use_nl(A) full(B) use_nl(B) */
A = 데이터 양이 작은 테이블
B = 데이터 양이 많은 테이블
저랑 동일 증상이시면 전 힌트를 이렇게 주니깐 1분걸리던게 10초로 줄어드네요....
HASH JOIN => NESTED LOOP 으로 변경했습니다.
by 손님
[2012.09.25 16:23:49]
SUM이 아니면 속도는 괜찮은 편입니다.
by 부쉬맨
[2012.09.25 16:33:42]
추출되는내역이 그렇다면
정상적으로 주문된것만 합하여 한건의 데이터로 취합하는건데
주문->취소->주문
한거는 어떻게 추출하실려고하는지요???
저껀에 대해서 1,3번의 주문건의 합계를 추출하는건지?
아님 마지막 주문건들에 대해서 날짜별 추출하는건지요?
위와, 같은 경우 2건의 주문된금액이 헤더에 들어가는건가요? 아님 마지막금액만 헤더에 들어가나요?
정상적으로 주문된것만 합하여 한건의 데이터로 취합하는건데
주문->취소->주문
한거는 어떻게 추출하실려고하는지요???
저껀에 대해서 1,3번의 주문건의 합계를 추출하는건지?
아님 마지막 주문건들에 대해서 날짜별 추출하는건지요?
위와, 같은 경우 2건의 주문된금액이 헤더에 들어가는건가요? 아님 마지막금액만 헤더에 들어가나요?
by 손님
[2012.09.25 16:37:23]
제가 설명이 부정확 했나보네요 ^^;;
detail의 취소된 내역은 바로 delete 합니다.
그래서 detail 테이블에는 취소플래그가 없는거구요,
실질적으로 계산되어진 정보만 detail 테이블에 가지고 있습니다.
header에는 detail의 총금액 정보와 후에 취소 했는지 안했는지만 cancel을 보고 판단합니다.
detail의 취소된 내역은 바로 delete 합니다.
그래서 detail 테이블에는 취소플래그가 없는거구요,
실질적으로 계산되어진 정보만 detail 테이블에 가지고 있습니다.
header에는 detail의 총금액 정보와 후에 취소 했는지 안했는지만 cancel을 보고 판단합니다.
by 손님
[2012.09.25 16:46:58]
//완전초짜 님
답변 감사합니다.
위에 적어주신대로
/*+ ordered use_nl(header) full(detail) use_nl(detail) */
넣고 plan을 보았는데
HASH JOIN => NESTED LOOP 로 변경이 안되네요.
by 완전초짜
[2012.09.25 16:49:33]
아.. 제가 설명이 부족했네요.. 테이블 명 아닌 alias 적어주시면됩니다.. ㅜ.ㅜ그대로 a,b로사용하시면 되실듯싶네요.. ㅜ.ㅜ 죄송합니다.
by 손님
[2012.09.25 16:58:15]
//완전초짜 님
답변 감사합니다.
근데 혹시 Group by도 하고 해보신건가요 ?
/*+ ordered use_nl(a) full(b) use_nl(b) */
넣고 했더니 속도가 더 안나옵니다. ㅜㅜ
by 완전초짜
[2012.09.25 17:05:25]
아.. 저도 그룹바이를 넣고 돌린건데요... 실행계획보면 데이터추출만 조회하면 NESTED LOOP 로 조회해서 빨리나오는데 sum 하면 hash join으로 돌더라구요... 그래서 방식을 바꾸면 되지 않을까 해서 적용했더니 저는 되었습니다... 혹시 제가 저걸 알고쓴건아니라서요.. a,b자리도 한번 바꿔보심이..
ㅜ.ㅜ 다른 방법을 더 찾아볼께요...
by 손님
[2012.09.25 17:23:13]
//완전초짜 님
답변 감사합니다.
ㅜ.ㅜ 저도 여러가지 방법을 찾는 중입니다. ㅜ.ㅜ
) 버튼을 클릭하여 작성 하시면 됩니다.