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"

ORACLE_012. Understanding Excution Plan

Understanding Excution Plan                                                                   

 SQL 구문을 수행하기 위해서 오라클을 많은 단계의 절차를 거칩니다. 어떠한 방법으로든 사용자가 구문을 입력하게 되면 각각의 단계에서는 데이터베이스에서 물리적으로 데이터의 행을 가져오거나 혹은 보낼 준비를 합니다. 각각의 단계를 조합하는 일은 오라클이 실행계획(Execution Plan)이란 구문을 통해 수행하게 됩니다. 실행계획은 구문(Statement)에 의해 읽혀지는 테이블의 접근경로와 Join Method 에서 Join 을 수행할 테이블의 순서를 포함합니다.

OVERVIEW OF EXPLAIN
 EXPLAIN PLAIN 이라는 SQL 구문을 통하여 옵티마이저가 선택한 실행계획을 확인할 수 있습니다. 구문이 입력되면, 옵티마이저는 실행계획을 선택하고 데이터베이스 테이블에 계획에 관한 자료를 삽입합니다. 간단하게 EXPLAIN PLAN 구문을 입력하고 출력할 테이블을 질의(Query) 하면 됩니다.

 다음은 EXPLAIN PLAN 구문의 기본적인 사용 방법 입니다.

 - UTLXPLAN.SQL Script 를 이용하여 스키마에 PLAN_TABLE을 만듭니다. 
 - SQL 구문의 처음에 EXPLAIN PLAN FOR 구문을 포함시킵니다.
 - EXPLAIN PLAN FOR 구문을 수행한후, 오라클이 제공하는 plan table을 보여주는 스크립트를
   사용합니다.
 - EXPLAIN PLAN의 출력 순서는 가장 가까운 쪽에서 먼쪽으로 출력됩니다. 각 라인은 다음 단계의
   부모 단계 입니다. 두 단계의 들여쓰기가 같다면, 상위 라인이 먼저 수행됩니다.

---

      NOTES:
               - 여기에서 EXPLAIN PLAN의 결과 출력은 utlxpls.sql 스크립트를 사용합니다.
               - 각 단계에서의 EXPLAIN PLAN의 출력 테이블은 각 시스템의 환경설정이나 옵티마이저에 따라
                 다르게 보여질 수 있습니다. 

---

 EXAMPLE : Using EXPLAIN PLAN
 EXPLAIN PLAN FOR
 SELECT e.employee_id,  j.job_title, e.salary, d.department_name
   FROM    employees e, jobs j, departments d
   WHERE e.employee_id < 103
        AND e.job_id = j.job_id
        AND e.department_id = d.department_id;

 EXAMPLE : Result of EXPLAIN PLAN Output

 

STEPS IN EXECUTION PLAN
 다음 단계들은 위 예제에서 데이터베이스의 오브젝트에서 물리적으로 데이터를 얻었습니다.
  - Step 3 에서 employees의 모든 행을 읽어들였습니다.
  - Step 5 에서 JOB_ID_PK 인덱스로 job_id를 찾고, jobs 테이블에서 rowid로 연관된 행을 찾습니다.
  - Step 4 에서는 Step 5에서 rowid로 행을 찾아냅니다.
  - Step 7 에서 DEPT_ID_PK 인덱스로 각각의 department_id를 찾고 departments 테이블에서 행과 연관된 rowid를
    찾습니다.
  - Step 6 에서 Step 7에서 획득한 rowid로 departments 테이블에서 행을 찾아냅니다.
 다음 단계들은 위 예제에서 그 전 행의 소스로 반환된 행을 다룹니다.
  - Step2는 employees, jobs 테이블에서 job_id를 이용하여 Nested loop을 수행하고 Step 3,4에서 반환한 행
    소스를 받아들이고, Step 3 소스를 Step 4에서 반환한 행과 Join 합니다.  그리고 결과를 Step 2에 반환 합니다.
  -  Step 1은 Nested loop을 수행하고 2와 6 단계의 소스를 받습니다. Step 2에서의 행을 Step 6의 행과 Join 하고,
     Step 1에거 그 결과 행을 반환합니다.


