INDEX UNIQUE SCAN

INDEX UNIQUE SCAN

 : 고유 인덱스가 정의된 컬럼이 조건절에서 '=' 로 비교되는 경우

 그 외의 경우는 전부 INDEX RANGE SCAN 발생

 

 

[실습]

SELECT /*+ gather_plan_statistics */ *
FROM products
WHERE prodno = 11000; 

 

-- PRODUCTS_PK : PRODNO   

 

 

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

 

[PLAN]

SQL_ID  g2gb0h3jzw95t, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ *  FROM products  WHERE prodno = 
11000
 
Plan hash value: 1005976121
 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PRODUCTS    |      1 |      1 |    20 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PRODUCTS_PK |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PRODNO"=11000)

[설명]

[Id=2]

    Buffers = 2, 리프블록 1개, 루트블록 1개,               

    A-Raws = 1, 1회의 랜덤 블록 액세스(Single Block I/O)

 

[Id=1]

    Buffers = 테이블 블록 1개 (=3-2)