ORACLE_014. Understanding Access Path : Index Table Scans

Understanding Access Path : Index Table Scans                                  

INDEX SCAN

 이 방법은 구문에서 사용하는 컬럼의 인덱스를 이용하여 행을 뽑아오는 방법입니다. 인덱스 스켄은 인덱스의 하나 혹은 그 이상의 컬럼값에 기초하여 데이터를 추출합니다. 인덱스 스켄을 수행하면 구문에 사용되는 컬럼중 인덱스가 있는 컬럼이 있는지를 우선적으로 찾습니다. 인덱스가 존재하면 인덱스가 있는 컬럼 값을 인덱스에서 바로 읽어옵니다.

 인덱스는 인덱싱 된 값 뿐만 아니라 행의 Rowid도 가지고 있습니다. 따라서 인덱스된 컬럼 외에 다른 컬럼값 역시 한번에 가져올 수 있습니다.

INDEX UNIQUE SCAN
 UNIQUE 나 PRIMARY KEY 로 제약조건이 걸려있는 컬럼에 대하여 접근할때 UNIQUE SCAN을 사용합니다.

 When the optimizer Uses Index Unique Scans
 이 접근경로는 동등한 조건식에 Unique Index 가 모든 컬럼에 정의되어 있을때 사용합니다.

 Index Unique Scan Hints
 일반적으로, Unique Scan 수행시 따로 Hint절을 달 필요는 없습니다. 힌트절 INDEX(alias index_name)는 인덱스를 사용함을 결정합니다만 접근 경로까지 결정하지는 않습니다.

INDEX RANGE SCAN
 Index Range Scan 은 일반적으로 선택된 데이터에 접근하는 가장 평범한 방법입니다. 결과는 인덱스 컬럼의 오름차순으로 정렬되어 반환됩니다. 동등한 값의 여러개의 행은 rowid에 의해 오름차순으로 정렬됩니다.

 데이터가 반드시 정렬되어야 한다면 ORDER BY 구문을 이용하고 인덱스의 정렬에  너무 의지하지 마십시요. 인덱스가 ORDER BY 문에 적용될 수 있게 정렬된 상태면 옵티마이저는 인덱스 정렬을 사용합니다.

 EXAMPLE_Index Range Scan                           
 SELECT order_status, order_id
  FROM   orders
 WHERE order_date = :b1;
 

 
 상기 쿼리는 분명 선택적인 쿼리 입니다. 행을 가져오기 위해 컬럼의 인덱스를 사용하는 것을 확인 할 수 있습니다. 반환된 데이터는 order_date 컬럼의 rowid 에 의해 오름차순으로 정렬됩니다. 이는 order_date 의 인덱스 컬럼이 여기서 선택된 행들과 같기때문에 데이터는 rowid로 정렬됩니다.

 When the Optimizer Uses Index Range Scans
 옵티마이저는 다음과 같은, 쿼리의 주가 되는 조건에 인덱스가 있는 컬림이 하나 혹은 그 이상의 컬럼이 있으면 옵티마이저는 Index Range Scan을 수행합니다.

 - col1 = :b1
 - col1 < :b1
 - col1 > :b1
 - 위 조건들이 인덱스에서 주가 되는 컬럼이고 이러한 조건들의 조합

 Range Scans는 unique 혹은 nonunique 인덱스를 사용합니다. Range Scan은 인덱스 컬럼이 ORDER BY/GROUP BY 와 구성되어 있으면 정렬을 수행하지 않습니다.

 Index Range Scan Hint
 옵티마이저가 풀테이블 스켄이나 다른 인덱스를 사용하려 할때 hint절을 사용할 수 있습니다. 힌트절 HINT(table_alias index_name)은 사용할 인덱스를 정할 수 있습니다. order_id 라는 컬럼이 비대칭(skew) 분포를 이루고 있다고 가정합시다. 컬럼은 히스토그램을 가지고 있고,  옵티마이저는 그 컬럼의 분포도를 알고 있습니다. 하지만 바인드 변수(Bind Variable) 옵티마이저는 값을 알 수 없거니와 테이블 스켄을 수행할 수 있습니다. 두가지 옵션이 있습니다.

 - nonsharing SQL 동안 문제를 야기시킬 수 있는 바인드 변수를 제거하고 명확한 값을 넣으세요
 - 힌트절을 사용합니다.

 EXAMPLE_Before Using Index INDEX hint                
 SELECT l.line_item_id, order_id, l.unit_price * l.quantity
    FROM order_items
 WHERE l.order_id = :b1
 

 

 EXAMPLE_Using Bind Variable and INDEX hint                                  
 SELECT /*+ INDEX(l item_order_ix) */ l.line_item_id, order_id, l. unit_price * l.quantity
   FROM order_items l
 WHERE l.order_id = :b1

