h2.Index Fast Full Scan

h5.Index Fast Full Scan vs Index Full Scan

위의 그림에서 알수있는거처럼 Index Fast Full 은 MultiBlock I/O를 사용한다는것이 한다는걸 알수있다..

h5.Parallel Index Fast Full Scan
Index Fast Full Scan의 큰장점중 하나는 Parallel Execution 이 가능하다는거다.

-- 테이블생성
SQL> create table t1(c1 int,c2 int)
  2  partition by hash(c1)
  3  partitions 4;

-- 인덱스생성
SQL> create index t1_n1 on t1(c1)
  2  local;

-- 데이터생성
SQL> insert into t1
  2  select level, level
  3  from dual
  4  connect by level <= 10000;

-- 통계정보수집
SQL> exec dbms_stats.gather_table_stats('bshman','t1',method_opt=>'for all columns size 1',cascade=>true);

-- 플랜
SQL> explain plan for
  2  select /*+ index_ffs(t1) */
  3  c1
  4  from t1
  5  where c1 >0;

----------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------

Parallel Hint를 부여하면?

-- 플랜
SQL> explain plan for
  2  select /*+ index_ffs(t1) parallel(t1) */
  3  c1
  4  from t1
  5  where c1 > 0;

----------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------
Parallel Hint를 주었음에도 불구하고 Parallel execution 이 선택되지않는다
이유는 Parallel Hint는 Table에서만 적용되기 때문이다.
Parallel Hint 를 사용하기위해서는 아래와 같이 Index Object에 Parallel 속성을 부여하거나
Parallel_Index Hint를 사용한다.

-- 인덱스변경
SQL> alter index t1_n1 parallel;

-- 플랜
SQL> explain plan for
  2  select /* index_ffs(t1) */
  3  c1 from t1
  4  where c1 > 0;
또는
SQL> explain plan for
  2  select /*+ index_ffs(t1) parallel_index(t1 t1_n1) */
  3  c1 from t1 where c1 > 0;

---------------------------------------------------------------------------------------------------------------------------------
| 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 |            |
--------------------------------------------------------------------------------------------------------------------------------

h5.Hints And Parameters

Index Fast Full Scan 의 동작여부는 Fast_Full_Scan_Enabled parameter를 이용하여 제어할수있다.