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

Is null사용

인덱스에서는 null 값을 저장하지 않는다.

따라서 단일 컬럼 인덱스에서는 null, 연산자 is null, is not null을 사용하면 인덱스 범위 스캔을 사용할 수 없다.

 

null 값을 요구 할 때 인덱스 사용 예시

##postal _code의 인덱스 생성
create index idx_postal_code on orders(postal_code);

explain plan for
select /*+index(orders idx_postal_code)*/ postal_code from orders where postal_code is null;

Plan hash value: 1275100350
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 41296 | 82592 |   479   (1)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| ORDERS | 41296 | 82592 |   479   (1)| 00:00:06 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("POSTAL_CODE" IS NULL)
   
   
#null값 출력 시 인덱스 타게 하는 쿼리문
##nvl을 이용한 인덱스 생성
create index idx_nvl_postal_code on orders(nvl(postal_code, 0));

explain plan for
select /*+index(orders idx_postal_code)*/ postal_code from orders where nvl(postal_code,0) =0; 

Plan hash value: 188411269
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |   513 |  7695 |    47   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS              |   513 |  7695 |    47   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_NVL_POSTAL_CODE |   205 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(NVL("POSTAL_CODE",0)=0)

 

 

Is null,  Is not null 의 사용 - 결합 인덱스★★★★

결합 컬럼 인덱스는 모든 컬럼에 null 이 저장된 경우가 아니면 index range scan이 가능한 경우가 있다.

구성 컬럼 중 하나라도 null이 아닌 레코드는 인덱스에 저장된다.

예)

인덱스에는 널이 저장되지 않는다고 하지만, 이는 단일 인덱스의 경우이며, 결합 인덱스는 다르다.
결합인덱스의 경우엔 모든 항목이 null 이 아닐 경우 인덱스에 저장된다.
인덱스에 저장된는 값들은 정렬되어 저장이 되는데, 이 때 null 값의 위치가 맨 마지막에 정렬된다.
예를 들어 두개 항목 결합인덱스에 데이터 값이 다음과 같다면
1, 1
1, 2
1, Null
Null, 1
Null, 2
Null, Null
두개 항목이 모두 널인 행은 인덱스에 저장되지 않고 나머지 5개 행만 저장되는데
저장 순서는 다음과 같이 널이 마지막에 정렬된다.
1, 1
1, 2
1, Null
Null, 1
Null, 2