INDEX RANGE SCANS DESCENDING
 Index range scan descending 은 내림차순으로 정렬되어 데이터가 반환되는 것을 제외하고는 Index range scan과 같습니다. 기본적으로 인덱스는 오름차순으로 저장됩니다. Index range scan descending은 가장 최근의 데이터를 얻거나 특정 값보다 적은 값을 찾을때 사용합니다.

 EXAMPLE_Index Range Scan Descending Using Two-Column Unique Index                  
 SELECT line_item_id, order_id
  FROM order_items

 WHERE order_id < :b1
 ORDER BY order_id DESC;
 

 

 데이타에서 선택된 행은 order_id, line_item, rowid 에 의해 내림차순 정렬이 됩니다.

 When the Optimizer Uses Index Range Scans Descending
 order by descending 단서가 인덱스를 만족하면 옵티마이저는 index range scan descending 을 수행합니다.

 Index Range Scan Descending Hints
 힌트절 INDEX_DESC(table_alias index_name) 는 이 접근경로를 사용하는데 사용됩니다.

INDEX SKIP SCAN
 Index Skip Scan은 nonprefix 컬럼을 읽는데 인덱스의 성능을 향상시킵니다. 자주, 인덱스 블럭을 읽는것은 테이블 블럭을 읽는것 보다 빠릅니다.

 Skip Scan은 여러개의 컬럼을 가진 인덱스를 논리적으로 작게 서브인덱스로 나눕니다.  Skip Scan에서 다열 인덱스의 이니셜 컬럼은 쿼리에서 정의되지 않습니다. 즉 무시(skip) 합니다.

 수개의 논리적 서브인덱스는 이니셜 컬럼에서 중복된 값의 갯수에 의해 정의됩니다. Skip Scan은 복잡한 인덱스의 리딩컬럼에 중복되는 값이 거의 없거나 리딩컬럼이 아닌 컬럼에 많은 수의 중복된 값이 있을때 효과적입니다.

 EXAMPLE_Index Skip Scan
 Employee(sex, employee_id, address) 테이블이 다열 인덱스 (sex, employee_id) 를 가지고 있다고 가정하도록 하겠습니다. 이 인덱스는 두개의 논리 서브인덱스로 나뉩니다. 하나는 M 하나는 F.

 이 예제에서 다음의 인덱스 값을 가지고 있다고 가정합니다.
 ('F', 98)
 ('F',100)
 ('F',102)
 ('F',104)
 ('F',101)
 ('M',101)
 ('M',103)

 ('M',105)

 인덱스는 다음과 같은 두 논리 서브인덱스로 나뉩니다.

  - 첫번째 서브 인덱스는 키값을 'F'를 갖습니다.
  - 두번째 서브 인덱스는 키값을 'M'을 갖습니다.

 

 다음과 같은 쿼리에서 sex 컬럼은 무시됩니다.
 SELECT * FROM employees WHERE employee_id = 101;

 완전한 Index 스켄은 일어나지 않습니다. 하지만 'F'값을 가진 서브 인덱스가 우선 수행됩니다. 다음 'M'값을 가진 서브 인덱스가 수행됩니다.

FULL INDEX SCANS
 만약 술부(predicate)가 인덱스가 있는 하나의 컬럼만을 참조하면 Full Scan 이 일어납니다. 술부는 index driver가 필요치 않습니다. 또한 술부가 존재하지 않거나 다음 두가지의 경우 full scan을 수행합니다.

 - 쿼리에서 사용하는 모든 컬럼에 인덱스가 존재할 경우
 - 최소 하나의 인덱스 컬럼이 Null 이 아닐 경우

 Full Scan은 정렬 과정을 생략할 수 있는데 데이터가 index key에 의해 정렬되어 있기 때문입니다.

FAST FULL INDEX SCANS
 Fast full scan 은 인덱스에 쿼리에 필요한 모든 컬럼에 대한 정보를 가지고 있을때와 최소 하나의 컬럼에 NOT NULL 제약조건을 가지고 있을때 full table scan을 할지 말지를 결정해야 합니다. Fast full scan 은 테이블에 접근할 필요 없이 인덱스에서 데이터에 접근합니다. 데이터는 인덱스 키에 의해 정렬되어 있지 않기 때문에 정렬 과정을 피할 수 없습니다. Full index scan과는 다르게 다중 블럭을 읽어들여 모든 인덱스를 읽어들입니다.

 Fast full scan은 오직 CBO에서만 작동합니다. OPTIMIZER_FEATURES_ENABLE 초기화 파라메터나 INDEX_FFS 힌트를 이용하여 정의할 수 있습니다. Fast full index scan은 비트맵 인덱스(bitmap index)에 대항하여 수행할 수 없습니다.

 Fast full index scan은 일반 full index scan 보다 멀티블락 I/O를 사용할때 보다 더 빠르며 테이블 스켄처럼 병렬처리가 가능합니다.

