문제 참고 한 곳
http://simverse.tistory.com/86
http://cafe.daum.net/oracleoracle
--- 튜닝 전
select b.대리점번호, b.계약상태코드, count(*) as 계약건수 , max(b.계약일자) as 최근계약일
from 대리점 a, 계약 b
where a.대리점번호 = b.대리점번호
and to_char (b.계약일자, 'yyyymm') = :v_date_contract
and b.계약상태코드 in (select 코드 from 코드테이블 where 그룹코드 = 'A01') --- 이 부분이 예측이 어려움
and a.업체 =: v_업체코드
group by b.대리점번호, b.계약상태코드
order by b.대리점번호
;
--- 튜닝 후
1)
select /*+ ordered use_nl(b) index (a 대리점_x01) index(b 계약_x01) */ b.대리점번호, b.계약상태코드, count(*) as 계약건수
, max(b.계약일자) as 최근계약일
from 대리점 a, ( select /*+ ordered use_nl(d) index(d 계약_x02) */ d.대리점번호 , d.계약일자, d.계약상태코드
from 코드테이블 c , 계약 d
where c.코드 = d.코드
and c.그룹코드 = 'A01'
) b
where a.대리점번호 = b.대리점번호
and b.계약일자 = to_date(:v_date_contract,'')
and a.업체 =: v_업체코드
group by b.대리점번호, b.계약상태코드
order by b.대리점번호
;
or
2)
select /*+ ordered use_nl(b) index (a 대리점_x01) index(b 계약_x01) */ b.대리점번호, b.계약상태코드, count(*) as 계약건수
, max(b.계약일자) as 최근계약일
from 대리점 a, ( select /*+ ordered use_nl(d) index(d 계약_x02) */ d.대리점번호 , d.계약일자, d.계약상태코드
from 코드테이블 c , 계약 d
where c.코드 = d.코드
and c.그룹코드 = 'A01'
) b
where a.대리점번호 = b.대리점번호
and to_char (b.계약일자, 'yyyymm') = :v_date_contract
and a.업체 =: v_업체코드
group by b.대리점번호, b.계약상태코드
order by b.대리점번호
;
조건 1 : 600개 업체, 3000개 대래점 (하나의 업체에 4 ~ 6개의 대리점이 있다고 가정) , 계약 (10만건)
조건 2 : A01 에 해당하는 코드는 3개
조건 3 : A01 에 해당하는 코드는 향후 더 추가 될 수 있음
1) 개선 부분
1. 계약일자 컬럼 인덱스 사용 못함
( 여기서는 상황에 따라서 해결 방법이 달라짐
1) 계약일자가 'yyyymm' 형식의 날짜 데이터라면 컬럼이 아닌 비교 값에 to_date를 사용하면 됨
2) 만약 계약일자가 'yyyymm'형식이 아니라면 함수 기반 인덱스 생성 )
2. 적절한 인덱스 생성
3. 조인 순서는 대리점 테이블 부터 하도록 힌트 사용
4. in 의 경우 in-line view를 통해서 조인 순서 조절할 수 있도록 함
대리점_pk = 대리점번호
대리점_x01 = 업체코드 + 대리점번호 --- 인덱스만 읽고 처리 가능
-- <인덱스>
계약_pk = 계약번호
계약_x01 = 대리점번호 + ( 1) 계약일자 or 2) to_char(계약일자,'yyyymm') )+ 계약상태코드
계약_x02 = 계약상태코드
2) 해당 쿼리를 통해 추가로 살펴 본 부분
1. 대리점 번호 인덱스 생성 후 group by를 하면 order by가 필요 없는가
- 위의 쿼리로 테스트 해봤는데 sort order by는 실행계획에 없고
sort group by nosort가 실행계획으로 출력 되었다.
테스트)
create index emp1_empno_ename on emp1(empno,ename) ;
select /*+ index(emp1 emp1_empno_ename) */ empno, ename
from emp1
where empno = 7788
group by empno, ename
order by empno
;
2. 위와 같은 쿼리에서 in-list 에 새로운 테이블을 사용하게 되면 처리가 어떻게 되는가
- unnest , view merging 이 일어날 것이라고 생각했는데
일단 테스트를 통해서 먼제 확인해보았다.
먼저 서브쿼리 테이블을 읽고 서브쿼리와 메인쿼리 테이블과 조인 한 후
메인 쿼리에 다른 테이블과 조인을 하였다.
테스트)
create table emp1
as select *
from emp
;
select a.empno, a.ename, b.dname, b.loc
from emp a, dept b
where a.deptno = b.deptno
and a.empno in ( select empno
from emp1
where deptno = 10 )
;
3. 인덱스 컬럼이 변형되어서 인데스 사용 못한다고 하였는데 복합 인덱스에 두번째 컬럼일 때도 그 영향이 있는가
- 복합 인덱스를 온전히 사용하는가 하지 못하는 가에 대해 이야기 했는데 영향을 주긴 한다.
이를 위해 테스트를 해보았는데 복합 인덱스를 사용 시 두번째 컬럼이 변형되면 제대로 복합 컬럼의 인덱스를 사용하지는 못한다.
제대로 사용은 못한다고 하더라도 복합 컬럼 인덱스를 통해 index를 사용할 수 있었으며
복합 컬럼만 출력 시 테이블 access도 일어나지 않았다.
다만 인덱스 사용시 두번째 조건이 access로 사용 되는가 filter 조건으로 달라졌다.
일반적으로 predicate에서 access만 나왔을 경우와 access와 filter가 함께 나왔을 경우에
access만 나온 경우가 더 효율적이라고 생각한다.
이유는 access는 필요한 block만 읽으면 되지만 access와 filter가 함께 나온 경우는
access를 통해 필요한 block을 읽고난 후 filter를 하는 작업을 또 해야 하는 것이다.
물론 추가적인 작업이 있기 때문에도 효율성을 이야기 하지만
생각해보면 인덱스에 a, b 컬럼이 속해 있는 복합 인덱스의 경우
인덱스를 온전히 사용하여 인덱스에서 a, b 컬럼 둘 다 값을 가지고 있는 block에 access 해서
그 값을 가져 오기만 하면 된다.
그러나 b 컬럼을 사용하지 못한다면 결국
a 컬럼에 해당 하는 블록들을 모두 읽고 난 후 b 컬럼 값의 조건을 filtering 해야 한다.
이는 즉 읽어 들이는 block 수에서도 비효율이 발생할 수 있는 부분이다.
그런데 아래 쿼리로 해당 작업을 테스트 할 때는 두번째 조건이 filter 조건으로 사용 되었을 때 더 성능이 좋게 나왔다는 것이다.
이 부분은 현재 확실히 이해가 되지 않는다.
온전히 인데스를 이용하는 것보다 두번째 컬럼을 이용하지 않을 때 더 좋은 성능이 나왔기 때문이다.
이는 어떻게 설명할 수 있는지 추가적인 좀 더 봐야 할 부분이다.
테스트)
create table emp1
as select *
from emp
;
create index emp1_deptno_hiredate on emp1(deptno,hiredate) ;
select /*+ index(emp1 emp1_deptno_hiredate) */ deptno , hiredate , count(*)
from emp1
where deptno = 10
and to_char(hiredate,'yyyymm') = '198105'
group by deptno, hiredate
order by deptno
;
select /*+ index(emp1 emp1_deptno_hiredate) */ deptno , hiredate , count(*)
from emp1
where deptno = 10
and hiredate = to_date('19820123', 'yyyymmdd')
group by deptno, hiredate
order by deptno
;