20180308 오라클 버전 확인, 옵티마이저 접근 경로 (Scan 종류)

접근 경로( Access Path )

옵티마이저가 각각의 실행계획에 따른 비용을 산정하는데 있어 테이블이나 뷰의 데이터를 읽어오는 방식을 접근 경로(Access Path) 라고 한다. 

오라클 버전확인은 아래와 같이 작성한다.

SQL> SELECT * FROM v$version WHERE banner LIKE 'Oracle%';

오라클 버전

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

접근 경로 종류

Table Access Full Scan

전체 테이블 스캔이며 쿼리를 수행하기 위해 테이블 전체를 읽는 방식을 의미한다. 예를 들어 부서 번호가 10인 사원을 찾는 쿼리가 있는데 Table Access Full Scan 방식을 이용하면, 테이블에 존재하는 데이터 블록을 연속적으로 처음부터 끝까지 검색한다. 그리고 검색 도중에 부서번호가 10인 사원을 찾는 것이다.

일반적으로 Table Access Full Scan 은 인덱스를 사용한 검색에 비해 비효율적인 접근 방식으로 알려져 이으며 실제로 비효율적이다. 하지만 무조건 Table Access Full Scan 이 안좋은 것은 아니다. 오히려 대용량 데이터를 조회하는 경우, 100만건의 데이터 중에서 80만건을 선택하는 경우라면 인덱스를 사용하는 것보다 테이블 전체를 검색하는 것이 비용적으로 적게 든다.

옵티마이저가 Table Access Full Scan 방식의 비용이 더 낫다고 평가하는 경우는 아래와 같다.

• 인덱스가 존재하지 않거나 쿼리가 인덱스를 사용하지 못하는 경우

• 대용량의 데이터를 조회하는 경우

• 소용량의 테이블을 경우

ROWID Scan

ROWID 는 테이블에 저장된 로우에 대해 정확한 위치 정보를 가지고 있으므로, ROWID를 이용한 검색방법은 단일로우를 검색하는 데에 있어서 가장 빠른 접근 방법이다. 일반적으로 ROWID에 대한 접근은 인덱스를 사용한 검색 후, 두 번째 단계에서 사용된다. 즉 인덱스를 먼저 검색한 뒤에 인덱스로부터 ROWID를 추출해낸 다음 ROWID를 사용하여 검색을 하게 되는 것이다.

Index Unique Scan

인덱스를 사용한 검색 방법으로, 사용되는 인덱스가 UNIQUE 인덱스일 경우에 이 방법을 사용한다. 따라서 검색 속도는 매우 빠르며, UNIQUE 한 값을 검색하게 되므로 이 방법을 사용한 검색 결과로 산출되는 로우는 단 한개이다.

Index Range Scan

인덱스를 사용해서 인덱스 컬럼에 대한 범위(Range)검색을 하는 방법이다. Index Range Scan 의 쿼리의 결과가 인덱스가 걸린 컬럼 값을 기준으로 오름차순으로 정렬된다. 그리고 해당 컬럼값이 같은 로우에 대해서는 ROWID 값을 기준으로 오름차순 정렬이 되어 결과가 나타난다. 

column_ 라는 컬럼이 인덱스 컬럼이고 WHERE 조건에서 아래와 같은 형태로 사용되는 경우 Index Range Scan을 이용해 검색하게 된다.

• column_ = value
• column_ < value
• column_ > value
• column_ LIKE 'ABC%'

여기서 column_ LIKE '%ABC' 형태가 되면 Index Range Scan 을 하지 않고 Table Access Full Scan 을 실시한다.

[ LIKE 연산자 '%' 위치에 따른 INDEX RANGE SCAN ]

[ LIKE 연산자 '%' 위치에 따른 TABLE ACCESS FULL SCAN ]

Fast Full Index Scan

쿼리에 대한 모든 컬럼이 인덱스 컬럼인 경우 해당 접근 방식을 이용한다. 테이블이 아닌 인덱스 자체 내에서 데이터를 Full Scan 방식으로 접근하는 방법이다. 

