인덱스를 사용하여 NULL값 찾기

며칠 전에 gurubee에서 오라클 데이터베이스의 인덱스에 상수값을 넣는 방식에 대한 질문글이 올라와 찾아본 내용을 정리해보려고 합니다. 몇개의 블로그 글과 오라클 매뉴얼에서 이와 관련된 재미있는 내용을 발견했습니다.

오라클은 인덱스의 키 값이 모두 NULL인 row에 대해서는 저장하지 않는다는 것입니다.

 

Oracle Database does not store in the index any rows where all the keys are NULL. 

docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm

 

이런 특성으로 인해 WHERE 조건절에 IS NULL을 사용할 경우 테이블 스캔이 발생할 수 있습니다. 이런 경우를 회피하기 위한 방법이 몇가지 있습니다.

 

먼저 NVL, COALESCE 함수를 사용한 함수 기반 인덱스를 사용하는 방법이 있고,

앞서 이야기한 인덱스에 상수값을 추가하는 방법이 있다고 합니다.

아래 Richard Foote의 블로그 글의 내용으로 테스트한 내용입니다. 먼저 인덱스 키 값이 모두 NULL인 row를 조회했을 때의 실행계획을 보겠습니다.

SQL> create table bowie (id number, code number, grade number, name varchar2(42));

Table created.

SQL> insert into bowie select rownum, mod(rownum,100), mod(rownum,1000), 'DAVID BOWIE' from dual connect by level < 1000000;

999999 rows created.

SQL> insert into bowie values (1000000, null, null, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

SQL> create index bowie_code_grade_i on bowie(code, grade);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BOWIE');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from bowie where code is null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4157583811

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    24 |  1115   (3)| 00:00:14 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     1 |    24 |  1115   (3)| 00:00:14 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("CODE" IS NULL)

13 rows selected.

인덱스가 있지만 키 값이 모두 NULL인 row에 대해서는 테이블 스캔이 발생하는 것을 확인할 수 있습니다.

그래서 이를 해결하기 위한 방안으로 기존 인덱스의 키 값에 상수값을 추가한 인덱스를 사용했을 때는 어떨까요?

SQL> create index bowie_code_grade_i_2 on bowie (code, grade, 1);

Index created.

SQL> explain plan for select * from bowie where code is null;

Explained.

SQL> select * from table (dbms_xplan.display('plan_table',null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3311524652

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    24 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOWIE                |     1 |    24 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BOWIE_CODE_GRADE_I_2 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("CODE" IS NULL)

동일한 인덱스에 상수값을 추가했더니 키 값이 모두 NULL인 row를 찾을 때 인덱스를 사용하는 것을 알 수 있습니다.

상수값 대신 스페이스(' ')를 사용하면 스토리지 용량을 절약할 수 있다는 팁도 있군요.

 

https://richardfoote.wordpress.com/category/indexing-nulls/

https://use-the-index-luke.com/sql/where-clause/null/index

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm

blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database