01. 인덱스 원리와 활용 (01. 인덱스 구조 ~ 05. 테이블 Random 액세스 최소화 튜닝) (by bshman) [2016.05.07]
h1.인덱스 구조
범위스캔
인덱스는 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스할 목적으로 사용하는 오브젝트이다.
인덱스는 키 컬럼 순으로 정렬되어있어, 특정위치에서 스캔을 시작해 검색 조건에 일치하지 않는 값을 만나는 순간 멈출 수 있다.
이것이 범위 스캔이 의미하는 바이다.
인덱스 기본 구조
- 브랜치 노드는 인덱스 분할에 의해 새로운 블록이 추가되거나 삭제될 때만 갱신
- 리프 노드상의 인덱스와 레코드와 테이블 레코드 간에 1:1관계
- 리프 노드상의 키 값과 테이블 레코드 키 값은 서로 일치
- 브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치
- 브랜치 노드상의 키 값은 하위 노드가 같은 값의 범위를 의미
인덱스 탐색
- 수평적 탐색 : 좌,우 스캔 함을 수평적
- 수직적 탐색 : 루트에서 리프블록 까지 아래쪽으로 진행(수평적 탐색을 위한 시작 지점을 찾는 과정)
- 브랜치 블록 스캔 :
1번 루트 블록에서 lmc가 가르키는 2번 브랜치 노드로 따라 내려감. 만약 같은 값인 3번 브랜치로 내려가면 2번 브랜치 끝에 놓인 3을 놓치기 때문
이제 도착한 2번 브랜치에서 맨 뒤쪽 3부터 거꾸로 스캔하다가 2를 만나는 순간 리프 블륵으로 내려감
거기서 키 값이 인 첫 번째 레코드를 찾아 오른쪽으로 리프 블록을 스캔해 나가면서 조회조건을 찾음. - 결합 인덱스 구조와탐색
'deptno = 20 and sal )= 2000' 조건으로 쿼리할 때 deptno가 20인 첫 번째 례코드가 스캔 시작점이라고 생각하기 쉽다
하지만 두 번째 리프 블록， 그 중에서도 두 번째 레코드부터 스캔이 시작된다는 사실을 반드시 기억하기 바란다.
7절에서 좀 더 자세한 원리를 설명하겠지만 이는 수직적 탐색 과정에서 deptno 뿐만 아니라 sal
값에 대한 필터링도 같이 이루어지기 때문이다. 그리고 마지막 블록 첫 번째 레코드에서 스캔을 멈춤.
(4) ROWID 포맷
데이터파일 번호, 블록번호,로우 번호 와 같은 위치정보를 포함한다.
테이블 레코드를 찾아가는데 필요한 주소 정보.
테이블에 실제 저장되어있지는 않음. pseudo 컬럼.
오라클 7버전까지 6byte 크기를 차지,
- 파티션되지 않은 일반 테이블에 생성한 인덱스
- 파티션된 테이블에 생성한 로컬 파티션 인덱스
오라클 8버전이후 10byte 크기를 차지 - 파티션 테이블에 생성한 글로벌 파티션 인덱스
- 파티션 테이블에 생성한 비파티션 인덱스
하지만, 아래와 같은 경우에는 기존처럼 6byte 공간을 차지
제한 rowid 포맷
- 데이터파일 번호(4자리) : 로우가 속한 데이터파일 번호로서, 데이터베이스 내에서 유일한 값
- 블록번호(8자리) : 해당 로우가 저장된 데이터 블록 번호이며,(테이블스페이스가 아니라) 데이터파일 내에서의 상대적 번호
- 로우번호(4자리) : 블록 내에서 각 로우에 붙여진 일련번호로서, 0부터시작
SQL> select rowid, empno, ename from emp;
ROWID EMPNO ENAME
------------------ ---------- ----------
AAAR3sAAEAAAACXAAA 7369 SMITH
AAAR3sAAEAAAACXAAB 7499 ALLEN
AAAR3sAAEAAAACXAAC 7521 WARD
확장 rowid 포맷
- 데이터 오브젝트번호(6자리) : 데이터베이스 세그먼트를 식별하기 위해 사용되는 데이터 오브젝트 번호
- 데이터파일 번호(3자리) : 로우가 속한 데이터파일 번호(테이블스페이스 내에서 상대적인 파일번호)
- 블록 번호(6자리) : 해당 로우가 저장된 데이터 블록 번호이며,(테이블스페이스가 아니라) 데이터파일 내에서의 상대적 번호
- 로우번호(3자리) : 블록 내에서 각 로우에 붙여진 일련번호로서, 0부터시작
SQL> select rowid extended_format
2 , dbms_rowid.rowid_to_restricted(rowid, 0) r_f
3 , dbms_rowid.rowid_object(rowid) object
4 , dbms_rowid.rowid_relative_fno(rowid) file_no
5 , dbms_rowid.rowid_block_number(rowid) block_no
6 , dbms_rowid.rowid_row_number(rowid) row_nillruJer
7 from emp e
8 where empno = 7369;
EXTENDED_FORMAT R_F OBJECT FILE_NO BLOCK_NO ROW_NILLRUJER
------------------ ------------------ ---------- ---------- ---------- -------------
AAAR3sAAEAAAACXAAA 00000097.0000.0004 73196 4 151 0
| 출력된 rowid 포맷이 어느 타입에 속하는지 확인 |
|---|
| {code:sql} SQL> select dbms_rowid.rowid_type( 'AAAR3sAAEAAAACXAAA' ) extended_format 2 , dbms_rowid.rowid_type ('00000097.0000.0004') restricted_format 3 from dual; |
EXTENDED_FORMAT RESTRICTED_FORMAT
---
-
1 0
||데이터파일번호를 이용해서 오브젝트와 데이터파일에 대한 정보를 조회||
|{code:sql}
select object_id,
owner,
object_name,
subobject_name
from dba_objects
where data_object_id ='73196'
OBJECT_ID OWNER OBJECT_NAME SUBOBJECT_NAME
---------- ------------------------------ ---------------- ------------------------------
73196 SCOTT EMP
select file_id, file_name, tablespace_name
from dba_data_files
where relative_fno =4;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ------------------------------------------ --------------------
4 C:\APP\BSHMAN\ORADATA\ORCL\USERS01.DBF USERS
h1.인덱스 기본 원리
인덱스 선수 컬럼이 조건절에 사용되지 않으면 범위 스캔을 위한 시작점을 찾을 수 없어서 옵티마이저는
인덱스 전체를 스캔하거나 테이블 전체를 스캔하는 방식을 취한다.
(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME
from ALL_IND_COLUMNS
where table_name ='EMP'
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------
SCOTT PK_EMP SCOTT EMP EMPNO
case1)
select *
from emp
where substr(empno,1,2) = 7369
case2)
select *
from emp
where empno<> 7369
case3)
select *
from emp
where empno is not null
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 0 |00:00:00.01 | 7 |
------------------------------------------------------------------------------------
| is null 조건을 사용하더라도 다른컬럼의 조건식이 하나라도 있으면 아래와 range scan가능 |
|---|
| {code:sql} |
create index emp_idx on emp(job,deptno);
select *
from emp
where job is null
and deptno =20;
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-
| 0 | SELECT STATEMENT | 1 | 0 | 00:00:00.01 | 1 | ||
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 0 | 00:00:00.01 | 1 |
| INDEX RANGE SCAN | EMP_IDX | 1 | 1 | 0 | 00:00:00.01 | 1 |
-
Predicate Information (identified by operation id):
---
2 - access("JOB" IS NULL AND "DEPTNO"=20)
filter("DEPTNO"=20)
참고로, 오라클은 null값을 맨뒤에 저장한다.
(2) 인덱스 컬럼의 가공
!인덱스컬럼의가공.JPG!
{code:sql}
case1)
create index emp_idx2 on emp(comm);
select * from emp
where nvl(COMM,0) < 10
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 11 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("COMM",0)<10)
case2)
select * from emp
where COMM > 10
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COMM">10)
case3)
select * from emp
where COMM < 10
or comm is null
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 11 | 11 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("COMM" IS NULL OR "COMM"<10))
case4) fbi 함수 생성
create index emp_fbi on emp(nvl(comm,0));
select * from emp
where nvl(COMM,0) < 10
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 11 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_FBI | 1 | 1 | 11 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP"."SYS_NC00009$"<10)
| 함수기반 인덱스(FBI) 활용 |
|---|
| {code:sql} alter table emp add v_deptno varchar2(2); |
update emp set v_deptno = deptno;
Process Time: 0.0194 sec
14 row(s) affected;
create index emp_x01 on emp(v_deptno);
select * from emp where v_deptno =20;
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | 1 | 5 | 00:00:00.01 | 8 | ||
| TABLE ACCESS FULL | EMP | 1 | 1 | 5 | 00:00:00.01 | 8 |
Predicate Information (identified by operation id):
---
1 - filter(TO_NUMBER("V_DEPTNO")=20)
create index emp_x02 on emp(TO_NUMBER("V_DEPTNO"));
select * from emp where v_deptno =20;
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-
| 0 | SELECT STATEMENT | 1 | 5 | 00:00:00.01 | 4 | ||
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 5 | 00:00:00.01 | 4 |
| INDEX RANGE SCAN | EMP_X02 | 1 | 1 | 5 | 00:00:00.01 | 2 |
-
Predicate Information (identified by operation id):
---
2 - access("EMP"."SYS_NC00011$"=20)
-- 부록(null 허용케이스)
create index emp_fbi01 on emp(mgr,'');
select *
from emp
where mgr is null;
---
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---
| 0 | SELECT STATEMENT | 1 | 0 | 00:00:00.01 | 1 | ||
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | 0 | 00:00:00.01 | 1 |
| INDEX RANGE SCAN | EMP_FBI01 | 1 | 1 | 0 | 00:00:00.01 | 1 |
---
Predicate Information (identified by operation id):
---
2 - access("MGR" IS NULL)
h1.다양한 인덱스 스캔 방식
||Index Range Scan||
|인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
실행계획 상에서 Index Range Scan 이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아니다.
인덱스를 스캔하는 범위를 얼마만큼 줄일 수 있느냐, 테이블로 액세스 하는 횟수를 얼마만큼 줄일 수 있느냐가 관건 |
|| Index Full Scan||
|{code:sql}
수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로,
대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택
Index Full Scan의 효용성 : 테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분 레코드를 필터링하고,
일부에 대해서만 테이블 액세스가 발생하도록 할 수 있다면 전체적인 I/O 효율 측면에서 이 방식이 유리
인덱스를 이용한 소트 연산 대체 : 옵티마이저는 소트 연산을 생략함으로써 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 Index Full Scan 방식을 선택
select /*+ first_rows * / * from emp
where sal > 1000
order by ename ;
create index emp_idx03 on emp(ename,sal);
select /*+ first_rows */ * from emp
where sal > 1000
order by ename ;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 13 | 12 |00:00:00.01 | 4 |
|* 2 | INDEX FULL SCAN | EMP_IDX03 | 1 | 13 | 12 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL">1000)
filter("SAL">1000)
first_row 힌트가 의미가없슴.
|
| Index Unique Scan |
|---|
| 수직적 탐색만으로 데이터를 찾는 스캔 방식 '=' 조건일때만 동작 |
| Index Skip Scan |
| {code:sql}인덱스 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 새로운 스캔방식을 9i에 버전에서 선보임. 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을때 유용 루트 또는 브랜치 블록에서 읽은 컬럼 값의 정보의 부합하는 레코드의 포함할 "가능성이 있는" 리프 블록만 골라서 액세스 하는 방식 |
버퍼 Pinning을 이용한 Skip 원리 : 브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가
다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복하는 것
Index Skip Scan이 작동하기 위한 조건 : 인덱스 맨 선두 컬럼이 누락됐을 때만 작동하는 것은 아니다.
업종별거래_PK: 업종유형코드 + 업종코드 + 기준일자
-중간 컬럼이 누락된 경우에도 사용
where 업종유형코드 = '01'
and 기준일자 between '20080101' and '20090101'
-두 개의 선두 컬럼이 모두 누락된 경우에도 사용
where 기준일자 between '20080101' and 20090101'
-선두 컬럼이 부등호, between, like 같은 범위 검색 조건일 때도 Index Skip Scan 사용이 유용
where 기준일자 between '20080501' and '20080531'
and 업종유형코드 = '01'
In-List Iterator 와의 비교
where 연봉 between 2000 and 4000
and 성별 in ('남', '여')
이렇게 쿼리 작성자가 직접 성별에 대한 조건식을 추가해주면 Index Skip Scan에 의존하지 않고도 빠르게 결과집합을 얻을 수 있다.
단, 이처럼 In-List 를 명시하려면 성별 값의 종류가 더 이상 늘지 않음이 보장 되어야 하고,
이 튜닝 기법이 효과를 발휘하려면 In-List 로 제공하는 값의 종류가 적어야 한다.
|
||Index Fast Full Scan||
|
인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문에 Index Full Scan 방식보다 빠르다.
물리적으로 디스크에 저장된 순서대로 읽어들인다.
-Index Fast Full Scan 의 특징
디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과를 발휘
대신 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
Index Fast Scan과 Index Fast Full Scan의 특징
!인덱스패스트풀스캔.JPG!
|{code:sql}
-Index Fast Full Scan 을 활용한 튜닝 사례
select * from 공급업체
where 업체명 like '%네트웍스%'
select /*+ ordered use_nl(b) no_merge(b) rowid(b) */ b.*
from ( select /*+ index_ffs(공급업체 공급업체X01) */ rowid rid
from 공급업체
where instr(업체명, '네트웍스') > 0) a, 공급업체 b
where b.rowid = a.rid
업체명 컬럼에 생성한 공급업체_x01 인덱스를 Fast Full Scan 해서 검색조건에 해당하는 공급업체만을 빠르게 찾아내도록 하였다.
그러고 나서 인덱스로부터 얻은 rowid 를 이용해 테이블을 다시 액세스하는 방식
그리고 like 연산보다 빠른 instr 함수 사용
일반적으로 인덱스 컬럼을 가공해선 안되지만 like 중간 값 검색이면 어차피 Index Range Scan 은 불가능하므로
instr 함수를 사용해 좌변 컬럼을 가공하더라도 나쁠 것 없다.
|
| Index Range Scan Descending | |
|---|---|
| Index Range Scan과 기본적으로 동일한 스캔방식 인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다. | And-Equal, Index Combine, Index Join |
| 두 개 이상 인덱스를 함께 사용하는 방법 | |
| {code} -And-Equal And-Equal 은 10g 부터 아예 폐기 select /*+ and_equal(e emp_deptno_idx emp_job_idx) * / * 단일 컬럼의 Non-Unique 인덱스여야 함과 동시에 인덱스 컬럼에 대한 조건절이 '=' 이어야 함 |
-Index Combine
select /*+ index_combine(e emp_deptno_idx emp_job_idx) * / *
데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random 액세스 량을 줄이는 데 목적이 있다.
조건절이 '=' 이어야 할 필요가 없고, Non-Unique 인덱스도 필요없다.
비트맵 인덱스를 이용하므로 조건절이 OR 로 결합된 경우에도 유용하다.
-Index Join
한 테이블에 속한 여러 인덱스를 이요해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식
|
h1.테이블 Random 액세스 부하
||(1) 인덱스 ROWID에 의한 테이블 액세스||
|
인덱스에 저장되어있는 rowid '물리적 주소정보' 라고 한다.
오브젝트번호, 데이터파일번호, 블록 번호와 같은 물리적 요소들로 구성
하지만, 보는 시각에 따라서 '논리적 주소정보' 라고 표현
이유는 인덱스에서 테이블 레코드로 직접 연결되는 구조가 아니기 때문.
- 메인메모리 DB와 비교
메인메모리 DB에서 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 실시간으로 만듬
인덱스는 메모리상의 주소정보 포인터를 담는다.
그래서 오라클과 비교할 수 없을 정도로 비용이 낮음.
한마디로 빠르다라는 의미.
오라클은 테이블블록이 수시로 버퍼 캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱 되기 때문에 직접 포인터로 연결할 수 없는 구조
대신 디스크 상의 블록위치의 해시 키값으로 삼아 해싱 알고리즘을 통해 버퍼 블록을 찾음.
캐싱되는 해쉬버킷만큼은 고정적.
- rowid는 우편주소에 해당
오라클 rowids : 우편주소
메인메모리DB 포인터 : 전화번호
우편주소 : 서울시 영등포구 양평동 123번지 oo타워 10층
rowid : 7번 데이터 파일에 위치한 123번 블록에 저장된 10번째 레코드
전화번호는 입력하면 바로 대상과 연결되는 구조.
- 인덱스 rowid에 의한 테이블 액세스 구조
인덱스 rowid 는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통한 테이블 액세스는 생각보다 고비용 구조다.
설령 모든 데이터가 메모리에 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 를 해싱하고 래치 획득 과정을 반복해야 하기 때문이며,
동시 액세스가 심할 때는 래치와 버퍼 Lock 에 대한 경합까지 발생한다.
|
||(2) 인덱스 클러스터링 팩터||
|{code:sql}
- 군집성 계수(= 데이터가 모여 있는 정도)
클러스터링 팩트는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
- 클러스터링 팩터 조회
create table t as
select * from all_objects
order by object_id;
Process Time: 3.6719 sec
74598 row(s) affected;
create index t_object_id_idx on t(object_id);
create index t_object_name_idx on t(object_name);
exec dbms_stats.gather_table_stats(user,'T');
select i.index_name ,t.blocks table_blocks, i.num_rows ,i.clustering_factor
from user_tables t, user_indexes i
where t.table_name ='T'
and i.table_name =t.table_name;
INDEX_NAME TABLE_BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------ ---------- -----------------
T_OBJECT_ID_IDX 1085 74598 1059
T_OBJECT_NAME_IDX 1085 74598 39356
CLUSTERING_FACTOR 수치가 테이블블록 에 가까울수록 정렬이 잘되어있음을 의미.
NUM_ROWS 에 가까울수록 흩어져 있음을 의미한다.
- 클러스터링 팩터와 물리적 I/O
인덱스 CF 가 좋다 고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 말한다.
- 클러스터링 팩터와 논리적 I/O
인덱스 CF 는 단적으로 말해, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미한다.
select /*+ index(t t_object_id_idx) */
count(*)
from t
where object_name >= ' '
and object_id >= 0
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.03 0 1225 0 1
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 4 0.03 0.03 0 1225 0 1
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 84 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1225 pr=0 pw=0 time=0 us)
74598 TABLE ACCESS BY INDEX ROWID T (cr=1225 pr=0 pw=0 time=39153 us cost=1227 size=2237940 card=74598)
74598 INDEX RANGE SCAN T_OBJECT_ID_IDX (cr=166 pr=0 pw=0 time=11131 us cost=167 size=0 card=74598)(object id 84677)
<1225 - 166 = 1059 = 으로 앞서보았던 CLUSTERING_FACTOR와 동일>
select /*+ index(t t_object_name_idx) */
count(*)
from t
where object_name >= ' '
and object_id >= 0
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.05 0 39723 0 1
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 4 0.06 0.05 0 39723 0 1
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 84 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=39723 pr=0 pw=0 time=0 us)
74598 TABLE ACCESS BY INDEX ROWID T (cr=39723 pr=0 pw=0 time=65000 us cost=39739 size=2237940 card=74598)
74598 INDEX RANGE SCAN T_OBJECT_NAME_IDX (cr=367 pr=0 pw=0 time=14074 us cost=368 size=0 card=74598)(object id 84678)
<39723 - 367 = 39356 = 으로 앞서보았던 CLUSTERING_FACTOR와 동일 >
- 버퍼 Pinning 에 의한 논리적 I/O 감소 원리
연속된 인덱스 레코드가 같은 블록을 가리킨다면, 래치 획득 과정을 생략하고 버퍼를 Pin 한 상태에서 읽기 때문에 논리적인 블록읽기 횟수가 증가하지 않는다.
|
| (3) 인덱스 손익분기점 |
|---|
| {code:sql} Index Range Scan 에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 흔히 '손익 분기점' 이라고 부른다. - 핵심적인 요인 > 인덱스 rowid 에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan 은 Sequential 액세스 방식으로 이루어진다. > 디스크 I/O 시, 인덱스 rowid 에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan 은 Multiblock Read 방식을 사용한다. |
create table good_cl_factor
pctfree 0
as
select t.* , lpad('x',630) x
from t
order by object_id;
Process Time: 1.0481 sec
74598 row(s) affected;
create table bad_cl_factor
pctfree 0
as
select t.* , lpad('x',630) x
from t
order by dbms_random.value;
Process Time: 1.0574 sec
74598 row(s) affected;
exec dbms_stats.gather_table_stats(user,'GOOD_CL_FACTOR');
exec dbms_stats.gather_table_stats(user,'BAD_CL_FACTOR');
select table_name, num_rows, blocks
, avg_row_len, num_rows/blocks rows_per_blocks
from user_tables
where table_name in('GOOD_CL_FACTOR','BAD_CL_FACTOR');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN ROWS_PER_BLOCKS
--
--
--
---
---
BAD_CL_FACTOR 74598 7574 728 9.84922101927647
GOOD_CL_FACTOR 74598 7430 728 10.0401076716016
select CLUSTERING_FACTOR, NUM_ROWS
from user_INDEXES
where table_name in('GOOD_CL_FACTOR','BAD_CL_FACTOR');
CLUSTERING_FACTOR NUM_ROWS
-
--
74587 74598
7304 74598
alter system flush buffer_cache;
create unique index GOOD_CL_FACTOR_idx on GOOD_CL_FACTOR(object_id);
create unique index BAD_CL_FACTOR_idx on BAD_CL_FACTOR(object_id);
select /* + index(t) */ count(object_name)
from GOOD_CL_FACTOR t
where object_id between 1 and 1000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 3 0 1
---
--
--
--
---
---
--
--
Total 4 0.00 0.00 3 3 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
888 INDEX RANGE SCAN GOOD_CL_FACTOR_IDX (cr=3 pr=3 pw=0 time=253 us cost=3 size=4400 card=880)(object id 84683)
select /* + index(t) */ count(object_name)
from BAD_CL_FACTOR t
where object_id between 1 and 1000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 3 3 0 1
---
--
--
--
---
---
--
--
Total 4 0.00 0.00 3 3 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=3 pr=3 pw=0 time=0 us)
888 INDEX RANGE SCAN BAD_CL_FACTOR_IDX (cr=3 pr=3 pw=0 time=126 us cost=3 size=4400 card=880)(object id 84684)
alter system flush buffer_cache;
select /* + index(t) */ count(object_name)
from GOOD_CL_FACTOR t
where object_id between 1 and 30000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 154 163 0 1
---
--
--
--
---
---
--
--
Total 4 0.03 0.02 154 163 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=163 pr=154 pw=0 time=0 us)
29276 INDEX FAST FULL SCAN GOOD_CL_FACTOR_IDX (cr=163 pr=154 pw=0 time=7286 us cost=45 size=132145 card=26429)(object id 84683)
select /* + index(t) */ count(object_name)
from BAD_CL_FACTOR t
where object_id between 1 and 30000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 154 163 0 1
---
--
--
--
---
---
--
--
Total 4 0.03 0.02 154 163 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=163 pr=154 pw=0 time=0 us)
29276 INDEX FAST FULL SCAN BAD_CL_FACTOR_IDX (cr=163 pr=154 pw=0 time=6775 us cost=45 size=132145 card=26429)(object id 84684)
alter system flush buffer_cache;
select /* + index(t) */ count(object_name)
from GOOD_CL_FACTOR t
where object_id between 1 and 15000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 30 0 1
---
--
--
--
---
---
--
--
Total 4 0.00 0.00 0 30 0 1
Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 84 (SCOTT)
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=30 pr=0 pw=0 time=0 us)
14276 INDEX RANGE SCAN GOOD_CL_FACTOR_IDX (cr=30 pr=0 pw=0 time=1402 us cost=29 size=66070 card=13214)(object id 84683)
select /* + index(t) */ count(object_name)
from BAD_CL_FACTOR t
where object_id between 1 and 15000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 30 30 0 1
---
--
--
--
---
---
--
--
Total 4 0.00 0.01 30 30 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=30 pr=30 pw=0 time=0 us)
14276 INDEX RANGE SCAN BAD_CL_FACTOR_IDX (cr=30 pr=30 pw=0 time=3186 us cost=29 size=66070 card=13214)(object id 84684)
select /*+ full(t) */ count(object_name)
from BAD_CL_FACTOR t
where object_id between 1 and 30000;
call count cpu elapsed disk query current rows
---
--
--
--
---
---
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.20 7447 7450 0 1
---
--
--
--
---
---
--
--
Total 4 0.06 0.20 7447 7450 0 1
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=7450 pr=7447 pw=0 time=0 us)
29276 TABLE ACCESS FULL BAD_CL_FACTOR (cr=7450 pr=7447 pw=0 time=89359 us cost=2057 size=132145 card=26429)
- 손익분기점을 극복하기 위한 기능들
1. IOT (Index-Organized Table) 로서, 테이블을 인덱스 구조로 생성하는 것
2. 클러스터 테이블 (Clustered Table)
3. 파티셔닝
|
h1.테이블 Random 액세스 최소하 튜닝
||(1) 인덱스 컬럼 추가||
|{code:sql}
create index emp_x01 on emp(deptno,job);
select /* index(emp emp_x01) */
*
from emp
where deptno =30
and sal >= 2000 ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 2 | 1 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_X01 | 1 | 6 | 6 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------
조건을 만족하는 사원이 단 한 명인데 , 이를 찾기 위해서 테이블 액세스는 6번발생
sal 컬럼을 인덱스로 추가하여서 random 액세스 횟수를 줄임
drop index emp_x01;
create index emp_x01 on emp(deptno,job,sal);
select /* index(emp emp_x01) */
*
from emp
where deptno =30
and sal >= 2000 ;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 2 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_X01 | 1 | 2 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------
|
| (2) PK 인덱스에 컬럼 추가 |
|---|
| {code:sql} dept_pk 인덱스에 loc컬럼을 추가하면 불필요한 11번의 random 액세스를 줄일수 있지만 컬럼을 추가할수 없음. |
select /* index(emp emp_x01) */
*
from emp
where deptno =30
and sal >= 2000 ;
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-
| 0 | SELECT STATEMENT | 1 | 1 | 00:00:00.01 | 3 | ||
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 2 | 1 | 00:00:00.01 | 3 |
| INDEX RANGE SCAN | EMP_X01 | 1 | 2 | 1 | 00:00:00.01 | 2 |
-
select /*+ ordered use_nl(d) */
*
from emp e, dept d
where d.deptno = e.DEPTNO
and d.LOC = 'NEW YORK'
Rows Row Source Operation
---
---
3 NESTED LOOPS (cr=27 pr=0 pw=0 time=0 us)
14 NESTED LOOPS (cr=13 pr=0 pw=0 time=52 us cost=17 size=310 card=5)
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=26 us cost=3 size=588 card=14)
14 INDEX UNIQUE SCAN PK_DEPT (cr=5 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73195)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=0 us cost=1 size=20 card=1)
alter table dept drop constraint PK_DEPT cascade;
create index dept_x01 on dept(deptno, loc) ;
alter table dept add constraint dept_pk primary key (deptno) using index dept_x01 ;
조인에 성공하고서 필터 조건 체크까지 완료된 3건만 dept테이블을 액세스함.
select /*+ ordered use_nl(d) */
*
from emp e, dept d
where d.deptno = e.DEPTNO
and d.LOC = 'NEW YORK';
Rows Row Source Operation
---
---
3 NESTED LOOPS (cr=15 pr=0 pw=0 time=0 us)
3 NESTED LOOPS (cr=13 pr=0 pw=0 time=34 us cost=17 size=310 card=5)
14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=26 us cost=3 size=588 card=14)
3 INDEX RANGE SCAN DEPT_X01 (cr=5 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 84701)
3 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=0 us cost=1 size=20 card=1)
|
||(3) 컬럼 추가에 따른 클러스터링 팩터 변화||
|{code:sql}
변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의를 기울여야 한다.
drop table t;
create table t as
select * from all_objects
order by object_type;
Process Time: 7.0506 sec
74611 row(s) affected;
create index t_idx on t(object_type);
exec dbms_stats.gather_table_stats(user,'T');
select CLUSTERING_FACTOR, NUM_ROWS
from user_INDEXES
where table_name in('T');
CLUSTERING_FACTOR NUM_ROWS
----------------- ----------
1059 74611
--실제 블록 I/O가 얼마나 발생하는지 확인(table access 하지않아서 block I/O 발생 x)
select /*+ index(t t_idx) */
count(object_name), count(owner)
from t
where object_type > ' '
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.03 204 205 0 1
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 4 0.00 0.03 204 205 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=205 pr=204 pw=0 time=0 us)
74611 INDEX RANGE SCAN T_IDX (cr=205 pr=204 pw=0 time=67827 us cost=206 size=671499 card=74611)(object id 84705)
-- t_indx에 object_name 컬럼 추가
drop index t_idx;
create index t_idx on t(object_type, object_name);
-- 클러스터링 팩터가 나빠진걸확인
select CLUSTERING_FACTOR, NUM_ROWS
from user_INDEXES
where table_name in('T');
CLUSTERING_FACTOR NUM_ROWS
----------------- ----------
51742 74611
select /*+ index(t t_idx) */
count(object_name), count(owner)
from t
where object_type > ' '
call count cpu elapsed disk query current rows
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.05 456 457 0 1
------- ---------- ---------- ---------- ----------- ----------- ---------- ----------
Total 4 0.03 0.05 456 457 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=457 pr=456 pw=0 time=0 us)
74611 INDEX RANGE SCAN T_IDX (cr=457 pr=456 pw=0 time=39032 us cost=458 size=671499 card=74611)(object id 84704)
|
| (4) 인덱스만 읽고 처리 |
|---|
| 테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함 시키는 방법을 고려해 볼 수 있다. MS-SQL 용어 로 Covered 인덱스라 한다. Covered 쿼리: 인덱스만 읽고 처리하는 쿼리 |
| (5) 버퍼 Pinning 효과 활용 |
| {code:sql} 오라클은 한번입력된 테이블 레코드는 절대로 rowid가 바뀌지않는다. 따라서 rowid값을 이용해서 레코드를 조회하는 것이 가능하다. 실행계획상에서 'Table Acces By Index ROWID' 대신 'Table Access By User ROWID'라고 표시 select /*+ ordered use_nl(b) rowid(b) / b. from (select /*+ index(emp emp_pk) no_merge */ rowid rid from emp order by rowid) a, emp b where b.rowid= a.rid |
Rows Row Source Operation
---
---
14 NESTED LOOPS (cr=3 pr=1 pw=0 time=0 us cost=16 size=812 card=14)
14 VIEW (cr=1 pr=1 pw=0 time=26 us cost=2 size=168 card=14)
14 SORT ORDER BY (cr=1 pr=1 pw=0 time=13 us cost=2 size=168 card=14)
14 INDEX FULL SCAN PK_EMP (cr=1 pr=1 pw=0 time=0 us cost=1 size=168 card=14)(object id 73197)
14 TABLE ACCESS BY USER ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=46 card=1)
– User Rowid에 의한 테이블 액세스시에도 버퍼 Pinning 효과가 나타난다면?
create table t_emp as
select * from emp ,
(select rownum no from dual connect by level <= 1000)
order by dbms_random.value;
Process Time: 0.0643 sec
14000 row(s) affected;
alter table t_emp add constraint t_emp_pk primary key(no,empno);
select /*+ ordered use_nl(b) rowid(b) / b.
from (select /*+ index(t_emp t_emp_pk) no_merge */ rowid rid
from t_emp
order by rowid) a, t_emp b
where b.rowid= a.rid
– cr블록확인
Rows Row Source Operation
---
---
14000 NESTED LOOPS (cr=689 pr=35 pw=0 time=25557 us cost=13892 size=1758061 card=13843)
14000 VIEW (cr=36 pr=35 pw=0 time=10435 us cost=44 size=166116 card=13843)
14000 SORT ORDER BY (cr=36 pr=35 pw=0 time=8144 us cost=44 size=166116 card=13843)
14000 INDEX FULL SCAN T_EMP_PK (cr=36 pr=35 pw=0 time=1272 us cost=42 size=166116 card=13843)(object id 84708)
14000 TABLE ACCESS BY USER ROWID T_EMP (cr=653 pr=0 pw=0 time=0 us cost=1 size=115 card=1)
|
||(6) 수동으로 클러스터링 팩터 높이기||
|
테이블에 데이터가 무작위로 입력되는 반면, 인덱스는 정해진 키 순으로 정렬되어 있기 떄문에
대게 클러스터링팩터가 좋지 않다.
그럴 때, 해당 인덱스 기준으로 테이블을 재생성함으로 클러스터링 팩터를 인위적으로 좋게 만드는 방법이다.
주의점은, 인덱스가 여러개 있는 상황에서 특정 인덱스기준으로 테이블을 재정렬 하면 다른 인덱스의
클러스터링 팩터가 나빠질 수 있다.
상관관계가 높은 인덱스 기준으로 진행 하는것이 바람직하다.
-차세대 시세틈 구축 시 주의 사항
데이터 이관 시 병렬 처리로 인한 데이터 정렬이 무작위로 흩어져서 클러스터링 팩터가 나빠지는 케이스가 많다.
데이터 이관 시 ASIS 대비 TOBE 시스템에 클러스터링 팩터가 나빠지지 않았는지 조사하고 결과에 따라서 조치를 취해야 한다.
|