INDEX RANGE SCAN DESCENDING

INDEX RANGE SCAN DESCENDING
: INDEX RANGE SCAN에서는 조건절의 범위를 검색 할 때, 기본적으로 최소 경계값부터 검색을 시작하여 최대 경계값에서 검색을 종료
: 만약, 최대 경계값에서 검색을 시작하여 최소 경계값에서 검색을 종료해야하는 경우에 사용

 

 

 

[INDEX RANGE SCAN DESCENDING B-Tree 구조]

 

[실습]

SELECT /*+ INDEX_DESC(products products_price_idx) */ *
FROM products
WHERE price BETWEEN 3350 AND 4500;

 

* 힌트 설명

   1) INDEX_DESC : 인덱스를 내림차순으로 검색

   2) INDEX_ASC, INDEX : 인덱스를 오름차순으로 검색

 

 

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

 

 

[PLAN]

SQL_ID  a8upwq0gc4qvg, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX_DESC(products 
products_price_idx) */ *  FROM products  WHERE price BETWEEN 3350 AND 
4500
 
Plan hash value: 3684199221
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| 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 DESCENDING       | 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"<=4500 AND "PRICE">=3350)
       filter(("PRICE">=3350 AND "PRICE"<=4500))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   1 -  SEL$1 / PRODUCTS@SEL$1
         U -  INDEX_DESC(products products_price_idx)

 

 

 

* INDEX RANGE SCAN DESCENDING을 언제 사용해야할까?

 

1. 정렬 작업의 회피 (정렬 작업에 사용되는 메모리)

 

SELECT /*+ gather_plan_statistics INDEX_DESC(products products_price_idx) */ *
FROM products
WHERE price BETWEEN 3350 AND 4500
order by PRODNO desc;

 

 

- PLAN에서 OMem, 1Mem, Used-Mem 보이게 하기 위해 아래와 같은 파라미터를 수정하였다. 

[용어 설명 ]

0Mem  = PGA를 사용하는 작업(Sort Merge Join, Hash Join 등)이 메모리에서 모두 처리(OPTIMAL PASS) 되었을 때, 예상되는 PGA 사용량
1Mem = PGA를 사용하는 작업이 PGA 메모리에서 한번에 처리되지 못하고, 디스크를 한번 사용(ONE PASS)했을 때, 예상되는 PGA 사용량
Used-Mem = PGA를 사용하는 작업에서 실제 사용된 PGA 사용량. OPTIMAL PASS이면 (0), ONE PASS이면 (1), MULTI PASS이면 (2)가 표시

 

 

 

 

1) tuning 세션

alter session set sort_area_size=0;

 - sort 작업 공간을 0으로 설정하여, sort 시 무조건 메모리를 사용하도록 한다. 

 

2) sys 세션

- 메모리 초기화, 한번 실행된 SQL은 메모리에 저장되므로 아래의 명령어를 통해 초기화 한다. 

alter system flush buffer_cache;
alter system flush shared_pool;

 * 운영 서비스에 해당 SQL 실행할 경우 모든 SQL의 실행계획 및 저장된 버퍼블록이 사라지므로 장애가 발생할 수 있다. 

 

[PLAN]

SQL_ID  813gd2mhasu8x, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics INDEX_DESC(products 
products_price_idx) */ *  FROM products  WHERE price BETWEEN 3350 AND 
4500 order by PRODNO desc
 
Plan hash value: 753726603
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |      1 |        |       |    25 (100)|          |     22 |00:00:00.01 |      24 |       |       |          |
|   1 |  SORT ORDER BY                       |                    |      1 |     22 |   440 |    25   (4)| 00:00:01 |     22 |00:00:00.01 |      24 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS           |      1 |     22 |   440 |    24   (0)| 00:00:01 |     22 |00:00:00.01 |      24 |       |       |          |
|*  3 |    INDEX RANGE SCAN DESCENDING       | PRODUCTS_PRICE_IDX |      1 |     22 |       |     2   (0)| 00:00:01 |     22 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("PRICE"<=4500 AND "PRICE">=3350)
       filter(("PRICE">=3350 AND "PRICE"<=4500))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   2 -  SEL$1 / PRODUCTS@SEL$1
         U -  INDEX_DESC(products products_price_idx)

[설명]

[Id=1] SORT ORDER BY,  Used-Mem=2048, 정렬 작업에 사용된 메모리