INDEX RANGE SCAN

INDEX RANGE SCAN
: INDEX UNIQUE SCAN을 제외한 모든 INDEX SCAN은 INDEX RANGE SCAN 이다.
: 고유 인덱스가 정의된 컬럼이 조건절에서 '=' 비교 연산자를 제외한 모든 연산자로 비교되는 경우
: 비고유 인덱스가 정의된 컬럼이 조건절에 기술되는 경우

 

[INDEX RANGE SCAN B-Tree 구조]

 

[실습]

 

SELECT /*+ gather_plan_statistics */ *
FROM products
WHERE PRICE BETWEEN 3350 AND 4500;

 

 

 

 

 select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST'));

 

[PLAN]

SQL_ID  8ssw9b2n9hmsx, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM products WHERE PRICE 
BETWEEN 3350 AND 4500
 
Plan hash value: 1668926985
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |       |    24 (100)|          |     22 |00:00:00.01 |      25 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS           |      1 |     22 |   440 |    24   (0)| 00:00:01 |     22 |00:00:00.01 |      25 |
|*  2 |   INDEX RANGE SCAN                  | PRODUCTS_PRICE_IDX |      1 |     22 |       |     2   (0)| 00:00:01 |     22 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PRICE">=3350 AND "PRICE"<=4500)

[설명]

[Id=2] 

   A-Rows = 22, 22번의 Single Block I/O 발생

   Buffers = 3, 인덱스에서 3개의 블록을 읽고 22개의 인덱스 항목을 검색

          (Single Block I/O 1회당 테이블 블록 1개를 읽음)

          (만약 위의 값과 차이가 나는 경우는 클러스터링 팩터와 연관이 있음) 

 

[Id=1]

  Buffers = 25, 22(=25-3)개의 테이블 블록을 읽음