UNDERSTANDING EXECUTION ORDER
 실행계획의 단계는 위 예제에서 보여주는 것 처럼 순서대로 수행되는 것이 아닙니다. 오른쪽으로 가장 들여쓰기가 되어진 행을 먼저 수행합니다. 각각의 단계의 결과는 부모 단계에게 값을 전달합니다.  쉽게 이하히기 위해 다음 그림을 참고하세요.

       <Pic : Graphical View of SQL Explain Plan in SQL Scratchpad/www.oracle.com>

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

 

ORACLE_010. OPTIMIZER - Choosing Optimizer Approach and Goal

OPTIMIZER - Choosing Optimizer Approach and Goal                        

 기본적으로 CBO의 목표는 최상의 출력결과 입니다. 이는 최소한의 자원을 이용하여 쿼리를 수행하는데 필요한 행을 읽어들이는 방법을 선택하는 것 입니다. 또한 오라클은 반응시간의 최소화를 위한 최적화도 수행합니다. 반응시간이 뜻하는 바는 SQL 구문에 의해 최소의 자원을 이용하여 첫번째 행에 접근하는 방법 입니다.

 

 옵티마이저에 의해 생성된 실행계획은 옵티마이저의 목표에 따라 다양하기도 합니다. 많은 처리량을 위한 최적화는 인덱스 스켄보다 오히려 풀 테이블 스켄이거나 Nested Loop Join 보다 Sort Merge Join 일 수 있습니다. 최상의 반응시간을 위한 최적화는 Index Scan, 혹은 Nested Loop Join 입니다.

 

 예를들어, Sort-Merge 나 Nested Loop 중 하나를 사용하여 Join 구문을 수행한다고 가정해 봅시다. Sort-merge 방식은 아마도 Query 의 모든 결과를 뱉어내는데 빠를 것이고 Nested Loop 방식은 첫번째 행을 뱉어네는데 효율적일 것 입니다. 많은 처리량에 대한 향상이 목표라면 Sort merge join 을 사용하면 될 것 입니다. 반면 좀더 빠른 반응을 원한다면, 옵티마이저는 Nested loop join 을 사용하는게 좋을 것 입니다.

 

  - Oracle Reports Application 과 같은 어플리케이션이 Batch로 동작할때는 처리량에 관해 최적화를 합니다. Batch Application 에서의 처리량은 굉장히 중요한데 사용자는 어플리케이션이 끝마치는 시간이 얼만큼 필요할지만 고려하기 때문입니다. 반응시간은 좀 덜 중요한데, 어플리케이션이 수행되는 동안 개별적으로 실행된 구문의 결과를 계산하지 않기 때문입니다.

 

 - Interactive Applicatoin (대화형 어플리케이션), 예를들어 Oracle Forms App. 나 SQL*Plus 쿼리등은 반응시간에 촛점을 맞춥니다. 사용자들은 구문을 통해 첫번째 행이나 혹은 첫 몇개의 행을 눈으로 확인하기 위해 기다리기 때문입니다.

 

 옵티마이저는 다음과 같은 SQL 문의 요소로 데이터로의 접근과 목표를 최적화 합니다.

  - OPTIMIZER MODE Initialization Parameter

  - CBO Statistics in the Data Dictionary

  - Optimizer SQL Hints for Changing the CBO Goal

OPTIMIZER_MODE INITIALIZATION PARAMETER
 OPTIMIZER_MODE 초기화 파라미터는 인스턴스에서 옵티마이저가 선택할 기본 행동 방법을 설정합니다.

 

Value

Description

CHOOSE                     ALL_ROWS     FIRST_ROWS_n      FIRST_ROWS     RULE

