ORACLE_013. Understanding Access Path : Full Table Scans

Understanding Access Path : Full Table Scans                                          

FULL TABLE SCANS
 이 형식의 스캐닝 방식은 테이블에서 모든 자료를 읽어들인 후 필터를 통해 원하는 행을 얻어내는 방식 입니다. 풀 테이블 스캔이 일어나는 동안 하이워터마크(High Water Mark, HWM)밑에 있는 테이블 블록은 모두 읽혀집니다. 각각의 행은 구문의 where 절의 조건에 맞는지 확인됩니다.

 풀 테이블 스켄이 수행될때 오라클은 모든 블럭을 순차적으로 읽습니다. 이는 블럭들이 근접해 있으면, 하나의 블록을 읽어들이는 것 보다 더 많이 읽어드리기에 프로세스의 속도를 높일 수 있기 때문입니다. 한개의 블럭에서 여러개의 블럭을 읽어드리는 크기는 DB_FILE_MULTIBLOCK_COUNT 파라메터에 나타나 있습니다. 다중 블럭 읽기(multiblcok read)는 풀 테이블 스켄에서 매우 효율적 입니다. 각각의 블록은 단 한번만 읽힙니다.

WHY A FULL TABLE SCAN IS FASTER FOR ACCESSING LARGE AMOUNTS OF DATA
 풀 테이블 스켄은 테이블의 크고 조각난 블록에 접근할 경우 인덱스 범위 스켄(Index Range Scan)보다 비용이 더 싸게 먹힙니다. 풀 테이블 스켄은 큰 I/O 단위를 사용하는데 이는 작은 I/O를 여러번 호출하는 것 보다 비용이 싸기 때문이죠.

WHEN THE OPTIMIZER USES FULL TABLE SCAN
 다음의 경우에 수행합니다.

 Lack of Index
 질의(Query)가 존재하는 인덱스를 사용할 수 없으면, 풀 테이블 스켄을 수행합니다. 인덱싱된 컬럼에 펑션(function)을 사용할 경우 인덱스를 사용하지 않고 풀 테이블 스켄을 사용합니다.
  Example)
  SELECT last_name, first_name
  FROM   employees
  WHERE UPPER(last_name) LIKE :b1

  * 만약 케이스에 의존하는 검색을 수행할 경우 검색하는 컬럼에 대하여 케이스를 섞는것을 허용하지 말거나 펑션 기반의 인덱스, 예를들어 UPPER(last_name)를 만드는 것을 허용하지 마십시요. 좀더 자세한 정보를 원하시면 아래 접어놓은 내용을 참고 하세요. (영문)

 

Function-Based Index

Using Function-based Indexes

A function-based index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression, such as col1 + col2.

Defining a function-based index on the transformed column or expression allows that data to be returned using the index when that function or expression is used in a WHERE clause or an ORDER BY clause. Therefore, a function-based index can be beneficial when frequently-executed SQL statements include transformed columns, or columns in expressions, in a WHERE or ORDER BY clause.

Function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. For example, the following index:

CREATE INDEX uppercase_idx ON employees (UPPER(last_name));

facilitates processing queries such as:

SELECT * FROM employees
    WHERE UPPER(last_name) = 'MARKSON';

Setting Parameters to Use Function-Based Indexes in Queries

To use function-based indexes in queries, you need to set the QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY parameters.

QUERY_REWRITE_ENABLED

To enable function-based indexes for queries, set the QUERY_REWRITE_ENABLED session parameter to TRUE. QUERY_REWRITE_ENABLED can be set to the following values:

• TRUE: cost - based rewrite
• FALSE: no rewrite
• FORCE: forced rewrite

When QUERY_REWRITE_ENABLED is set to FALSE, then function-based indexes are not used for obtaining the values of an expression in the function-based index. However, function-based indexes can still be used for obtaining values in real columns.

When QUERY_REWRITE_ENABLED is set to FORCE, Oracle always uses rewrite and does not evaluate the cost before doing so. FORCE is useful when you know that the query will always benefit from rewrite, when reduction in compile time is important, and when you know that the optimizer may be underestimating the benefits of materialized views.

QUERY_REWRITE_ENABLED is a session-level and also an instance-level parameter.

QUERY_REWRITE_INTEGRITY

Setting the value of the QUERY_REWRITE_INTEGRITY parameter determines how function-based indexes are used,

• If the QUERY_REWRITE_INTEGRITY parameter is set to ENFORCED (the default), then Oracle uses function-based indexes to derive values of SQL expressions only. This also includes SQL functions.
• If QUERY_REWRITE_INTEGRITY is set to any value other than ENFORCED, then Oracle uses the function-based index, even if it is based on a user-defined, rather than SQL, function.

