#33_140729_TUNING_INDEX SCAN

# INDEX SCAN

점심문제14. 이름, 월급, 부서번호, 자기가 속한 부서번호별 평균월급을 출력하시오(아래의SQL을 각각 출력해서 튜닝전후가 비교가 되도록 레포팅)

분석함수 : listagg, max(sal) over () ….

1.     분석함수 이용

2.     분석함수 이용하지 않은 것

# 튜닝 전 SELECT ename, sal, deptno, AVG(sal) OVER(PARTITION BY deptno) FROM EMP;	db block gets        0 consistent gets       56 physical reads        0
# 튜닝 후 SELECT e.ename, e.sal, e.deptno, f.aver FROM EMP e, (SELECT deptno, AVG(sal) aver FROM EMP GROUP BY deptno) f WHERE e.deptno = f.deptno;	db block gets        0 consistent gets       14 physical reads        0

l 실행 통계에서 블록을 읽는 갯수는 튜닝전의 분석함수를 이용했을 때 많지만 속도는 더 빠르다

• INDEX UNIQUE SCAN

- 인덱스에서 데이터를 읽을 때 하나의 로우만 스캔하는 방법

- 수행 : 사원테이블에 사원번호 primary key 생성. primary key를 생성하게 되면 자동으로unique index가 생성된다

SQL> alter table emp

     add constraint emp_empno_pk primary key(empno);

SQL> select index_name, column_name

     from user_ind_columns

     where table_name='EMP';

# 인덱스 리스트 조회

SQL> select index_name, uniqueness

     from user_indexes

     where table_name='EMP';

# unique 인덱스인지 non unique 인덱스인지 알아낼 수 있음

# unique 인덱스가 생성되려면 데이터의 값이 중복되면 안된다

문제15. 사원번호가 7788번인 사원의 사원번호와 이름과 월급을 출력하고 실행계획을 확인하시오

SELECT empno, ename, sal

FROM EMP

WHERE empno=7788;

l 사원번호에 걸린 인덱스의 출력

l 인덱스에서 data를 읽어오려면 해당 인덱스 컬럼이 where 절에 반드시 존재해야 한다

- SQL> select empno, rowid

from emp

where empno > 0;

l 인덱스 스캔 수행

- SQL> select empno, ename, sal

FROM EMP

WHERE empno=7788;

- Range scan은 여러 개를 읽지만 index unique scan은 하나만 읽어서 찾는다

- 위 쿼리는 7788의 rowid를 읽어서 다른 데이터를 스캔하지 않고 바로 찾을 수 있다

- 그 이유는 primary key가 empno에 걸리면 unique가 걸리기 때문에 해당 7788번 하나만 찾으면 되기 때문이다.

- 그래서 이 수행은 결국 index unique scan을 수행한다

  

문제16. 사원 이름에 인덱스를 생성하고 사원이름이 ADAMS 인 사원의 이름과 월급과 직업을 출력하시오. 그리고 실행계획을 확인해보면 ADAMS는 한건밖에 없지만 index unique scan을 하게 되는지 아니면 index range scan을 하게 되는지 확인해라

CREATE INDEX index_ename ON EMP(ename);

# non unique index가 걸린다

SELECT ename, sal, job

FROM EMP

WHERE ename = 'ADAMS';

- non unique index가 걸리기 때문에 index range scan을 수행한다

- adams를 찾아보고 rowid로 데이터를 찾은 이후에 non unique이기 때문에 allen까지 스캔해보고 같은 이름이 아니라는 것을 판단한다

- 결국 2번 데이터를 읽어서 index range scan이다

- 여기서 rowid는 항상 오름차순 정렬로 나열하는데 이는 만약 range scan을 할 경우에 바로 다음 내용을 찾아봐야 하는데 다음의 값이 같은 값이 아니라면 그 이하는 전부다 다른 값으로 이해할 수 있기 떄문이다.

문제17. 사원번호가 7788 번이고 사원이름이 scott인 사원의 사원번호와 이름과 월급과 직업을 출력하는 쿼리를 작성하고 실행하면 옵티마이져가 empno와 ename에 걸린 인덱스중에서 어떤 인덱스를 선택하는가?

SELECT empno, ename, sal, job

FROM EMP

WHERE empno = 7788;

l 당연히 unique가 걸린 empno로 수행하는 것을 확인할 수 있다

• INDEX RANGE SCAN

- non unique index를 읽어서 스캔하는 방법인데 인덱스에서 하나 이상의 row를 읽은 것이다