옵티마이저는 Cost-base와 Rule-base중 하나를 선택합니다. 이는 Statistics 가 유효한지에서 결정됩니다.     - DD(Data Dictionary)가 최소 하나의 테이블에 유효한 통계를 가지고 있다면 옵티마이저는 Cost-Based 방식을 사용합니다.   - DD에 아주 소량의 통계만 있다면 그 값이 유효할때까진 Cost-base 를 사용합니다. 하지만 옵티마이저가 반드시 다른 통계 없이 이 구문을 수행하기 위한 통계를 추측합니다. 이는 차선책의 실행계획을 수행할 수 있습니다.   - DD에 통계가 전혀 전재하지 않다면 Rule-base 방식을 사용합니다.    통계의 존재 유무를 떠나서 세션의 모든 SQL문에 Cost-base 방식을 사용합니다. 많은 작업량 처리에 적합한 최적화를 실행합니다.(최소의 자원으로 모든 구문을 해결합니다.)    통계의 유무에 상관없이 Cost-base 를 선택합니다. 또한 첫 n 개의 행을 뱉어내기 위해 최소의 시간을 소요하는 최적화를 수행합니다. n값은 1,10,100 혹은 1000 입니다.    옵티마이저는 COST를 섞어 스스로 첫번째 행을 뱉어네는데 가장 좋은 계획을 결정해 냅니다.    RULE-BASE 방식으로 설정합니다. 통계자료의 유무는 고려대상이 아닙니다.

 

 옵티마이저의 파라메터는 다음과 같은 방법으로 변경할 수 있습니다.

 

  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

 

OPTIMIZER SQL HINTS FOR CHANGING THE CBO GOAL
 개별적으로 수행하는 SQL 문의 CBO 를 변경하기 위해서 다음의 힌트를 개별 SQL에 삽입할 수 있습니다.

 - FIRST_ROWS(n),   n은 어떤 양수도 가능합니다.

 - FIRST_ROWS

 - ALL-ROWS

 - CHOOSE

 - RULE

 

CBO STATISTICS IN THE DATA DICTIONARY

 CBO가 사용하는 통계정보는 DD(Data Dictionary)에 저장되어 있습니다. DBMS_STATS 패키지나 ANALYZE 구문을 통해 물리적 저장장치의 특성이나 스키마 오브젝트의 데이터 분산정도를 수집할 수 있습니다.

 

       * ORACLE은 ANALYZE 구문보다 DBMS_STATS 패키지를 이용하여 통계정보를 모으는 것을

       권장합니다. 이 패키지는 통계정보를 병렬로 수집 가능하게 해 주며 파티션된 오브젝트의 정보

       를 모을 수 있습니다. 또한 다른 여러가지 방법으로 통계정보를 모을 수 있는 수단을 제공합니

       다. 게다가 CBO 는 DBMS_STATS를 이용해 수집한 통계정보만을 사용할 것 입니다.

 

       하지만 DBMS_STATS 보다 ANALYZE를 반드시 사용해야 할 경우가 있습니다.

        - VALIDATE 혹은 LIST CHAINED ROWS 단서를 달아 사용할때

        - FREELIST BLOCKS 의 정보를 수집할 때

 보다 효과적으로 CBO를 사용하기 위해 존재하는 데이터들의 통계정보를 가지고 있어야 합니다. SKEW DATA라 불리우는 중복되는 넓은 범위의 숫자로 이루어진 컬럼이 있다면 히스토그램 정보를 모으길 권장합니다.

 

 통계정보의 결과는 CBO에게 데이터의 유일함과 분산정도의 정보를 제공합니다. 이 정보를 이용하여 CBO는 높은 수준의 정확한 실행계획의 비용을 계산할 수 있습니다. 이는 CBO가 가장 적은 비용을 소요하여 실행 계획을 선택하는 것을 가능하게 해 줍니다.

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

 

Next => Understanding the Cost-Based Optimizer

 

ORACLE_009. OPTIMIZER - OVERVIEW

OPTIMIZER - OVERVIEW                                                                    

