데이터베이스 질문과 답변
오라클 9i 입니다.
토드에서 아래와 같이 1번쿼리와 2번쿼리 경우 속도에서 차이가 납니다. (1번쿼리는 바로조회되는데., 2번쿼리는 1분이 넘어도 조회가 안되네요...)
1번과 2번의 차이가 where 절차이( 아규먼트 or 직접명기) 차이밖에 없거든요..
이게 정상인건가요?
1번+++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT YY ,
MM,
HOGI ,
SUM(SL_WGT) ,
SUM(TOT_WGT) ,
ROUND(SUM(SL_WGT) / SUM(TOT_WGT) * 100 ,2) YUL
FROM
(
SELECT TO_CHAR(IB_ILJA ,'YYYY') AS YY ,
TO_CHAR(IB_ILJA ,'MM' ) AS MM ,
HOGI ,
0 SL_WGT,
SUM(MC_WGT) TOT_WGT
FROM SMDHP.SM_MCD A
WHERE IB_ILJA BETWEEN '20170101' AND '20170930'
GROUP BY TO_CHAR(IB_ILJA ,'YYYY'),TO_CHAR(IB_ILJA ,'MM') ,HOGI
UNION ALL
SELECT TO_CHAR(IB_ILJA ,'YYYY') AS YY ,
TO_CHAR(IB_ILJA ,'MM' ) AS MM ,
HOGI ,
0 SL_WGT,
SUM(MC_WGT) TOT_WGT
FROM SMDHP.SM_MCD A
WHERE IB_ILJA BETWEEN '20160101' AND '20160930'
GROUP BY TO_CHAR(IB_ILJA ,'YYYY'),TO_CHAR(IB_ILJA ,'MM') ,HOGI
)
WHERE HOGI = '1'
GROUP BY YY, MM, HOGI
ORDER BY HOGI, YY DESC , MM
1번쿼리 실행계획 +++++++++++++++++++++++++++++++++++++++++++++++++++++
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 2 11
SORT GROUP BY 2 48 11
VIEW 2 48 10
UNION-ALL
SORT GROUP BY 1 15 5
FILTER
TABLE ACCESS BY INDEX ROWID SMDHP.SM_MCD 222 3 K 3
INDEX RANGE SCAN SMDHP.IDX_SM_MCD02 887 2
SORT GROUP BY 1 15 5
FILTER
TABLE ACCESS BY INDEX ROWID SMDHP.SM_MCD 222 3 K 3
INDEX RANGE SCAN SMDHP.IDX_SM_MCD02 887 2
2번. +++++++++++++++++++++++++++++++++++++++++++++++++++++
where 절을 .. 아래와같이 변수(아규먼트) 로 조회 할 경우.
A.IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD'))
SELECT YY ,
MM,
HOGI ,
SUM(SL_WGT) ,
SUM(TOT_WGT) ,
ROUND(SUM(SL_WGT) / SUM(TOT_WGT) * 100 ,2) YUL
FROM
(
SELECT TO_CHAR(IB_ILJA ,'YYYY') AS YY ,
TO_CHAR(IB_ILJA ,'MM' ) AS MM ,
HOGI ,
0 SL_WGT,
SUM(MC_WGT) TOT_WGT
FROM SMDHP.SM_MCD A
WHERE IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD'))
GROUP BY TO_CHAR(IB_ILJA ,'YYYY'),TO_CHAR(IB_ILJA ,'MM') ,HOGI
UNION ALL
SELECT TO_CHAR(IB_ILJA ,'YYYY') AS YY ,
TO_CHAR(IB_ILJA ,'MM' ) AS MM ,
HOGI ,
0 SL_WGT,
SUM(MC_WGT) TOT_WGT
FROM SMDHP.SM_MCD A
WHERE IB_ILJA BETWEEN ADD_MONTHS(TO_DATE(:AG_YYMM1||'01','RRRRMMDD'),-12) AND
LAST_DAY(ADD_MONTHS(TO_DATE(:AG_YYMM2|| '01','RRRR-MM-DD'),-12))
GROUP BY TO_CHAR(IB_ILJA ,'YYYY'),TO_CHAR(IB_ILJA ,'MM') ,HOGI
)
WHERE HOGI = '1'
GROUP BY YY, MM, HOGI
ORDER BY HOGI, YY DESC , MM
2번쿼리 실행계획 +++++++++++++++++++++++++++++++++++++++++++++++++++++
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 K 433
SORT GROUP BY 1 K 31 K 433
VIEW 1 K 31 K 428
UNION-ALL
SORT GROUP BY 671 9 K 214
FILTER
TABLE ACCESS BY INDEX ROWID SMDHP.SM_MCD 1 K 18 K 208
INDEX RANGE SCAN SMDHP.IDX_SM_MCD03 493 K 8
SORT GROUP BY 671 9 K 214
FILTER
TABLE ACCESS BY INDEX ROWID SMDHP.SM_MCD 1 K 18 K 208
INDEX RANGE SCAN SMDHP.IDX_SM_MCD03 493 K 8
WHERE IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD'))
조건절로 입력했을경우 값이 다른데요?
1번 쿼리의 조건절의 결과값 : 20170101
2번 쿼리의 조건절의 결과값 : 2017-01-01
맞은결과가 나오는건가요??
죄송합니다 질문수정했습니다....수정한내용과 별개로...원인이 답변해주신이유라면....
답변해주신.... 20170101 , 2017-01-01 에 따라서 조회코스트가 다른가요?
조건절의 데이터가 달라서
조회되는 데이터가 다르기 때문에 검증의 척도가 일정치가 않습니다.
별다른내용아니라 조건절의 데이터값 때문에 발생된 문제일수도있고요,
인라인뷰쪽꺼만 따로 조회해보면 사실 많이 차이는 안나는데...전체쿼리에선 차이가 너무 납니다....
조건절의 데이타를 동일한 포멧 '2017-01-01' 로 바꿔도 동일한 속도차이가 납니다. ㅡㅡ
1번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD02
2번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD03
타는 인덱스도 다른데요?
그렇군요
1번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD02 인덱스칼럼 : ib_ilja , hogi, lot
2번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD03 인덱스칼럼 : hogi, lot
이렇게되어 있는데...
WHERE IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD')) 이걸썻을경우.... ib_ilja 포함 인덱스를 못타는거 같은데요...
이게 정상인가요? 제가 알기론 where 절에 ib_ilja 에 변형을 가하지 않으면 인덱스 타는거 아니던가요?
예를들면 where to_char(ib_ilja ,'yyyymm') ...
이런식으로 변형을 가하면 인덱스를 못탄다고 알고 있습니다만... 예시의경우는 타야되는거 아닐까요? .
to_date 자체가 인덱스를 변현하기때문에
타지못합니다.
답변감사드립니다. 정확히 이해하기 위해 질문좀 더 드려보겠습니다.
아래의 경우. 1, 2번 모두 인덱스 못타나요?
저는 테이블컬럼값에 변형을준 1번만 안되고,
2번은 컬럼에 변형을 준게 아니기(아규먼트에 변형을가함) 때문에 인덱스를 타는걸로 알고 있었네요...
1) where to_char(ib_ilja ,'yyyymm') = '201701'
2) where ib_ilja = to_date('201701' || '01')
날짜 컬럼이 to_date로 감쌓여있는지 알앗네요.
말씀하신게맞습니다.
그런데 저는 궁금한게
sql1 : 조건값은 20170925이고
sql2 : 조건값은 2017-09-25인데 같은 결과를 얻어야되느냐가 궁금합니다.
9i는 RBO(rull base) 이기 때문에 데이터 분포에 따라서 인덱스를 제거하고 수행했을꺼입니다.
`-` 형태로 들어가있는 데이터 분포가 얼마인지
`-` 형태가 없는 데이터 분포가 얼마인지 확인해보심이..
아니면 강제로 index02를 힌트로 제어해보심이 어떠신지요?
1번 쿼리는 문자열 조건을 주었고 / 2번 쿼리는 날짜형 조건을 주었네요.
1번 쿼리가 빨랐다면? 해당 컬럼이 문자형인 듯 하네요.
2번 쿼리의 조건을 to_char 를 이용해 문자열로 바꾸셔야 합니다.
그거 이외에도 문제가 많습니다.
1. group by 이후에 호기 조건을 주는데
- group by 하기 전에 조건을 주는게 좋습니다.
2. 두가지 union sum 이 있는데. 두개 쿼리의 sum 의 위치가 서로 같네요?
- 전년도와 당해년도 sum 의 위치가 달라야 맞을 듯 하네요.
3. union 으로 두번 쿼리하는데
- or 절로 한번에 쿼리하여
- select 절에서 sum(case 로 두가지 sum 을 구하시는게 좋을 듯.
IB_ILJA 가 문자형 컬럼이죠? 문자형 컬럼이라고 할 때
IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD'))
위와 같이 문자형 컬럼과 일자형 컬럼을 비교하시면
날짜형이 우선순위가 더 높아 묵시적으로 형변환이 일어납니다.
내부적으로는
TO_DATE(IB_ILJA,'RRRRMMDD') BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD'))
이렇게 바뀝니다. 묵시적 형변환으로 인덱스 사용 못 합니다.
ib_ilja 의 타입은 date 입니다....ㅡㅡ
그렇다면 형변환이 왜 일어났을까요?
테이블 스키마상 DATE형인 게 확실한가요? 그렇다면 조금 이상한데요..
다시 확인해보세요.
SELECT DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'SM_MCD'
AND OWNER = 'SMDHP'
AND COLUMN_NAME = 'IB_ILJA'
date 타입이 확실하구요...제가 테스트 해보니까 hogi 조건이 있느냐 없느냐에 따라서 달라집니다.
위에 부쉬멘님께도 말씀드렷지만
1번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD02 인덱스칼럼 : ib_ilja , hogi, lot
2번 SQL : INDEX RANGE SCAN SMDHP.IDX_SM_MCD03 인덱스칼럼 : hogi, lot
인덱스가 이렇게 구성되어 있는데요...호기 조건이들어가면 2번 인덱스 타면서 느려집니다. ㅡㅡ
문제가 좀 복합적인거 같아요... 1번쿼리는 호기 조건을 넣어도...1번 인덱스르 탑니다.
date형이 확실하다면 옵티마이저 입장에서 생각해보면
1번 쿼리는 날짜가 명시적으로 들어왔으니 확실히 SMDHP.IDX_SM_MCD02 인덱스를 타는 게 맞다고 판단할 수 있는 것이고
2번 쿼리는 날짜 조건이 전체 데이터에서 상당수 이상이 되면 오히려 SMDHP.IDX_SM_MCD03 인덱스를 타는 게 맞다고 판단하여 실행계획을 바꾼 게 아닌가 추측이 되는데요.
통계정보가 틀렸을 수도 있고 이런 경우에는 힌트를 사용하시는 게 나을 것 같아요.
1. 인덱스 힌트를 이용해 보세요.
- /*+ INDEX(a idx_sm_mcd02) */ -- 02 번 인덱스를 이용하세요.
- /*+ NO_INDEX(a idx_sm_mcd03) */ -- 03 번 인덱스를 이용하지 마세요.
2. 날짜 비교시 주의사항
- date 컬럼에 시분초 까지 저장이 되어 있다면? 작성하신 조건은 틀렸습니다.
- 마지막날 23:59:59 까지 포함되도록 조건을 바꾸셔야 합니다.
모두 모든분들 답변 감사드립니다.
1. 우선 힌트로 해결했습니다.
2. 해당칼럼이 DATE 였고 : SMDHP.IDX_SM_MCD02 (인덱스칼럼 : ib_ilja , hogi, lot )
: SMDHP.IDX_SM_MCD03 (인덱스칼럼 : hogi, lot ) <---- 요놈이 몇개월전(?)에 추가한 문제의 새로운 인덱스였습니다.
3. 날짜칼럼에 데이타는 '2017-01-01' 타입으로 시간제외하고 들어가 있습니다.
HOGI 조건이 밖에 있던 것, UNION ALL 항목 교차되지 않고 같은위치에 중첩되는것은 쿼리 단순화 하면서 잘못적용한 단순 실수입니다.... (마농님 질문에 핑계.)
4. 현재) 제가 조회를 막 많이 해봐서 통계가 바뀌었는지 모르겠지만... ㅡㅡ
1) WHERE IB_ILJA BETWEEN '20160101' AND '20160930' +++++ > IDX_SM_MCD02 인덱스 탑니다.
2) WHERE IB_ILJA BETWEEN '2016-01-01' AND '2016-09-30' +++++ > IDX_SM_MCD02 인덱스 탑니다.
2) WHERE IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD')) ++++++> IDX_SM_MCD02 인덱스 탑니다.
5. 그러나 WHERE 절에 HOGI = '1' 이라는 조건을 추가하면 .....
1. WHERE IB_ILJA BETWEEN '20160101' AND '20160930' AND HOGI ='1'
-> IDX_SM_MCD02 인덱스 탑니다.
2. WHERE IB_ILJA BETWEEN TO_DATE(:AG_YYMM1||'01','RRRRMMDD') AND LAST_DAY(TO_DATE(:AG_YYMM2||'01','RRRRMMDD')) AND HOGI ='1'
-> IDX_SM_MCD03 인덱스 탑니다.
이런상황이네요~~
여기만 매달릴수 없어서 일단 힌트치고..다른일좀 ^^
모두 감사드려요.
) 버튼을 클릭하여 작성 하시면 됩니다.