Optimizing Oracle Optimizer (2009년)
목록
Index Fase Full Scan 0 0 98,911

by 구루비스터디 Index Fase Full Scan [2018.07.14]

Index Fast Full Scan

  • Index Fast Full Scan 은 Table Full Scan의 Index Version 이라고 할 수 있다.
  • Table Full Scan 이 Table Segment 를 처음부터 끝(HWM) 까지 읽는 Operation 이라면, Index Fast Full Scan 은 Index Segment 를 처름부터 끝(HW)까지 읽으면서 그 중에서 Leaf Block 에 존재하는 Key 값을 Operation 이다.
  • Root / Branch / Leaf 모두 다 읽는다는 의미 - 정렬 안해서..


Index Fast Full Scan 의 특징을 한마디로 표현하면
  • "Index Key 를 모두 읽는 Operation 중 가장 효율적인 Operation" 이다.


Why ? 내 추측
  • Index Full Scan - 정렬해서 Ordering 작업 여기갔다가 저기 갔다가...- Single I/O
  • Index Fast Full Scan - 그냥 해당 Index Segment 에 해당하는 Block 을 쭉~욱 읽는다. - Multi Block I/O
  • Oracle 자신도 Index Key 를 모두 읽는 목적으로 Index Fast Full Scan 을 사용한다.
  • 가령 Index를 Rebuild(alter Index .. rebuild)하는 경우, Oracle 은 원본 Index 를 Index Fast Full Scan 으로 읽어 들이면서 새롭게 Index를 재구축한다.


Index Full Scan VS Index Fast Full Scan


Index FullScanIndex Fast Full Scan
I/O 방식Single Block I/OMulti Block I/O
정렬정렬 보장정렬 안됨
속도느림빠름
병렬읽기지원안됨지원됨


  • Index Fast Full Scan 이 Table Full Scan 과 동일한 방식의 Multi Block I/O 를 사용한다는 것이 핵심이다.
  • Multi Block I/O 를 사용하기 때문에 정렬이 보장이 안되고, 그 만큼 속도는 빠르다. 자연스럽게도 Parallel Query 도 지원된다.


  • Index Range Scan / Index Full Scan 은 기본적으로 Parallel Scan 이 지원되지 않는다.
  • 단. Partition Index에 대해 PARALLEL_INDEX Hint를 사용하는 경우에는 Rarallel Range Scan 이 가능하다.


Parallel Index Fast Full Scan


SYS>drop table t1 purge ;

Table dropped.

SYS>create table t1 (c1 int, c2 int)
        partition by hash (c1)
        partitions 4 ;

Table created.

SYS>create index t1_n1 on t1(c1) local ;

Index created.

SYS>insert into t1 select level, level from dual connect by level <= 10000 ;
10000 rows created.

SYS>commit ;
Commit complete.

SYS>@gather t1
PL/SQL procedure successfully completed.

SYS>explain plan for select /\* index_ffs(t1) \*/ c1 from t1 where c1 > 0 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
\------------------------------------------------------------------------------------------------\-
Plan hash value: 2434397737

\----------------------------------------------------------------------------------------------\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
\----------------------------------------------------------------------------------------------\-
| 0 | SELECT STATEMENT | | 10000 | 40000 | 6 (0) | 00:00:01 | | |
| 1 | PARTITION HASH ALL | | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 |
| * 2 | INDEX FAST FULL SCAN | T1_N1 | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 |
\----------------------------------------------------------------------------------------------\-

\--> Pstart / Pstop 해석을 어떻게 함 ㅇㅇ? 파티션 1에서 4까지 ?

Predicate Information (identified by operation id):
\--------------------------------------------------\-

2 - filter("C1">0)

14 rows selected.

SYS> select /*\+ gahter_plan_statistics index_ffs(t1) \*/ c1 from t1 where c1 > 0
10000 rows selected.
==> 실제 리턴되는 결과는 정렬되지 않고 Return 된다.

SYS>@stat

PLAN_TABLE_OUTPUT
\--------------------------------------------------------------------------------\-
SQL_ID 3tjq2pqgay4z0, child number 0
\------------------------------------\-
select /*\+ gahter_plan_statistics index_ffs(t1) \*/ c1 from t1 where c1
> 0

Plan hash value: 2434397737

\------------------------------------------------------------\-
| Id | Operation | Name | E-Rows | Cost (%CPU) |
\------------------------------------------------------------\-
| 1 | PARTITION HASH ALL | | 10000 | 6 (0) |
| * 2 | INDEX FAST FULL SCAN | T1_N1 | 10000 | 6 (0) |
\------------------------------------------------------------\-

Predicate Information (identified by operation id):
\--------------------------------------------------\-

2 - filter("C1">0)

Note
- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

25 rows selected.


  • Paralle Index Fast Full Scan 을 수행하려면 어떻게 해야 할까 ?
  • 다음과 같이 PARALLE Hint를 부여하면 ?
  • 실망스럽게도 Parallel Execution 이 선택되지 않는다.
  • 이유는 간단하다. PARALLEL Hint 는 Table 에 대해서만 적용되기 때문이다.
  • Index Fast Full Scan 을 Paralle Execution 으로 전환하는 방법은 Index Object 에 PARALLEL 속성을 부여하거나 PARALLEL_INDEX Hint 를 사용하는 것이다.