Fast Full Index Scan Hints
 Fast Full Index Scan은 특별한 인덱스 힌트를 가지고 있습니다. INDEX_FFS. 정규 INDEX 힌트와 같은 인수와 표현식을 사용합니다.
 
Fast Full Index Scan Restrictions
 Fast full index scan은 다음과 같은 제약이 있습니다.
 - 최소 한개의 컬럼이 NOT NULL 제약조건을 가지고 있어야 합니다.
 - 병렬로 Fast full index scan을 사용하기 위해 인덱스에 parallel 단서를 달아야 합니다.
 - 인덱스를 반드시 Anlalyze 해야합니다. 그렇지 않으면 옵티마이저는 FFS를 수행하지 않습니다.

INDEX JOINS
 Index Join은 쿼리에서 참조하는 테이블의 모든 컬럼을 가지고 있는 몇몇 인덱스의 Hash Join 입니다. Index join은 테이블에 접근할 필요가 없는데, 이는 index에서 필요한 데이터를 모두 얻을 수 있기 때문입니다. 정렬 과정을 거쳐야 하며 오직 CBO에서만 수행이 가능합니다.

Index Join Hints
 OPTIMIZER_FEATURES_ENABLE 초기화 파라메터와 INDEX_JOIN힌트절을 사용할 수 있습니다.

BITMAP JOINS
 Bitmap join이란 키값을 위해 비트맵을 사용하고 rowid 에 각각의 비트를 변환하여 위치시킵니다. 비트맵은 where 단서에서 AND나 OR 같은 Boolean Operation 같은 몇몇 조건에서 인덱스를 합치는데 매우 효율적입니다.

 Bitmap Access 는 CBO에서만 가능합니다.
 *Oracle 9i Enterprise Edition 이상의 버전에서만 Bitmap Index 혹은 Bitmap join index 사용 가능

CLUSTER SCANS
 Cluster scan은 index cluster로 저장된 테이블에서 같은 클러스터 키 값을 갖는 모든 행을 얻는데 사용합니다. Index cluster 에서 같은 클러스터 키 값을 가지고 있는 모든 행은 같은 데이터 블럭안에 저장됩니다. Cluster scan 을 수행하기 위해서는 Cluster index 스케닝을 통해 얻은 한개의 행의 rowid를 획득합니다. 그후 이 rowid를 가지고 행들을 가져다 놓습니다.

HASH SCANS
 해시값을 기초로한 해쉬 클러스터에 있는 행을 가져가기 위해 사용됩니다. 해쉬 클러스터에서 해시 값이 같은 모든 데이터는 같은 데이터 블럭에 위치합니다. Hash scan을 수행하면 오라클은 해시값을  얻고, 얻은 해시값을 가지고 있는 행을 포함하고 있는 데이터 블럭을 스켄합니다.

SAMPLE TABLE SCANS
 무작위로 테이블에서 샘플 데이터를 수집합니다. FROM 단서에 SAMPLE 혹은 SAMPLE BLOCK 단서가 존재하면 이 접근방법을 사용합니다. 행단위 샘플링(SAMPLE clause)을 통한 Sample table scan을 수행하면 테이블의 일정 %만큼의 행을 읽습니다. 블럭 단위의 샘플링을 수행하면 (SAMPLE BLOCK clause), 특정 %의 테이블 블럭을 읽습니다.

 쿼리에 Join 혹은 원격 테이블이 포함되어 있따면 Sample table scan을 수행하지 않습니다.

 EXAMPLE_Sample Table Scan                                                       
 SELECT * FROM employees SAMPLE BLOCK (1);

Hints for access pat(영문)

FULL

The FULL hint explicitly chooses a full table scan for the specified table.

full_hint::=

Text description of the illustration full_hint.gif

where table specifies the name or alias of the table on which the full table scan is to be performed. If the statement does not use aliases, then the table name is the default alias.

For example:

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e 
  WHERE last_name LIKE :b1;

Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.

Note: Because the employees table has alias e the hint must refer to the table by its alias rather than by its name. Also, do not specify schema names in the hint even if they are specified in the FROM clause.

ROWID

The ROWID hint explicitly chooses a table scan by rowid for the specified table.

rowid_hint::=

Text description of the illustration rowid_hint.gif

where table specifies the name or alias of the table on which the table access by rowid is to be performed.

For example:

SELECT /*+ROWID(employees)*/ * 
FROM employees 
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155; 

