[SQLP] 인덱스 성능 저하 요인 ★★★★★

인덱스 사용 시 SQL 명령문은 성능을 저하시킬 수 있다.

1) 인덱스 컬럼에 <> 연산자의 사용(부정형 조건)

2) 인덱스 컬럼에 SUBSTR 연산자의 사용

3)인덱스 컬럼에 산술 연산자(+,-,/,*) 사용
4)인덱스 컬럼에 TRUNC 연산자의 사용

5) 인덱스 컬럼에 || 연산자의 사용

6) 인덱스 컬럼에 데이터 타입의 변환

7) 인덱스 컬럼에 is null /is not null 연산자의 사용

==> 인덱스 컬럼의 가공할 경우에 성능 저하 요인이 된다.

 

* 인덱스 구성 컬럼 : 어디에 써야지 인덱스를 사용이 가능한가??

   : where 절에 들어가야 사용이 가능하다.

단일 컬럼 인덱스 : 해당 단일 컬럼이 WHERE 절에서 사용

결합 인덱스: 결합 인덱스의 선두 컬림이 WHERE 절에서 사용  

 

실습) 인덱스 생성
ORDERS 의 인덱스가 ROW_ID, SHIP_MODE 에 대해서 생성되어 있는 상황이다.

#인덱스 생성
CREATE INDEX IDX_ORDERS_ROW_ID ON ORDERS(ROW_ID);
CREATE INDEX IDX_ORDERS_SHIP_MODE ON ORDERS(SHIP_MODE);

Q. ORDERS 테이블에서 SHIP_MODE 가 'first Class' 인 행들의 SHIP_MODE 를 출력해본다
(힌트로 인덱스의 사용을 강제한다.)

EXPLAIN plan for
SELECT SHIP_MODE FROM ORDERS
WHERE SHIP_MODE = 'First Class';

select * from table(DBMs_XPLAN.display);

Plan hash value: 231799062
 
-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      | 12823 |   175K|    45   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ORDERS_SHIP_MODE | 12823 |   175K|    45   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("SHIP_MODE"='First Class')

 

Q. ORDERS 테이블에서 Segment 가 'Corporate' 인 행들을 출력한다. (힌트는 ship_mode 사용)

##힌트강제안했을때

EXPLAIN plan for
SELECT SHIP_MODE FROM ORDERS
WHERE segment = 'Corporate';

select * from table(DBMs_XPLAN.display);


Plan hash value: 1275100350
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 17097 |   400K|   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS | 17097 |   400K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SEGMENT"='Corporate')
   
   
   ##힌트강제했을때
EXPLAIN plan for
SELECT /*+index(INDEX IDX_ORDERS_SHIP_MODE)*/ SHIP_MODE FROM ORDERS
WHERE segment = 'Corporate';

select * from table(DBMs_XPLAN.display);

Plan hash value: 1275100350
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 17097 |   400K|   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS | 17097 |   400K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SEGMENT"='Corporate')
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   1 -  SEL$1
   
   
   ##인덱스를 Ship_mode 를 사용했으나, where 절에서 별도의 행을 조건으로 잡으면서 full scan 사용

 

 

Q. ORDERS 테이블에서 SHIP_MODE 가 'first Class' 인 행들의 모든 정보 를 출력해본다
(힌트로 인덱스의 사용을 강제한다.)

이런식으로 모든 행을 출력해야하는 경우 선택도가 높기 때문에(출력되는 행이 많다) table access full 을 사용한다.

EXPLAIN plan for
SELECT /*+index(orders IDX_ORDERS_SHIP_MODE)*/* FROM ORDERS
WHERE SHIP_MODE = 'First Class';

select * from table(DBMs_XPLAN.display);
Plan hash value: 1887598579
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      | 12823 |  2842K|  1604   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS               | 12823 |  2842K|  1604   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ORDERS_SHIP_MODE | 12823 |       |    45   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SHIP_MODE"='First Class')
   
   
   
   #힌트 강제안할 경우
EXPLAIN plan for
SELECT * FROM ORDERS
WHERE SHIP_MODE = 'First Class';


select * from table(DBMs_XPLAN.display);

Plan hash value: 1275100350
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 12823 |  2842K|   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ORDERS | 12823 |  2842K|   479   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SHIP_MODE"='First Class')

힌트 강제 안할 경우가 cost가 더 적게 발생하는 것을 알 수 있다!!

 

 

16:01