어떤 하나의 스캔이 가장 최적이라 말할 수 없다.
우리의 실생활에는 다양한 운송수단이 있다.
이는 데이터를 액세스 하는 방법에서도 마찬가지 이다.
이제 그 스캔의 유형의 내용을 살펴봅시다.
전체테이블 스캔(Full Tables Scans) 테이블에 있는 모든 로우들을 읽어내는 방법.
다중 블록단위로 메모리에 옮겨지며, 이 블록들은 순차적으로 읽혀진다.
일반적으로 블록들은 서로 인접되어 있기 때문에 한번의 I/O 에서 처리되며 , 이것은 로우당 소요되는 운반단가를 저렴학게 만든다.
한번의 액세스 하는 블록의 양을 정의하려면 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터에서 지정한다.
그럼 이제 옵티마이져가 Full Tables Scan 을 선택하게 되는 경우를 살펴 봅시다.
※ Full Table Scan 을 했을때의 실행계획 ※
{*}Full Tables Scan의 가장 단순한 형태{*}
SELECT *
FROM TF002NGT
WHERE ITEM_NAME LIKE '%BF%'
| call | count | cpu | elapsed | disk | query | current | rows |
|---|---|---|---|---|---|---|---|
| Parse | 1 | 0.01 | 0.01 | 0 | 0 | 0 | 0 |
| Execute | 1 | 0.00 | 0.00 | 0 | 0 | 0 | 0 |
| Fetch | 1 | 1.47 | 2.25 | 12740 | 12754 | 3 | 9 |
| total | 3 | 1.48 | 2.53 | 12740 | 12754 | 3 | 9 |
Rows Rows Source Operation
하단의 51005는 테이블을 액세스한 로우수(전체 로우수) 이고 ,
WHERE 절을 체크를 통해 선별된 최종 결과의 로우수는 '9' 이다.
{*}정렬처리를 했을때 나타나는 실행계획{*}
Rows Rows Source Operation
51005건을 Full Tables Scan 으로 액세스 하여 체크조건을 적용 하였더니 3548 건이 추출되었으며
이를 정렬한 후에 최종결과를 리턴하겠다는 것.
{*}데이터를 Insert, Update , Delete를 하는 경우의 실행계획{*}
| call | count | cpu | elapsed | disk | query | current | rows |
|---|---|---|---|---|---|---|---|
| Parse | 1 | 0.03 | 0.04 | 0 | 0 | 0 | 0 |
| Execute | 1 | 50.73 | 60.25 | 10073 | 40726 | 962822 | 2658 |
| Fetch | 0 | 0.00 | 0.00 | 0 | 0 | 0 | 0 |
| total | 2 | 50.76 | 60.29 | 10073 | 40726 | 962822 | 2658 |
Rows Rows Source Operation
'BT_REG_BASE' 테이블을 Full Tables Scan 하여 150842 건을 액세스 하여 조건절을 체크하였더니 2658건이 되었고
이를 다른 테이블에 입력하였다는 것을 의미.
이때 Select 한 테이블은 실행계획에 나타나지만 입력된 테이블은 해당 SQL 에서만 확인할 수 있다.
또한 'Execute' 란에만 숫자가 나타난다. 액세스를 한 테이블에 대해서는 'Fetch'가 발생하였지만
실행통계에는 액세스와 입력 작업에서 발생한 것을 모두 합해서 두 번째 라인에 기록한다.
{*}조인에서 발생하는 Full Tables Scan. <NestedLoops 조인시 선행 처리에서 Full Tables Scan이 발생하는경우>*
Rows Execution Plan
① 'ITEM_BASE'를 Full Tables Scan으로 액세스한 로우수는 148046.
이중 조건절을 통과한 로우수는 7724. 이것은 'PK_CS_SPEC' 에 연결을 시도한 회수를 보고 알아낼 수 있음
② 선행테이블의 조건을 통과한 7724건이 'CS_SPEC' 테이블의 기본키를 이용하여 연결을 시도. 이중 5건은 실패.
이는 7724 - 7719 를 해보면 알 수 있음.
③ 기본키의 ROWID 로 액세스 하였다. 근데 'CS_SPEC' 에도 체크조건이 있었다는걸 알 수 있음.
이는 7719 - 7701 를 해보면 알 수 있음.
④ 최종결과가 7701 임을 알 수 있음.
{*}조인에서 발생하는 Full Tables Scan. <NestedLoops 조인시 후행 처리에서 Full Tables Scan이 발생하는경우>*
Rows Execution Plan
① 'BAL_ITEM' 테이블의 기본키를 범위 스캔하면서 Rowid 로 테이블을 액세스한 건이 210991 이지만.
실제 테이블을 액세스 한것은 74861건. 이러한 현상이 나타나는 이유는
최소 두개 이상의 컬럼에 조건이 부여되었지만 이들이 결합인덱스로 구성된 기본키에서 연속된 순서를 가지고 있지 않다는것을 나타낸다.
즉, 이 컬럼들 사이에 조건으로 부여하지 않은 하나이상의 컬럼이 존재 한다는것.
② 테이블을 액세스한 78461 중에서 체크조건에 의해서 다시 걸러지고 남은것은 280건.
그 이유는 이 조인은 아우터 조인이기 때문에 연결에 실패 하였더라도 조인은 언제나 성공이므로
그 조인결과 집합인 280과 동일하다.
또한.
선행테이블(BAL_ITEM)이 이미 범위 처리를 했기때문에 나중에 연결되는 집합은 그 테이블의 기본키로 유일하게 액세스 되어야 한다.
그러나 연결고리에 인덱스가 없기때문에 연결 대상마다 매번 Full Tables Scan를 하였고.
③ 이 테이블(TPF_INFO)을 스캔한 로우스는 53200이지만 연결을 시도한 횟수는 280 이다.
그렇다면 TPF_INFO 의 총 로우 수는 190(=53200/280) 이다.
④ 연결을 시도한 280건이 아우터 조인에 의해 모두 성공하게 되므로 최종결과는 280건.
ROWID는 그 로우를 포함하고 있는 데이터파일과 데이터 블록, 그리고 블럭 내에서의 위치를 가지고 있다.
그러므로 하나의 로우를 찾는 가장 빠른 방법.
대부분의 ROWID 스캔은 인덱스를 경유하여 테이블을 액세스 하는 과정에서 발생.
실제적으로 가장 많이 발생하는 방식.
로우를 추출할때 결과를 보면 로우를 찾는것이지만 실제 내부적인 I/O는 언제나 블록을 액세스 한다.
따라서 옵티마이져가 비용을 산정할때도 블록을 기준으로 계산한다.
이는 클러스터링 팩터가 얼마나 좋으냐에 따라 액세스 효율에 커다란 영향을 미치게 된다.
인덱스 스캔을 좀더 세부적으로 분류하면 다음과 같다.
가) 인덱스 유일 스캔 (Index unique Scan)
나) 인덱스 범위 스캔 (Index Range Scan)
다) 인덱스 역순 범위 스캔 (Index Range Scans Descending)
라) 인덱스 스킵 스캔 (Index Skip Scan)
마) 인덱스 전체 스캔 (Ful Scan)
바) 인덱스 고속 전체 스캔 (Fast Full Index Scan)
사) 인덱스 조인 (Index Join)
아) 비트맵 인덱스 (Bitmap Index)
가) 인덱스 유일 스캔 (Index unique Scan)
단 하나의 Rowid를 추출한다.
인덱스가 기본키나 유일인덱스 (Unique index)로 생성되어 있어야 하며
인덱스를 구성하는 모든 컬럼들이 모두 조건절에서 '=' 비교 되어야 한다.
인덱스 유일 스캔 (Index unique Scan)로 유도해야 하는 경우 특정 인덱스 사용을 권고하는 'INDEX(Table_Alias Index_Name)' 힌트를 준다.
나) 인덱스 범위 스캔 (Index Range Scan)
가장 보편정인 액세스 형태.
시작과 종료를 가진 경우와 하나 이상이 끝을 가지지 않은 경우가 있다.
이 스캔을 경유 하여 추출되는 로우는 인덱스 구성 컬럼의 정렬 순서와 동일하게 나타난다.
최초의 시작점을 찾을 때만 랜덤 액세스를 사용하고 그 후로 종료시 까지는 스캔을 한다.
즉, Branch Block 를 경유 하여 Leaf Block 을 찾은후 연결된 다음 Leaf Block 를 스캔 하다 종료점을 만나면 멈춘다.
스캔 범위가 넓어질때 부하가 증가하는것은 인덱스 탓이 아니라 인덱스의 ROWID로 테이블을 랜덤 액세스 해야 하는 부분이다
따라서 인덱스 범위 스캔 (Index Range Scan) 클러스터링 팩터에 직접적인 영향을 받는다.
인덱스 범위 스캔 (Index Range Scan)로 유도하는 힌트는 'INDEX(Table_Alias Index_Name)' 이다.
다) 인덱스 역순 범위 스캔 (Index Range Scans Descending)
역순으로 데이터를 액세스 한다는 것을 제외 하면 인덱스 범위 스캔 (Index Range Scan)와 동일.
인덱스는 순차적으로 정렬되어 저장된다.
따라서 이 스캔은 가장 최근의 값을 가장 처음 스캔할 수 있다.
이는 실무적으로 많은 도움이 된다.
인덱스 역순 범위 스캔 (Index Range Scans Descending)을 유도하는 힌트는 'INDEX_DESC(Table_Alias Index_Name)' 이다
라) 인덱스 스킵 스캔 (Index Skip Scan)
sal_tp(매출유형) + item_cd(상품코드) + sal_dt(매출일자) 로 구성된 인덱스가 있고
쿼리의 조건에는 item_cd , sal_dt만 사용되었다. sal_tp는 D,E,L 세종류만 있다고 가정한다.
인덱스 스킵 스캔이 적용 되었다면 마치 조건절에 sal_tp IN ('D','E','L') 을 추가한 것과 동일한 효과를 얻을 수 있다.
여기서 D,E,L 을 논리적 서브 인덱스 라고 부른다. 인덱스 스킵 스캔은 서브인덱스의 종류가 많지 않고 뒤에 오는 컬럼은 종류가 많을때
가장 좋은 결과를 얻을수 있다. 이말은 이런 경우가 아니라면 큰 효과를 얻을 수 없다는 뜻이다.
인덱스 스킵 스캔으로 유도 하는 방법은 'INDEX_SS','INDEX_SS_ASC','INDEX_SS_DESC' 등이 있고
인덱스 스킵 스캔을 하지 않고 싶다면 'NO_INDEX_SS'를 사용한다.
마) 인덱스 전체 스캔 (Ful Scan)
조건절에서 그 인덱스의 컬럼이 적어도 하나이상 사용 되었을 때 적용이 가능하다. 즉, 반드시 선행컬럼이 사용되어야 할 필요는 없다는 것.
근데 만약 아래 두 조건을 모두 만족한다면 조건절에 전혀 사용된 컬럼이 없어도 적용 가능하다.
＊쿼리 내에 사용된 어떤 테이블들의 모든 컬럼들이 그 인덱스에 모두 존재하고
＊인덱스 컬럼 중에서 최소한 NOT NULL 인 컬럼이 하나는 존재할 때
바) 인덱스 고속 전체 스캔 (Fast Full Index Scan)
만약 쿼리를 위해 사용된 어떤 테이블의 컬럼이 모두 그 인덱스에 포함되어 있다면 인덱스 고속 전체 스캔은 전체테이블 스캔의 대안으로
사용될 수 있다. 단, 인덱스 전체 스캔 (Ful Scan) 과 마찬가지로 NOT NULL 제약조건의 컬럼이 반드시 하나 이상 존재해야 한다.
이 스캔으로 유도 하고 싶다면 'INDEX_FFS' 그렇지 않다면 'NO_INDEX_FFS' 로 해제 한다.
사) 인덱스 조인 (Index Join)
별도의 장에서 상세히 설명
아) 비트맵 인덱스 (Bitmap Index)
별도의 장에서 상세히 설명
1:M 관계를 가진 두 테이블을 클러스터링 하면 동일한 클러스터 키값을 가진 두 테이블의 모든 로우는 같은 클러스터 내에 저장된다.
이때 1쪽의 데이블을 클러스터키로 액세스 하면 하나의 로우가... M 쪽을 액세스 하면 여러개가 나타날 것이다.
인덱스를 이용하여 데이터를 액세스 하는 방법은 반드시 인덱스 I/O 와 테이블 I/O를 거쳐야 하지만
해쉬클러스터의 데이터 접근경로는 해쉬함수를 생성하는 것과 테이블 I/O로만 구성되므로 그만큼 I/O를 줄일 수 있다.
따라서 넓게 산포된 테이블의 액세스에서 디스크 I/O를 줄임으로써 시스템 성승향상을 기대할 수 있다.
이 스캔은 해쉬함수를 통해서 데이터를 액세스 해야하므로 액세스 형태가 다양하지 않고
주로 '=' , 'BETWEEN', 'IN'으로 적용할 수 있는 테이블에 적용해야 한다.
또한 해쉬키로 지정된 컬럼이 자주 손상되지 않는것이 좋고 대량으로 데이터가 증가하지 않는것이 바람직 하다.
테이블의 데이터 중에서 사용자가 부여한 비율 만큼의 데이터를 읽고 그중에서 조건을 만족하는 로우들을 리턴 한다.
테이블의 표본 데이터를 스캔하는 방식은 아래와 같다.
SELECT .....
FROM tabel_name SAMPLE BLOCK option (Sample Percent)
WHERE ...
GROUP BY ....
HAVING ....
ORDER BY .....
SAMPLE BLOCK(sample percent) 를 사용하면 전체 액세스 대상 블록에서 지정한 비율(sample percent) 만큼의 블록을 읽은 후
조건을 만족하는지 확인한다. 여기서 숫자는 확률값을 의미 하므로 수행할 때마다 다른 블록이 나타날수 있다.
{*}SAMPLE BLOCK(sample percent){*}를 지정하면 모든 블록이 액세스되지만 각각의 블록에서 지정한 비율만큼의 로우들을 임의로 선택한 후
이를 대상으로 조건을 체크하여 결과를 리턴한다.
{*}비율(Sample Percent){*}는 0.000001 과 99.999999 값을 지원하며 , 0 또는 100을 지원하지 않는다.
{*}주의사항{*}은 로우 수가 작은 테이블에서 견본 데이터를 액세스하면 일정 비율의 데이터가 리턴되지 않을 수도있다.
{*}이 기능은{*} 과거에는 하나의 테이블에서 쿼리를 할 때만 사용가능 하였으나 10g 부터는 이런 제한이 없어졌다.
또한 비용기준 옵티마이져를 사용하게 되며 'RULE' 힌트를 사용하더라도 비용기준으로 수행된다.