CLUSTER

The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.

cluster_hint::=

Text description of the illustration cluster_hint.gif

where table specifies the name or alias of the table to be accessed by a cluster scan.

For example:

SELECT /*+ CLUSTER */
employees.last_name, department_id
FROM employees, departments
WHERE department_id = 10 
AND employees.department_id = departments.department_id;

HASH

The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.

hash_hint::=

Text description of the illustration hash_hint.gif

where table specifies the name or alias of the table to be accessed by a hash scan.

INDEX

The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.

index_hint::=

Text description of the illustration index_hint.gif

where:

• table specifies the name or alias of the table associated with the index to be scanned.
• index specifies an index on which an index scan is to be performed.

This hint can optionally specify one or more indexes:

• If this hint specifies a single available index, then the optimizer performs a scan on this index. The optimizer does not consider a full table scan or a scan on another index on the table.
• If this hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan or a scan on an index not listed in the hint.
• If this hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The optimizer can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

For example, consider this query that selects the name, height, and weight of all male patients in a hospital:

SELECT name, height, weight
FROM patients
WHERE sex = 'm';

Assume that there is an index on the SEX column and that this column contains the values m and f. If there are equal numbers of male and female patients in the hospital, then the query returns a relatively large percentage of the table's rows, and a full table scan is likely to be faster than an index scan. However, if a very small percentage of the hospital's patients are male, then the query returns a relatively small percentage of the table's rows, and an index scan is likely to be faster than a full table scan.

Barring the use of frequency histograms, the number of occurrences of each distinct column value is not available to the optimizer. The cost-based approach assumes that each value has an equal probability of appearing in each row. For a column having only two distinct values, the optimizer assumes each value appears in 50% of the rows, so the cost-based approach is likely to choose a full table scan rather than an index scan.

If you know that the value in the WHERE clause of the query appears in a very small percentage of the rows, then you can use the INDEX hint to force the optimizer to choose an index scan. In this statement, the INDEX hint explicitly chooses an index scan on the sex_index, the index on the sex column:

SELECT /*+ INDEX(patients sex_index) use sex_index because there are few
   male patients  */ name, height, weight
FROM patients
WHERE sex = 'm';

The INDEX hint applies to IN-list predicates; it forces the optimizer to use the hinted index, if possible, for an IN-list predicate. Multicolumn IN-lists will not use an index.

INDEX_ASC

The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.

index_asc_hint::=

Text description of the illustration index_asc_hint.gif

Each parameter serves the same purpose as in the INDEX hint.

Because Oracle's default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. However, you might want to use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.

INDEX_COMBINE

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.

index_combine_hint::=

Text description of the illustration index_combine_hint.gif

For example:

SELECT /*+INDEX_COMBINE(employees salary_bmi hire_date_bmi)*/ * 
FROM employees
WHERE salary < 50000 AND hire_date < '01-JAN-1990';

INDEX_JOIN

The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

index_join_hint::=

Text description of the illustration index_join_hint.gif

where:

• table specifies the name or alias of the table associated with the index to be scanned.
• index specifies an index on which an index scan is to be performed.

For example, the following query uses an index join to access the employee_id and department_id columns, both of which are indexed in the employees table.

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/ 
    employee_id, department_id 
    FROM employees 
    WHERE department_id > 50;

INDEX_DESC

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_desc_hint::=

Text description of the illustration index_desc_hint.gif

Each parameter serves the same purpose as in the INDEX hint. For example:

SELECT /*+ INDEX_DESC(a ord_order_date_ix) */ 
       a.order_date, a.promotion_id, a.order_id
  FROM orders a
 WHERE a.order_date = :b1;

INDEX_FFS

The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

index_ffs_hint::=

Text description of the illustration index_ffs_hint.gif

For example:

SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*)
FROM order_items l, orders o
WHERE l.order_id > 50
     AND l.order_id = o.order_id;

See Also: "Full Scans"

NO_INDEX

The NO_INDEX hint explicitly disallows a set of indexes for the specified table.

no_index_hint::=

Text description of the illustration no_index_hint.gif

• If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.
• If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.
• If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

The NO_INDEX hint applies to function-based, B-tree, bitmap, cluster, or domain indexes. If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then both the NO_INDEX hint and the index hint are ignored for the specified indexes and the optimizer considers the specified indexes.

For example:

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id 
FROM employees 
WHERE employee_id > 200; 

AND_EQUAL

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.

and_equal_hint::=

Text description of the illustration and_equal_hint.gif

where:

• table specifies the name or alias of the table associated with the indexes to be merged.
• index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five.
  

 

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

        Oracle Documents (Server .920)/a96533 "Optimizer Hints"