문제18. 직업과 이름에 인덱스를 생성하고 사원이름이 scott 이고 직업이 analyst인 사원의 이름과 직업과 월급과 부서번호를 출력하고 실행계획을 확인하면 이름에 걸린 인덱스를 엑세스 하는가? 아니면 직업에 걸린 인덱스를 엑세스 하는가?

CREATE INDEX index_ename ON EMP(ename);

CREATE INDEX index_job ON EMP(job);

SELECT ename, job, empno

FROM EMP

WHERE ename='SCOTT' AND job = 'ANALYST';

l 둘 다 non unique index 이면 둘중에 선택도가 좋은 인덱스를 선택한다.

l 인덱스를 넓게 안읽어도 원하는 데이터를 테이블에서 엑세스 할 수 있는 인덱스를 선택도가 좋다라고 한다.

문제19. 문제 18번의 SQL에서 ename이 아니라 job에 인덱스를 선택하도록 힌트를 주시오

SELECT /*+ index(emp index_job) */ ename, job, empno

FROM EMP

WHERE ename='SCOTT' AND job = 'ANALYST';

문제20. 아래의 인덱스를 생성하고 아래의 SQL의 튜닝 전후를 비교하시오

CREATE INDEX sales500_promo_id ON SALES500(promo_id);

# 튜닝 전 select /*+ index(sales500 sales500_promo_id) */ * from sales500 where rn = 299 and promo_id= 999;	# 수행시간 : 34초 db block gets       0 consistent gets     60119 physical reads      60065
# 튜닝 후 select /*+ index(sales500 sales500_rn) */ * from sales500 where rn = 299 and promo_id= 999;	# 수행시간 : 0.58 db block gets       0 consistent gets     56 physical reads      4

l 수행속도나 읽은 버퍼의 개수에서도 큰차이를 보이는 것을 알 수 있다. 이것은 rn(rownum) 같은 경우에는 값이 중복되지 않는 값이고 promo_id 값은 중복된 값이 대다수이기 때문이다

• INDEX FULL SCAN

• (힌트 : /*+ index_fs(테이블명 인덱스명) */ )

- 인덱스를 full로 스캔해서 결과를 보는 스캔방법

- index full scan을 해서 결과를 보는 것보다 index fast full scan이 더 빠르다

• INDEX FAST FULL SCAN

- 인덱스를 fast full scan으로 스캔해서 결과를 보는 스캔 방법

- index fast full scan이 index full scan 보다 빠른 이유 2가지

1)     multiblock i/o 가능 (db_file_multiblock_read_count에 지정된 숫자 만큼 한번에 I/O로 블록을 읽는다)

2)     병렬작업이 가능하다

문제22. sales500의 전체 건수를 조회하시오

SELECT /*+ index_ffs(sales500 sales_rn) */ COUNT(*) FROM SALES500;

- sales 500에 커서를 놓고 f4 키를 누르면 위 결과를 확인할 수 있다

l 왜 힌트를 줬는데 promo_id 인덱스를 엑세스하는 이유는 rn에 not null 제약이 안걸려있기 때문이다.

l index fast full scan이 되려면 해당 컬럼에 not null 제약이 걸려있어야 한다

- not null 제약을 통해서 빈 데이터가 없다는 것을 알려줘야할 필요가 있다

- 왜냐하면 여러 개의 데이터를 읽어오기 때문에 빈 데이터가 없어야 효율을 높일 수 있기 때문이다

문제23. rn에 not null 제약을 걸어주고 다시 실행계획을 확인하시오

SQL> alter table sales500

modify rn not null;

SQL> alter session set db_file_multiblock_read_count=256;

- db_file_multiblock_read_count는 최대 128까지 이다

SELECT /*+ index_ffs(sales500 sales500_rn) */ COUNT(*) FROM SALES500;

문제24. 위를 병렬로 작업해서 수행속도를 높이시오. 인덱스를 병렬로 스캔해라

SELECT /*+ index_ffs(sales500 sales500_rn)

parallel_index(sales500, sales500_rn,2) */ COUNT(*) FROM SALES500;

- parallel_index(테이블명, 인덱스 이름, 병렬도)

문제25. 아래의 인덱스를 생성하고 직업, 직업별 인원수를 출력하시오. 실행계획을 확인해서index fast full scan이 되게하시오

create index emp_job_empno on emp(job,empno);

SELECT /*+ index_ffs(emp emp_job_empno) */ job, COUNT(empno)

FROM EMP

GROUP BY job;

• INDEX SKIP SCAN

