7) index merge scan
- 두개의 인덱스를 동시에 사용해서 하나의 인덱스만 사용했을 때보다 더 큰 시너지 효과를 보겠금 하는 스캔 방법
(시너지 효과 : table 엑세스 횟수를 줄이는 효과가 있다.)
(10g 부터는 사용되지 않고 있는 기능이다. )
- 힌트 : and_equal
⑴ index merge test
① @ctas (초기화)
② create index emp_deptno on emp(depno) ;
③ create index emp_job on emp(job) ;
⑵ index merge 실행계획
SELECT /*+ and_equal(emp emp_deptno emp_job) */ ename, job, deptno
FROM EMP
WHERE deptno = 30
AND job = 'SALESMAN';
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 3 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB | 3 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
3 - access("JOB"='SALESMAN')
4 - access("DEPTNO"=30)
* 실행계획을 읽을 때 동일한 위치에 있는 것들은 위에서 부터 읽으면 된다.
8) index bitmap merge scan ( 10g 버젼부터 가능한 스캔 방법 _ 책에는 9c 부터)
- index merge scan 과 스캔방법은 똑같은데 인덱스의 크기를 줄이기 위해서 인덱스를 bitmap 으로 변환하는 작업이 추가되었다.
- hint : index_combine
SELECT /*+ index_combine(emp) */ ename, job, deptno
FROM EMP
WHERE deptno = 30
AND job = 'SALESMAN';
-- 4
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 | -----------------------------------------> 실행순서 8
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 14 | 3 (0)| 00:00:01 | ------------------------------------------> 실행순서 7
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | | ------------------------------------------> 실행순서 6
| 3 | BITMAP AND | | | | | | ------------------------------------------> 실행순서 5
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | ------------------------------------------> 실행순서 2
|* 5 | INDEX RANGE SCAN | EMP_JOB | | | 1 (0)| 00:00:01 | ------------------------------------------> 실행순서 1
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | ------------------------------------------> 실행순서 4
|* 7 | INDEX RANGE SCAN | EMP_DEPTNO | | | 1 (0)| 00:00:01 | ------------------------------------------> 실행순서 3
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("JOB"='SALESMAN')
7 - access("DEPTNO"=30)
※ 선택도 확인 방법
① ANALYZE TABLE EMP COMPUTE STATISTICS ;
② select c.column_name, t.num_rows, c.num_nulls, c.num_distinct
, 1/c.num_distinct selectivity
, num_rows/c.num_distinct cardinality
from user_tables t, user_tab_columns c
where t.table_name = 'EMP'
and c.table_name = t.table_name
and c.column_name in ('DEPTNO','JOB') ;
.png)
- num_distinct 가 클 수록 선택도가 좋아진다. (num_distinct : 해당 컬럼을 구분하는 구분기준의 갯수)
(selectivity, cardinality 의 값이 작을 수록 선택도가 좋아진다.)
- 선택도가 좋아진다는 것은 optimize 가 선택할 가능성이 크다는 것을 의미한다.
9) index join
- 인덱스끼리 조인해서 바로 결과를 보고 테이블 엑세스는 따로 하지 않는 스캔 방식
- 힌트 : index_join
⑴ index join 테스트
① create index emp_deptno on emp(deptno) ;
create index emp_job on emp(job) ;
② select deptno, job
from emp
where deptno = 30 and job='SALESMAN';
-- consistent gets 4
-- full table scan
③ SELECT /*+ index_join(emp emp_deptno emp_job) */ deptno, job
FROM EMP
where deptno = 30 and job='SALESMAN';
-- consistent gets 3
-- 2번의 index range scan
-- hash join
10) index unique scan
- primary key 나 unique 제약을 걸면 unique 인덱스가 자동으로 생성이 되는데 바로 이 unique 인덱스를 이용해서 데이터를 스캔하는 방법
( 생성할 수만 있다면 non unique 인덱스보다 성능이 좋다.)
- 힌트 : index ( 해당 컬럼에 unique 제약이 있으면 자동으로 적용된다.)
⑴ index unique scan 생성 방법
① primary key 생성시 unique 인덱스 자동 생성)
alter table emp
add constraint emp_empno_pk primary key(empno) ;
select index_name, uniqueness
from user_indexes
where table_name ='EMP'
② unique index 직접 생성
- create unique index emp_comm on emp(comm);
⑵ unique index 의 특징
- non unique index는 unique 하다고 선언되어 있지 않아서 필요한 자료를 찾은 후 또 자료 있는지 그 행 다음열 부터 다시 찾는 작업을 한 번더 실행한다.)
- 즉, unique index는 필요한 data를 찾는 1번의 검색만 하는 반면 non unique index는 총 2번의 검색 작업이 이루어진다.)
(4) 테이블 random 엑세스 부하
1) 테이블 랜덤 엑세스 줄이는 방법
① 결합 컬럼 인덱스를 활용한다.
② index의 클러스터링 팩터를 좋게한다. (p. 68)
③ index의 컬럼추가 (p. 79)
④ 테이블의 구조를 IOT (index organizaion table) 로 구성한다. (p. 98)
⑤ 테이블의 구조를 cluster table로 구성한다. (p.118)
2) index 컬럼추가
- 기존에 사용되던 index에서 필요한 컬럼을 추가하여 table access를 줄인다
① create table ts
as select *
from all_objects
order by object_type ;
② create index t2_owner
on t2(owner) ;
③ alter system flush buffer_cache ; ----------------> buffer cache 내용 비우는 작업
※ 차세대 시스템 구축시 주의 사항 !!
<예시>
SQL> insert /*+ parallel(e1,4) */ into emp e1
SQL> select /*+ parallel(e2,4) */ *
from emp e2;
- 데이터 이행시 위와 같이 병렬로 작업해서 이행하면 데이터가 무작위로 흩어져서 저장되기 때문에 클러스터링 팩터가 더 안좋아져서 성능이 느려질 수 있다.
문제 29. 부서번호가 30번이고 직업이 SALESMAN인 사원들의 이름과 직업과 부서번호를 출력하시오!
(실행계획을 보고 인덱스 둘중에 어느 인덱스를 사용했는지 확인하시오!)
SELECT ename, job, deptno
FROM EMP
WHERE deptno = 30
AND job = 'SALESMAN';
- emp(job) 에 걸려 있는 index를 사용
- emp(job) 으로는 4건 , emp(deptno) = 6 건
- 더 적게 table acess 하는 방법으로 optimize 가 선택하였다.
* 튜닝을 할 때는 where 조건에 걸려 있는 각각의 건수들이 몇건인지 확인해보는 것이 기본이다.
* 옵티마이져가 인덱스의 선택도를 조사해서 선택도가 좋은 인덱스를 선택했다.
( emp_job > emp_deptno)
문제 30. 위의 SQL 의 실행계획이 부서번호의 인덱스를 엑세스 하겠금 힌트를 주시오 !
SELECT /*+ index(emp emp_deptno) */ ename, job, deptno
FROM EMP
WHERE deptno = 30
AND job = 'SALESMAN';
문제 31. 아래의 SQL 의 컬럼에 각각 인덱스를 걸고 두개의 실행계획을 비교하는데 하나는 index merge scan 이 되게하고 ,
다른 하나는 index bitmap merge scan 이 되게해서 consistent gets를 비교하시오 !
튜닝 전 :
select *
from sales500
where channel_id = 3
and amount_sold= 1232.16 ;
-- consistent gets 4442
-- (full hint 사용 시)
튜닝 후:
1) 일반적인 인덱스 사용
select /*+ index(sales500 sales500_amount) */ *
from sales500
where channel_id = 3
and amount_sold= 1232.16 ;
-- consistent gets 18
-- index range scan
2) index merge scan 사용
SELECT /*+ and_equal(sales500 sales500_channel sales500_amount) */ *
from sales500
where channel_id = 3
and amount_sold= 1232.16 ;
-- consistent gets 31
-- index range scan 2번
3) index bitmap merge scan
SELECT /*+ index_combine(sales500) */ *
from sales500
where channel_id = 3
and amount_sold= 1232.16 ;
-- consistent gets 412
-- index range scan
-- bitmap conversion
문제 32. 아래의 SQL 을 튜닝하시오 !
튜닝 전:
select *
from t2
where owner='SCOTT' and object_name='EMP';
-- consistent gets 232
튜닝 후:
1) index column 1개만 사용
select /*+ index(t2 t2_owner)*/ *
from t2
where owner='SCOTT' and object_name='EMP';
-- consistent gets 30
-- index range scan
2) index column 을 2개 사용하여 복합 컬럼 인덱스 구성
select /*+ index(t2 t2_owner_name)*/ *
from t2
where owner='SCOTT' and object_name='EMP';
-- consistent gets 4
-- index range scan
문제 33. 짝궁의 SCOTT 의 SALES500 테이블의 SALES500_CUST_ID 인덱스의 클러스터링 팩터를 비교하시오!
SQL> select a.index_name, a.clustering_factor, b.index_name, b.clustering_factor
from user_indexes a, user_indexes@shs_link2 b
WHERE a.table_name = 'SALES500';
* 점심시간 문제 : 아래의 SQL 을 튜닝하시오 !
(deptno , sal 2개의 컬럼으로 구성 된 index가 있을 시 )
create index emp_deptno_sal on emp(deptno,sal);
튜닝 전 :
select deptno, max(sal)
from emp
where deptno = 20
group by deptno ;
-- 46
-- full (full 힌트를 주어서 작동 시켰을 때 값)
튜닝 후 :
1) 일반적인 복합 컬럼 인덱스를 사용하여 튜닝
SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
FROM EMP
WHERE deptno = 20
AND ROWNUM = 1;
-- consistent gets 1
-- index range scan
2) 인라인 뷰 안에서 먼저 복합 컬럼 인덱스를 사용하여 튜닝 후 첫번째 로우만 뽑아 내기
SELECT *
FROM (SELECT /*+ index_desc(emp emp_deptno_sal) */ deptno, sal
FROM EMP
WHERE deptno = 20 )
where rownum = 1 ;
-- consistent gets 1
-- index range scan
3) 인라인 뷰 안에서 스킵 스캔을 사용하여 튜닝 후 첫번째 로우만 뽑아 내기
SELECT *
FROM (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ deptno, sal
FROM EMP
WHERE deptno = 20 )
where rownum = 1 ;
-- consistent gets 1
-- index skip scan
-- index skip scan