B-Tree 인덱스 구조

B-Tree 인덱스 구조

 

루트 블록 : 브랜치 블록의 블록 주소, 인덱스 컬럼 값으로 구성

브렌치 블록 : 리프 블록의 블록 주소, 인덱스 컬럼 값으로 구성

리프 블록 : 인덱스 컬럼, 테이블 ROWID로 구성

                인덱스 컬럼으로 정렬되어 있으며, 컬럼 값이 같으면 ROWID 로 정렬

 

CREATE INDEX products_price_idx ON products(price);   --  products 테이블의 price 컬럼에 비고유 인덱스 생성

1. products 테이블 전체를 읽고, price로 오름차순 정렬한다.
2. 정렬된 price 값과 해당 값이 포함된 행의 ROWID를 첫 번째 리프 블록부터 채워 넣는다.
3. 리프 블록이 완성되면, 각 리프 블록의 첫 번째 price 값과 해당 리프 블록의 주소를 첫 번째 브랜치 블록부터 채워 넣는다.
4. 브랜치 블록이 완성되면, 각 브랜치 블록의 첫 번째 price 값과 해당 브랜치 블록의 주소를 루트 블록에 채워 넣는다.

 

 

products_price_idx B-Tree 인덱스 구조

 

 

실습)

SELECT *
FROM products
WHERE price = 3350;

1. 루트 블록의 첫 번째 price 값부터 차례로 3350과 비교한다.
2. 3350보다 작은 값들 중에 가장 큰 값(3100)을 찾고, 해당 블록 주소(90000)를 읽는다.
3. 90000번 블록을 읽는다.
4. 90000번 브랜치 블록의 첫 번째 price 값부터 차례로 3350과 비교한다.
5. 3350보다 작은 값들 중에 가장 큰 값(3300)을 찾고, 해당 블록 주소(30000)를 읽는다.
6. 30000번 블록을 읽는다.
7. 30000번 리프 블록의 첫 번째 price 값부터 차례로 3350을 검색한다.
8. price가 3350인 항목이 발견되면, ROWID를 읽는다.
9. ROWID를 이용하여 해당 행이 저장되어 있는 테이블 블록을 읽는다.

 

 

 

 

SELECT /*+ gather_plan_statistics */ *
FROM products
WHERE price = 3350;

 

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

 

[PLAN]

SQL_ID  7dqsdht7bwp3y, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM products WHERE price = 3350
 
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 |        |       |     3 (100)|          |      2 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS           |      1 |      2 |    40 |     3   (0)| 00:00:01 |      2 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN                  | PRODUCTS_PRICE_IDX |      1 |      2 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PRICE"=3350)

 

설명, 

[ID = 2]  -->>  PRODUCTS_PRICE_IDX 인덱스에서 Buffers 3개의 블록을 읽음

[ID = 3]  -->>  테이블에서 2개의 블록을 읽음 (=5-3) 

 

 

옵티마이저의 판단은 INDEX RANGE SCAN 을 타도록 유도하고 있다.

 -> 결론적으로는 힌트를 지정할 필요 없이, 해당 SQL 의 구문의 경우 인덱스를 타게 된다. 

 -> 오라클에서는 힌트의 사용을 권장하지 않는다. 

     하지만, 바인드 변수를 사용하거나 통계정보가 오래된 경우에는 옵티마이저가 올바른 판단을 내리기 쉽지 않으므로 이와 같은 경우에는 올바른 힌트를 사용하는것을 추천한다.