OVERVIEW SQL PROCESSING
 SQL 프로세싱은 SQL문을 실행하기 위해 다음과 같은 절차를 따릅니다.

 - 분석자(PARSER)는 SQL문을 분석합니다. (PARSING)

 - 옵티마이저는 비용기반의 옵티마이저 (COST BASED OPTIMIZER, CBO) 혹은 규칙기반의 옵티마이저 (RULE-BASED OPTIMIZER, RBO)중 최고의 효율을 보여주는 방법중 하나를 선택합니다.

 - RSG(Row Source Generator)는 옵티마이저에게 최적화된 계획을 받고 SQL 실행을 위한 최적화된 실행 계획을 출력합니다.

 - SQL 실행 엔진(SQL Execution Engine)은 SQL문을 실행계획에 따라 실행하고 쿼리의 결과를 생산합니다.

<Picture : SQL Processing Overview : www.oracle.com>

OVERVIEW OF THE OPTIMIZER
 옵티마이저는 SQL 문에 있는 특정한 조건과 참조되는 오브젝트들에 대한 요소들을 분석하고 SQL 문을 가장 효율적으로 수행할 수 있는 방법을 선택합니다. 이러한 선택은 SQL을 실행하는 과정에서 굉장히 중요한 부분이며, SQL을 실항하는 시간에 지대한 영향을 끼칩니다.

 

 SQL은 다음과 같은 여러가지 방법으로 수행될 수 있습니다.

 

 - Full table scans

 - Index scans

 - Nested loops

 - Hash joins

 

 옵티마저에서 생성한 출력물은 쿼리 수행에 있어 가장 최적화된 방법을 설명합니다. 오라클 서버는 비용기반(Cost-based)과 규칙기반(Rule-based)의 최적화 방식을 제공합니다. 일반적으로 최종 목표 데이터에 접근하는데는 비용기반 방식이 사용됩니다.

 

 사용자는 옵티마이저를 세팅하거나 STATISTICS를 수집하여 데이터에 접근하는 방법을 조절할 수 있습니다.

 떄떄로 어플리케이션의 데이터에 대한 충분한 정보를 가지고 있는 어플리케이션 디자이너들은 SQL 문을 수행하는데 좀더 효과적인 경로를 직접 선택할 수 있습니다. 어플리케이션 디자이너는 SQL 문에 HINT 절을 이용하여 그 SQL문이 수행하는데 필요한 정보를 제공할 수 있습니다.

 

FEATURES THAT REQUIRE THE CBO
 다음은 CBO(Cost-Base Optimze)를 수행하는데 필요한 조건들 입니다.

 - Partitioned Table and Index
 - IOT (Index Organized Table)

 - Reverse Key Index

 - Function-based Index

 - SAMPLE clause in a SELECT statement

 - Parallel query and parallel DML

 - Star transformations and star joins

 - Extensible Optimizer

 - Query rewirte with materialized views

 - Enterprise Manager progress meter

 - Hash Joins

 - Bitmap indexes and bitmap join indexes

 - Index skip scans

 OPTIMIZER_MODE 가 RULE로 되어 있다고 해도 위 사항중 하나라도 해당이 되면 CBO로 실행이 됩니다.

OPTIMIZER OPERATIONS
 오라클에 의해 수행되는 모든 SQL문은 옵티마이저에 의해 다음을 수행 합니다.  

---

  Evaluation of expressions and conditions

      옵티마이저는 첫째로 가능한한 모든 조건들과 값들을 평가합니다.
  Statements transformation
      복잡한 구문을 포함한 쿼리 - 서브쿼리 혹은 뷰 (예) -  들은 Join 을 이용한 구문으로 변환합니다.
  Choice of optimizer approches
      옵티마이저는 CBO와 RBO 중 하나를 선택하고 최적화 목표를 결정합니다.
  Choice of access path
      옵티마이저는 테이블 데이터를 얻기 위한 하나 혹은 그 이상의 접근 경로를 결정합니다.
  Choice of join orders
      두 테이블 이상이 결합된 Join 문에서 옵티마이저는 우선 몇개의 테이블을 Join 될 지결정하고,

      결과에 몇개의 테이블이 Join 될 지를 결정합니다.

  Choice of join methods
      어던 구문에서도 Join을 수행하기 위한 동작을 결정합니다.

---

       
 

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