INDEX RANGE SCAN 과 FULL TABLE SCAN

INDEX RANGE SCAN 과 FULL TABLE SCAN

: FULL TABLE SCAN은 테이블의 블록들을 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터에 지정된 개수 단위로 읽음
: INDEX RANGE SCAN은 블록 단위로 읽음
: 그렇다면 언제나 FULL TABLE SCAN이 유리한 것인가? 그렇지 않다.
  예를 들어, DB_FILE_MULTIBLOCK_READ_COUNT가 16이라고 가정하고, 16개의 블록을 읽어야 한다면
  INDEX SCAN과 같은 Single Block I/O는 총 16회, FULL TABLE SCAN과 같은 Multi Block I/O는 1회 소요
  하지만, Single Block I/O의 1회당 소요 시간과 Multi Block I/O의 1회당 소요 시간은 동일하지 않기 때문에 적절한 균형이 필요하다.

 

 

 

 

ex) 테이블에서 매우 적은 행들을 검색하는 경우

SELECT count(*) FROM orders; -- 90만건

CREATE INDEX orders_custno_idx ON orders(custno);

SELECT /*+ gather_plan_statistics FULL(orders) */ MAX(orderdate)
FROM orders
WHERE custno BETWEEN 1 AND 100;

 

[PLAN]

SQL_ID  396x0udd337r9, child number 0
-------------------------------------
  SELECT /*+ gather_plan_statistics FULL(orders) */ MAX(orderdate)     
FROM orders     WHERE custno BETWEEN 1 AND 100
 
Plan hash value: 630573765
 
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |       | 52584 (100)|          |      1 |00:00:04.85 |     300K|    300K|
|   1 |  SORT AGGREGATE    |        |      1 |      1 |    12 |            |          |      1 |00:00:04.85 |     300K|    300K|
|*  2 |   TABLE ACCESS FULL| ORDERS |      1 |   9303 |   109K| 52584   (1)| 00:00:03 |   8811 |00:00:05.06 |     300K|    300K|
--------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("CUSTNO"<=100 AND "CUSTNO">=1))

[PLAN 설명]

[Id=2], 30만 블록을 읽음  300K = 300,000

 

 

 

## 인덱스를 탈 경우

SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) */ 
MAX(orderdate)
FROM orders
WHERE custno BETWEEN 1 AND 100;

 

[PLAN]

SQL_ID  3qcqn25arj985, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) */ 
MAX(orderdate)     FROM orders     WHERE custno BETWEEN 1 AND 100
 
Plan hash value: 2339385844
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |       |  9329 (100)|          |      1 |00:00:00.02 |    8831 |
|   1 |  SORT AGGREGATE                      |                   |      1 |      1 |    12 |            |          |      1 |00:00:00.02 |    8831 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |      1 |   9303 |   109K|  9329   (1)| 00:00:01 |   8811 |00:00:00.04 |    8831 |
|*  3 |    INDEX RANGE SCAN                  | ORDERS_CUSTNO_IDX |      1 |   9303 |       |    22   (0)| 00:00:01 |   8811 |00:00:00.01 |      20 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CUSTNO">=1 AND "CUSTNO"<=100)

[PLAN 설명]

[Id=2] 인덱스 블록과 테이블 블록 모두 합쳐서 8831 블록 읽음

 

 

 

## 읽는 조건을 크게 할 경우 - INDEX 사용,

총 90만 건중, 45만건이 인덱스에서 추출될 경우

 

SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) */

MAX(orderdate)
  FROM orders
 WHERE custno BETWEEN 1 AND 5000;

 

[PLAN]

SQL_ID  f3k0b9akv1qf2, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) 
*/MAX(orderdate)     FROM orders    WHERE custno BETWEEN 1 AND 5000
 
Plan hash value: 2339385844
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |       |   451K(100)|          |      1 |00:00:00.86 |     449K|
|   1 |  SORT AGGREGATE                      |                   |      1 |      1 |    12 |            |          |      1 |00:00:00.86 |     449K|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |      1 |    449K|  5273K|   451K  (1)| 00:00:18 |    448K|00:00:01.00 |     449K|
|*  3 |    INDEX RANGE SCAN                  | ORDERS_CUSTNO_IDX |      1 |    449K|       |   950   (1)| 00:00:01 |    448K|00:00:00.07 |     939 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CUSTNO">=1 AND "CUSTNO"<=5000)

[PLAN 설명]

[Id=1] 인덱스 블록 + 테이블 블록 45만 블록을 읽음

 

 

## 읽는 조건을 크게 할 경우 - INDEX 사용,

총 90만 건중, 9 만건이 인덱스에서 추출될 경우

 

SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) */
  MAX(orderdate)
  FROM orders
 WHERE custno BETWEEN 1 AND 1000 ;

 

[PLAN]

SQL_ID  14zr0m38a93cz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX(orders orders_custno_idx) */ 
MAX(orderdate)       FROM orders       WHERE custno BETWEEN 1 AND 1000
 
Plan hash value: 2339385844
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |       | 92181 (100)|          |      1 |00:00:00.17 |   90226 |
|   1 |  SORT AGGREGATE                      |                   |      1 |      1 |    12 |            |          |      1 |00:00:00.17 |   90226 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS            |      1 |  91941 |  1077K| 92181   (1)| 00:00:04 |  90045 |00:00:00.19 |   90226 |
|*  3 |    INDEX RANGE SCAN                  | ORDERS_CUSTNO_IDX |      1 |  91941 |       |   197   (2)| 00:00:01 |  90045 |00:00:00.01 |     189 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CUSTNO">=1 AND "CUSTNO"<=1000)

[PLAN 설명]

[Id=1] 인덱스 블록 + 테이블 블록 9만 블록을 읽음  

 

 

 

 

 

##  ORDERS 테이블 블록 수 확인

SELECT OWNER, TABLE_NAME, BLOCKS FROM DBA_TABLES
WHERE TABLE_NAME ='ORDERS';

 

OWNER	TABLE_NAME	BLOCKS
TUNING	ORDERS	302559

인덱스를 이용해서 읽은 인덱스 블록과 테이블 블록의 개수가 테이블에 할당된 전체 블록 수를 초과한다면 차라리 FULL TABLE SCAN이 유리하다.

 

 

테이블의 전체 건수에서 약 5% 이내의 행을 조회하는 경우, 인덱스가 유리하다는 말이 있다. 

하지만 문건에 따라 다르므로 실제로 돌려보고 판단을 권장한다.