SYS>explain plan for select /*\+ index_ffs(t1) parallel(t1) \*/ c1 from t1 where c1 > 0 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
\----------------------------------------------------------------------------------------------\-
Plan hash value: 2434397737

\----------------------------------------------------------------------------------------------\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop |
\----------------------------------------------------------------------------------------------\-
| 0 | SELECT STATEMENT | | 10000 | 40000 | 6 (0) | 00:00:01 | | |
| 1 | PARTITION HASH ALL | | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 |
| * 2 | INDEX FAST FULL SCAN | T1_N1 | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 |
\----------------------------------------------------------------------------------------------\-

Predicate Information (identified by operation id):
\--------------------------------------------------\-

2 - filter("C1">0)

14 rows selected.

SYS>alter index t1_n1 parallel ;

Index altered.

SYS>explain plan for select /*\+ index_ffs(t1) \*/ c1 from t1 where c1 > 0 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
\--------------------------------------------------------------------------------------------------------------------------------\-
Plan hash value: 3351752447

\--------------------------------------------------------------------------------------------------------------------------------\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop | TQ | IN-OUT | PQ Distrib |
\--------------------------------------------------------------------------------------------------------------------------------\-
| 0 | SELECT STATEMENT | | 10000 | 40000 | 6 (0) | 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 40000 | 6 (0) | 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 | Q1,00 | PCWC | |
| * 4 | INDEX FAST FULL SCAN | T1_N1 | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 | Q1,00 | PCWP | |
\--------------------------------------------------------------------------------------------------------------------------------\-

Predicate Information (identified by operation id):
\--------------------------------------------------\-

4 - filter("C1">0)

16 rows selected.


PARALLEL_INDEX Hint 사용해서 Index Parallel 처리

SQL> explain plan for select /*+ index_ffs(t1) parallel_index(t1 t1_n1) */ c1 from t1   where c1 > 0 ;
Explained.

SQL> @plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 10000 | 40000 |     6   (0)|       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 | 10000 | 40000 |     6   (0)|       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |          | 10000 | 40000 |     6   (0)|     1 |     4 |  Q1,00 | PCWC |            |
|*  4 |     INDEX FAST FULL SCAN| T1_N1    | 10000 | 40000 |     6   (0)|     1 |     4 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("C1">0)

Note
-----
   - 'PLAN_TABLE' is old version

19 rows selected.


3. Access SubQuery

  • Query 변형이 없음
  • 흔히 말하는 제공자 SubQuery임
  • SubQuery 부터 풀려서 MainQuery 에 값이 제공된다.


4. Filter SubQuery

  • Query 의 변형이 없음
  • 흔히 말하는 확인자 SubQuery임
  • Main Query 의 값을 제공받아 SubQuery 에서 Check 하는 방식임
    =* => Main Query 가 먼저 수행된 값을 받아 Checkg 하는 쿼리일때는 Filter
  • 근데 테스트 후반에 가면 2개가 같이 나오는 경우가 많다.
  • 무슨 의미일까 ?




SYS>explain plan for select /*\+ index_ffs(t1) parallel_index(t1 t1_n1) \*/ c1 from t1 where c1 > 0 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
\-------------------------------------------------------------------------------------------------------------------------------\-
Plan hash value: 3351752447

\--------------------------------------------------------------------------------------------------------------------------------\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart | Pstop | TQ | IN-OUT | PQ Distrib |
\--------------------------------------------------------------------------------------------------------------------------------\-
| 0 | SELECT STATEMENT | | 10000 | 40000 | 6 (0) | 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 40000 | 6 (0) | 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 | Q1,00 | PCWC | |
| * 4 | INDEX FAST FULL SCAN | T1_N1 | 10000 | 40000 | 6 (0) | 00:00:01 | 1 | 4 | Q1,00 | PCWP | |
\--------------------------------------------------------------------------------------------------------------------------------\-

Predicate Information (identified by operation id):
\--------------------------------------------------\-

4 - filter("C1">0)

16 rows selected.


Hints and Parameters

  • Index Fast Full Scan 은 INDEX_FFS Hint 를 이용해 제어 한다.


select /*\+ index_ffs(t1) \*/ c1 from t1 ;
select /*\+ index_ffs(t1 t1_n1) \*/ c1 from t1 ;
select /*\+ index_ffs(t1 t1(c1)) \*/ c1 from t1;


  • Index Fast Full Scan의 동작 여부는 \_FAST_FULL_SCAN_ENABLED Parameter 를 통해 제어 가능하다.


SYS>@check_hidden_parameter.sql
Enter value for input_parameter: \_fast_full_scan_enabled
old 15: a.ksppinm LIKE '&input_parameter'
new 15: a.ksppinm LIKE '_fast_full_scan_enabled'

Parameter Session Value Instance Value
\-----------------------------------------------------------\- \-----------------------------\- \-----------------------------\-
\_fast_full_scan_enabled TRUE TRUE


_FAST_FULL_SCAN_ENABLED
  • 이 값이 TRUE일 경우 Index에 대한 DB_FILE_MULTIBLOCK_READ_COUNT를 통한 Full Scan을 가능하도록 한다.
  • 단 Index의 해당 Column에는 Not Null과 해당 Column이 모두 Index로 구성이 되어야 한다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3863

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입