Function-based indexes are an efficient mechanism for evaluating statements that contain functions in WHERE clauses. You can create a function-based index to store computation-intensive expressions in the index. This permits Oracle to bypass computing the value of the expression when processing SELECT and DELETE statements. When processing INSERT and UPDATE statements, however, Oracle evaluates the function to process the statement.

For example, if you create the following index:

CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b); 

then Oracle can use it when processing queries such as:

SELECT a 
    FROM table_1 
    WHERE a + b * (c - 1) < 100;

You can also use function-based indexes for linguistic sort indexes that provide efficient linguistic collation in SQL statements.

Oracle treats descending indexes as function-based indexes. The columns marked DESC are sorted in descending order.

 

 Large Amount of data
  만약 옵티마이저가 생각하기에 테이블의 대부분의 블럭을 읽어들인다 판단하면 인덱스가 존재한다 하더라도 풀 테이블 스켄을 수행합니다.

 Small Tables

  테이블의 하이워터마크 밑의 블럭이  DB_FILE_MULTIBLOCK_COUNT 에 정의되어 있는 값보다 적은 경우, 즉 단 한번의 I/O만으로 테이블을 전부 읽을 수 있는 경우 풀 테이블 스켄을 수행합니다. 이럴경우 인덱스가 존재한다고 하여도 풀 테이블 스켄을 수행합니다.

 

 High Degree of Parallelism

  고도(High Degree)의 테이블 Skew 데이터라면 옵티마이저는 풀 테이블 스켄을 수행합니다. ALL_TABLES에서 DEGREE 값을 확인할 수 있습니다.

FULL TABLE SCAN HINTS
 FULL(table_alias)를 이용하여 테이블 스켄을 강제로 할 수 있습니다.
 
 SELECT /*+ FULL(e) +/ employee_id, last_name
 FROM employees e
 WHERE last_name LIKE :b1;

ASSESSING I/O BLOCKS, NOT ROWS
 오라클은 I/O 단위로 블럭을 다룹니다. 그러므로 옵티마이저는 행의 갯수가 아닌 블럭의 사용률에 따라 풀 테이블을 할지 결정합니다. 이는 인덱스 클러스터링 값으로 불리워집니다. 하나의 블럭에 하나의 행만 존재할시 Row에 접근하는 것과 블록에 접근하는 것이 같은 소요비용이 소모됩니다.

 하지만 대부분의 테이블은 각각의 블록에 여러개의 행을 가지고 있습니다. 그러므로 여러개의 행들을 최소의 블록에 함께 클러스터링 하기를 바랍니다. 그렇지 않으면 많은 수의 블럭에 데이터들이 퍼져나갈 것 입니다.

HIGH WATER MARK(HWM) IN DBA_TABLES
 DDT(Data Dictionary Table)는 삽입된 행이 차지하고 있는 블록의 트랙을 가지고 있습니다. HWM은 풀 테이블 스켄시 끝점을 나타냅니다. HWM은 DBA_TABLES의 BLOCKS에 저장되어 있습니다. 이 값은 테이블이 truncated 혹은 drop 될시 초기화 됩니다.

 예로 과거에 많은 행을 가지고 있던 테이블이 있었다고 가정해 봅시다. 대부분의 행이 최근에 지워졌습니다. 그래서 지금은 HWM 밑의 많은 블럭들이 빈 상태입니다. 이때 풀 테이블 스켄을 수행시 HWM까지 읽어드리기 때문에 좋지 못한 성능을 내게 됩니다.

ROWID SCANS

 각 행의 ROWID는 데이터 파일에 지정되어 있으며 데이터 블록은 행과 그 행위 위치한 정보를 포함하고 있습니다. ROWID 에 의해 위치가 지정된 행은 한개의 행을 가져오는데 가장 빠른 방법입니다.

 ROWID를 이용해 TABLE에 접근하려면 오라클은 우선 WHERE 구문 혹은 하나이상 테이블 인덱스를 통해 선택된 행에 대해서 ROWID를 획득합니다. 그리고 그 행의 ROWID를 이용하여 테이블에 각각 위치시킵니다.

WHEN THE OPTIMIZER USES ROWIDS
 일반적으로 인덱스에서 ROWID를 얻어낸 후의 다음 단계입니다. 인덱스가 존재하지 않는 컬럼에 대해서도 테이블에 대한 접근이 일어날 수 있습니다. ROWID를 이용한 접근방법은 다음에 나올 인덱스 스켄이 필요하지 않습니다. 구문에 사용되는 컬럼이 모두 인덱스가 있다면 ROWID를 이용한 접근은 일어나지 않을 것 입니다.

 

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"