• (힌트 : /*+ index_ss(테이블명 인덱스이름) */ )

- 결합 컬럼인덱스의 첫번째 컬럼이 아닌 두번째 도는 세네번째의 컬럼이 where 절에 있을때에도 인덱스를 엑세스 할 수 있게 하는 스캔 방법

- 결합 컬럼 인덱스의 첫번째 컬럼이 where 절에 존재해야 인덱스를 엑세스 할 수 있다

- 수행

SQL> @demobld.sql SQL> create index emp_deptno_sal on emp(deptno,sal); SQL> select ename, sal, deptno, job       from emp       where sal =3000; - 이대로 수행하면 where 절의 컬럼이 2번째 이므로 full table scan을 하게된다

SQL> select /*+ index_ss(emp emp_deptno_sal) */ ename, sal, deptno, job       from emp       where sal =3000; - 이럴때 index skop scan을 이용한다

- 위 그림에서 현재 deptno와 sal이 걸려있기 때문에 위처럼 index를 생성한다

- 위의 그림에서 화살표는 스캔의 순서를 나타내는데 deptno와 sal은 위처럼 ascending 돼 있다

- 그래서 처음에 10번 부서번호에서 3000 번호를 찾고 다음을 찾아보니 3000을 넘기 때문에skip 하고 20번 부서번호로 넘어가고 또 3000을 찾고 skip하고를 반복하며 찾는다

- 결국 경우에 따라서 테이블 full scan보다 빠른데 index skop scan이 효과를 볼려면 결합 인덱스의 첫번째 컬럼의 종류가 적어야 한다

문제26. 아래의 인덱스를 생성하고 아래의 SQL을 튜닝하시오

CREATE INDEX sales500_channel_profit ON SALES500(channel_id, profit);

  

# 튜닝 전
SELECT /*+ full(sales500) */ * FROM SALES500 WHERE profit=34522;	db block gets        0 consistent gets       526 physical reads        622 - 7초 수행
# 튜닝 후
SELECT /*+ index_ss(sales500 sales500_channel_profit) */ * FROM SALES500 WHERE profit=34522;	db block gets        0 consistent gets       137 physical reads        78 - 0.92초 수행

• BITMAP INDEX 

l bitmap index 사용

l 인덱스의 종류

1.     b-tree index : tree 형식의 구조의 인덱스

2.     bitmap 인덱스

- 중복된 데이터가 많은 컬럼에 걸면 b-tree 인덱스 보다 더 큰 효과를 본다

- cardinality : 중복된 data가 많은 컬럼

- 대표적 예로 성별, 색깔

3.     bitmap index : tree 형식인데 leaf 가 bitmap으로 구성됨

4.     reverse key index : index column 값을 뒤집어서 저장한 인덱스

l b-tree index와 bitmap index의 차이

# b-tree index

# bitmap index

SQL> drop index emp_job; SQL> create bitmap index emp_job on emp(job); SQL> select index_name, index_type      from user_indexes      where table_name='EMP'; INDEX_NAME                     INDEX_TYPE ------------------------------ --------------------------- EMP_JOB                        BITMAP EMP_DEPTNO                     NORMAL - 남자와 여자라는 데이터가 있을 때 남자 혹은 라는 같은 데이터를 각각의 rowid를 가지게 하는 것이 b-tree index이다 - 하지만 b-tree index는 같은 데이터를 각각의 rowid를 가지기 때문에 검색에서 느리기 때문에 그래서 필요한 것이 bitmap index이다 - bit map index는 위 그림에서처럼 남자라는 모든 데이터들을 하나로 묶어 bitmap이라는 주소체계를 만들고 각각의 binary는 남자라는 데이터 하나하나를 가르키고 있다 - 실제로는 bitmap이 가르키는 데이터가 바뀔때마다 락이 걸리기 때문에 활용하기 어렵다

• INDEX JOIN

- 실제로는 customer 테이블과 sales 테이블을 조인시키면 대용량이기 때문에 속도가 굉장히 느려지는데 위처럼 조인으로 index를 시키면 sales 테이블을 조회할 때 조인할 필요도 없고 속도도 빨라진다

- 수행 예제 :

select s.prod_id, s.promo_id, c.cust_city from sales s, customers c where s.cust_id=c.cust_id;

select prod_id, promo_id, cust_city from saels where prod_id=10101; - 본래는 위처럼 일일이 조인문장을 넣어줘야 했으나 비트맵 조인 인덱스를 수행하면 간단하게 쿼리를 짤 수 있다.