+) Fast Full Index Scan 또한 쿼리의 결과가 인덱스가 걸린 컬럼 값이 기준이기 때문에 오름차순으로 정렬되서 결과값이 나온다.

Index Range Scan 과 Fast Full Index Scan 이 오름차순으로 정렬되서 결과값이 나온다는 것을 어떻게 확인할 수 있을까? ROWNUM 활용

ROWNUM 을 통해 테스트

(1) 우선 두 개의 테이블 TEST_TB 와 TEST_TB_NONE 을 만든다. TEST_TB 는 T_NUM 컬럼에 PK 생성

(2) 각각의 테이블에 데이터를 삽입 ( 값을 넣은 순서는 두 테이블 모두 동일 )

INSERT INTO TEST_TB(T_NUM) VALUES(1);

INSERT INTO TEST_TB(T_NUM) VALUES(57);

INSERT INTO TEST_TB(T_NUM) VALUES(23);

INSERT INTO TEST_TB(T_NUM) VALUES(41);

INSERT INTO TEST_TB(T_NUM) VALUES(30);

INSERT INTO TEST_TB(T_NUM) VALUES(2);

INSERT INTO TEST_TB(T_NUM) VALUES(11);

INSERT INTO TEST_TB(T_NUM) VALUES(6);

INSERT INTO TEST_TB(T_NUM) VALUES(26);

INSERT INTO TEST_TB(T_NUM) VALUES(37);

INSERT INTO TEST_TB(T_NUM) VALUES(5);

INSERT INTO TEST_TB(T_NUM) VALUES(9);

INSERT INTO TEST_TB(T_NUM) VALUES(19);

INSERT INTO TEST_TB(T_NUM) VALUES(15);

INSERT INTO TEST_TB(T_NUM) VALUES(12);

(3) TEST_TB(PK 존재)와 TEST_TB_NONE 을 조회 ( 일반적인 조회 )
+) ROWNUM 도 같이 출력. ROWNUM이란 오라클에서 결과집합에 대한 순번이다. 결과집합이 메모리에 생성된 이후에 각각의 레코드에 붙여지는 가상의 순번.

내용을 살펴보면 ROWNUM 은 레코드에서 순서대로 나오는 반면 컬럼 값인 T_NUM 은 데이터를 입력한 순서대로 나오고 있다. 

(4) TEST_TB(PK 존재)와 TEST_TB_NONE 을 조회 ( Index Range Scan : column_ <= value ) 

(3)의 결과에서는 두 테이블모두 동일한 순서대로 쿼리집합을 생성한 반면에 (4)의 결과를 보면 Table Access Full Scan(TEST_TB_NONE 테이블)과 Index Range Scan(TEST_TB 테이블) 간의 명확한 차이가 존재함을 알 수 있다. 

추가내용)

만약에 위의 해당 테이블에서 Index Fast Full Scan 을 확인하고 싶다면 아래의 쿼리를 써서 비교분석하면 된다.

(1) SQL> SELECT ROWNUM, T_NUM FROM TEST_TB ORDER BY T_NUM ASC;

(2) SQL> SELECT ROWNUM, T_NUM FROM TEST_TB_NONE ORDER BY T_NUM ASC;

(1) 은 Index Fast Full Scan 을 시도하고 (2) 는 Table Access Full Scan 을 시도한다. 출력되는 값은 (1) 의 경우 ROWNUM 과 T_NUM 이 순서대로 나타나는 반면에, (2) 의 경우 T_NUM은 ORDER BY 를 통해서 정렬되어 출력되지만 ROWNUM은 그 이전에 SELECT 시점에서 붙여지기 때문에 순서대로 나타나지 않는다. 

본인이 직접 해보고 확인하는 습관을 가지자.

[ 참고자료 및 추가 ]

[ 뇌를 자극하는 오라클 프로그래밍 SQL & PL/SQL ]

[ 데이터베이스 인덱스(Index) : 인덱스 기본 ]