3) full table scan
- High water mark 까지 스캔하는 방법
(HWM - 포멧된 디스크와 포멧되지 안은 디스크의 경계선)
- full table scan 방법은 인덱스 스캔은 아니지만 인덱스가 없는 경우 발생하는 기본 스캔임으로 알아둘 필요가 있다.
- full table scan 방법은 인덱스 스캔은 아니지만 인덱스가 없는 경우 발생하는 기본 스캔임으로 알아둘 필요가 있다.
⑴ Full table scan을 할 수 밖에 없는 경우
① 인덱스가 없을 때
② full 힌트를 사용하면
③ 인덱스를 생성할 때
④ 테이블의 통계정보를 수집할 때
⑵ Full table scan을 할 수밖에 없다면 full table scan 속도를 높이는 방법
① full table scan 에 관련한 오라클 파라미터를 조정
- 파라미터 : db_file_multiblock_read_count
( 이 파라미터는 full table scan 을 할 때 한번에 읽어들이는 블럭의 갯수를 결정하는 파라미터 )
alter session set db_file_multiblock_read_count = 128;
② 병렬로 작업한다.
select /*+ full(emp) parallel(emp,4) */ ename, sal, job
from emp
where job = 'SALESMAN';
⑶ full table scan 속도 향상 테스트
① set timing on
② create table sales800
as
select * from sh.sales ;
③ alter session set db_file_multiblock_read_count=4 ;
alter session set workarea_size_policy=manual ;
alter session set sort_area_size=10 ;
④ create index sales_index
on sales800(amount_sold, prod_id) ;
-- 시간 확인
⑤ drop table sales800 purge ;
⑥ 다시 scott 으로 접속해서
⑦ 2번부터 다시 수행하는데 아래와 같이 128로 설정하고 테스트
alter session set db_file_multiblock_read_count=128 ;
alter session set workarea_size_policy=manual ;
alter session set sort_area_size=10000000 ;
4) index range scan
- 인덱스의 일부분만 범위 스캔해서 DATA를 엑세스 스캔 방법
힌트 : /*+ index (emp emp_sal) */
(기본적으로 사용하는 hint 는 index range scan을 이용하라는 의미이다.)
⑴ 실습 환경 구성
drop table sales500 purge
create table sales500
as
select rownum rn , s.*
from sh.sales s;
create index sales500_rn on sales500(rn) ;
create index sales500_promo_id on sales500(promo_id);
튜닝 전 : select /*+ full(sales500) */ *
from sales500
where rn=299;
튜닝 후 : select /*+ index(sales500 sales500_rn) */ *
from sales500
where rn=299;
* 실행 통계 및 자동 추적 사용 시 (SQL gate )
db block gets ┐
consistent gets ┘ ------------> 메모리 영역
physical reads ------------------> 디스크 영역
5) index full scan
⑴ index full scan 의 종류
- index full scan : 인덱스 full 로 스캔
(힌트 : index_fs )
- index fast full scan : 인데스 full 로 스캔하는데 index full scan 보다 더 속도가 빠르다.
(힌트 : index_ffs ) (multiblock i/o를 하기 때문에)
⑵ index full scan 과 index fast full scan 의 차이
index full scan vs index fast full scan
- 인덱스 구조에 따라 스캔 세그먼트 전체를 스캔
- 순서가 보장 (정렬 됨) 순서가 보장이 안된다. (정렬이 보장이 안된다는 것이지 아예 안된다는 것은 아니다.)
- single block i/o multi block i/o
- 병렬 스캔이 블가능하다. 병렬스캔이 가능하다.
* SELECT /*+ index_fs(sales500 sales500_rn) parallel_index(sales500,sales500_rn,4) */ count(rn)
from sales500;
( 위의 SQL 은 index full scan으로 hint 를 사용하여 병렬처리는 실행계획에 포함되지 않는다.)
6) index skip scan 스캔 방법
- 인덱스를 full 또는 fast full 로 전체를 스캔하는 것이 아니라 중간 중간 skip 을 해서 사용
(힌트 : index_ss)
⑴ index skip scan 이란
- 인덱스의 첫번째 컬럼이 where 조건이 없어도 인덱스를 사용할 수 있게 한다.
( 이전에는 결합 컬럼 인덱스의 첫번째 컬럼이 where 조건에 있어야만 결합 컬럼 인덱스를 엑세스 할 수 있다. )
예시 ) index의 컬럼 순서에 따라서 index를 사용하지 못하는 경우
create index emp_deptno_sal
on emp(deptno, sal) ;
select ename, sal
from emp
where sal = 3000; ----------------> 위의 index 사용 못 함
select ename, sal
from emp
where deptno = 10; -----------------> 위의 index를 사용 함
( 실무에서는 인덱스를 임의로 추가하기 힘들다.)
SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal
from emp
where sal = 3000;
⑵ index skip scan 스캔 방법 설명
SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal
from emp
where sal = 2975;
DEPTNO SAL ROWID
10 1300 AAAg7WAAZAAAAfrAAQ
10 2975 AAAg7WAAZAAAAfrAAF
20 800 AAAg7WAAZAAAAfrAAN
20 1100 AAAg7WAAZAAAAfrAAP
20 2975 AAAg7WAAZAAAAfrAAG
20 3000 AAAg7WAAZAAAAfrAAM
20 3000 AAAg7WAAZAAAAfrAAO
30 950 AAAg7WAAZAAAAfrAAK
30 1250 AAAg7WAAZAAAAfrAAH
30 2975 AAAg7WAAZAAAAfrAAL
30 3500 AAAg7WAAZAAAAfrAAJ
30 3600 AAAg7WAAZAAAAfrAAI
30 4050 AAAg7WAAZAAAAfrAAE
- deptno 번호별 해당 sal 의 data 가 있으면 값을 찾고 다름 deptno 그룹으로 넘어간다.
- 해당 경우에서는 deptno의 종류가 적을 수록 효과적이다.
( 앞의 컬럼이 나누어지는 경우의 수가 적을 수록 더 빠르게 값을 찾을 수 있다.)
* 결합 컬럼 인덱스의 첫번째 컬럼이 where 절에서 선분조건 (betwwen .. and like) 으로 사용 된다면
인덱스를 넓게 읽을 수 밖에 없기 때문에 성능이 저하된다. 그럴때는 두가지 방법으로 튜닝한다.
1 index skip scan 사용
2 between .. and 를 in으로 변경
(그림은 index 사용 시에 대한 실행 내용)
문제 17. 아래 SQL 의 성능을 높일 수 있도록 인덱스를 생성하시오 !
튜닝 전 :
select *
select *
from sales500
where cust_id=35834 ;
튜닝 후 :
CREATE INDEX sale500_cust ON SALES500(cust_id);
select /*+ index (sales500 sales500_cust) */ *
from sales500
where cust_id=35834 ;
문제18. EMP테이블에 사원 수가 몇명인지 조회하시오.
튜닝 전 :
select count(*)
from emp ;
튜닝 후 :
select /*+ index (emp emp_sal) */ sal
from emp ;
문제 19. 아래의 SQL 의 성능을 높일 수 있도록 인덱스를 생성하고 힌트를 주시오 !
튜닝전 :
select count(ename)
from emp;
튜닝 후 :
(인덱스 생성 후 create index emp_ename on emp(ename) ; )
SELECT /*+ index(emp emp_ename) */ COUNT (ename)
FROM EMP ;
or
더 성능이 좋게 하려면
SELECT /*+ index(emp emp_ename) */ COUNT (ename)
FROM EMP
WHERE ename > ' ' ;
- 더 성능이 좋을 것이라고 판단하는 것은 위의 SQL 은 index full scan이 실행계획에 포함되지만
- 아래 SQL은 index range scan 이 실행계획에 포함된다.
* ename 컬럼에 not null 제약이 걸려있어야 더 확실히 옵티마니져가 index full scan 이나 index fast full scan 을 할 수 있다.
문제 20. 직업에 인덱스를 걸고 아래의 SQL 의 성능을 높이시오
튜닝 전 :
select count(job) from emp ;
튜닝 후 :
CREATE INDEX EMP_JOB ON EMP(job);
SELECT /*+ index_ffs(emp emp_job)*/COUNT(job)
FROM EMP;
- 이 경우는 어차피 테이블의 모든 row를 보아야 하고 테이블도 작기 때문에 fast full scan을 하는 것이 더 나을 수 있다.
* 오라클 버전에 따라서 not null 조약에 따라서 index full scan이 되지 않을 수도 있다.
문제 21. 아래의 SQL의 성능을 높이시오 !
튜닝 전 :
select count(*)
from sales500;
튜닝 후 :
create index sales500_rn on sales500(rn) ;
select /*+ index_ffs (sales500 sales500_rn) count(rn)
from sales500;
문제 22. demo를 다시 돌리고 월급에 인덱스를 생성한 후에 월급이 0 이상인 사원들의 월급을 출력하는데 index fast full scan 이 되도록 하시오 !
SELECT /*+ index_ffs(emp emp_sal) */ sal
FROM EMP
WHERE sal > 0 ;
* 이 경우 index full scan hint를 주더라도 실행계획은 rang scan으로 짠다.
( 더 효율적인 방식으로 인식하여)
SELECT /*+ index_fs(emp emp_sal) */ sal
FROM EMP
WHERE sal > 0 ;
문제 23. 이름에 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급과 직업과 부서번호를 출력하시오!
SQL> select ename, sal, job, deptno
from emp
where ename like '%EN%'
or ename like '%IN%'
where ename like '%EN%'
or ename like '%IN%'
* regexp_like 를 사용하여도 된다.
SQL> select ename, sal
from emp
where regexp_like(ename,'(EN|IN)');
문제 24. 아래의 환경을 구성하고 아래의 SQL을 튜닝하시오.
create table customers
as select * from sh.customers ;
create index customers_indx1
on customers (cust_street_address) ;
SELECT *
FROM customers
WHERE cust_street_address LIKE '%Ceneva%'
or cust_street_address LIKE '%Mcintosh%';
consistent gets -- 1528
* 각 튜닝 방법 적용 후에 consistent gets의 값
1) where 절에 like 대신 instr 함수를 사용한 경우
SELECT /*+ index(customers customers_indx1) */ *
FROM customers
WHERE INSTR(cust_street_address, 'Ceneva') >0
or INSTR(cust_street_address, 'Mcintosh') >0 ;
consistent gets -- 389
2) where절에 like를 사용한 경우
SELECT /*+ index(customers customers_indx1) */ *
FROM customers
WHERE cust_street_address LIKE '%Ceneva%'
or cust_street_address LIKE '%Mcintosh%';
consistent gets -- 586
3) where 절에 like를 사용하고 index full scan을 시도한 경우
SELECT /*+ index_fs(customers customers_indx1) */ *
FROM customers
WHERE cust_street_address LIKE '%Ceneva%'
or cust_street_address LIKE '%Mcintosh%';
consistent gets -- 1462 ( 실제 실행계획이 full table scan 함 )
4) where 절에 like를 사용하고 index fast full scan을 시도한 경우
SELECT /*+ index_ffs(customers customers_indx1) */ *
FROM customers
WHERE cust_street_address LIKE '%Ceneva%'
or cust_street_address LIKE '%Mcintosh%';
consistent gets -- 1462 (실제 실행계획이 full table scan 함)
5) Rowid를 사용해서 테이블 스캔이 일어나도록 하는 방식
SELECT /*+ no_query_transformation rowid(customers) */ a.*
FROM customers a, (SELECT /*+ index(customers customers_indx1) */ ROWID
FROM customers
WHERE cust_street_address LIKE '%Ceneva%'
or cust_street_address LIKE '%Mcintosh%') b
WHERE A.ROWID = B.ROWID ;
consistent gets -- 1725
* where 절에 like 사용 보다 instr 사용이 더 빨라진다.
문제 25. 부서번호의 종류가 몇가지 인지 출력하시오!
SQL> SELECT COUNT (*)
FROM (SELECT DISTINCT (deptno)
FROM EMP
WHERE deptno IS NOT null) ;
or
SQL> SELECT COUNT( DISTINCT deptno)
FROM EMP;
문제 26. 아래의 SQL을 튜닝하시오!
create index sales500_channel_custid
on sales (channel_id, cust_id)
튜닝 전 :
select /*+ full(sales500) */ *
from sales500
where cust_id = 2869 ;
consistent gets -- 4452
튜닝 후 :
select /*+ index_ss(sales500 sales500_channel_custid) */ *
from sales500
where cust_id = 2869 ;
consistent gets -- 200
* 해당 테이블에 alias를 사용하였다면 index 사용시 table 명에도 사용하는 alias를 사용해야 한다.
(alias를 사용하지 않으면 hint 가 제대로 사용되지 않는다. )
select /*+ index_ss(s sales500_channel_custid) */ *
from sales500 s
where cust_id = 2869 ;
문제 27. 아래의 SQL을 튜닝하시오 !
( 환경 설정 :
CREATE TABLE mcustsum
AS
SELECT rownum custno
, '2008' || lpad(CEIL(rownum/100000), 2, '0') salemm
, decode(MOD(rownum, 12), 1, 'A', 'B') salegb
, round(dbms_random.VALUE(1000,100000), -2) saleamt
FROM dual
CONNECT BY level <= 1200000 ;
CREATE INDEX mcustsum_IDX2 ON mcustsum(salemm, salegb);
EXEC dbms_stats.gather_schema_stats('SCOTT');
튜닝 전 :
select count(*)
from mcustsum t
where salegb = 'A'
and salemm between '200801' and '200812';
-- 3367
-- index fast full scan : 실행계획
-- index range scan : 실제 실행계획
튜닝 후 :
SELECT /*+ index_ss(t mcustsum_IDX2) */ count(*)
from mcustsum t
where salegb = 'A'
and salemm between '200801' and '200812';
-- 300
-- index skip scan
* between .. and는 index range scan 과 index fast full scan 이 가능하다.
* 2가지를 고려해서 테스트 한 후 더 성능이 좋은 것을 사용하면 된다.
문제 28. 위의 SQL을 힌트쓰지 말고 재작성하는데 Between .. and 가 아니라 IN으로 변경해서 실행하시오
SELECT /*+ index(t mcustsum_IDX2) */ count(*)
from mcustsum t
where salegb = 'A'
and salemm IN ( '200801', '200802','200803','200804','200805','200806','200807','200808','200809','200810','200811', '200812' );
-- 304
* in을 사용해서 실행계획에서 index range scan이 사용 된다.
* in 사용 시 index_ss(skip scan) 을 사용해도 skip scan 으로 실행계획이 세워지지 않는다.
* 과거 skip scan 기능이 나오기 전에 사용한 튜닝 방법이다.
* 점심시간 문제 : 아래의 SQL 을 튜닝하시오!
(직업과 월급에 각각 인덱스가 있다.)
튜닝 전 :
select ename,job, sal, deptno
from emp
where job||sal = 'SALESMAN3000' ;
-- 41
-- full table scan
튜닝 후 :
select ename, job, sal ,deptno
from emp
where job = 'SALESMAN'
and sal = 3000;
-- 1
-- index range scan
* 비교 방법
db block gets ┐
consistent gets ┘ ------------> 메모리 영역
physical reads ------------------> 디스크 영역
* 인덱스를 각각 생성하지 않아도 가능하다.
CREATE INDEX emp_job_sal ON EMP(job, sal);
* 오늘의 마지막 문제:
아래 SQL 을 튜닝하시오 !
(그룹 함수 쓰지 말고 sal 인덱스만 이용해서 결과를 출력하시오!)
튜닝 전 :
select max(sal)
from emp ;
튜닝 후 :
SELECT /*+ index_desc(emp emp_sal) */ sal
FROM EMP
WHERE sal > 0
AND ROWNUM = 1 ;
( - 따로 sal 에 index를 걸어주었을 때)
or
SELECT sal
FROM (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ sal
FROM EMP
WHERE sal > 0)
WHERE ROWNUM = 1;
( - 기존에 걸어둔 emp_deptno_sal (deptno, sal) index를 사용하려면 )
* 실행 통계를 보기 전에 analizy를 하는 것이 더 정확하다
* 간혹 SQL gate 버전에 따라서 실행 통계 값이 부정확 할 수 있다.