NULL in Oracle 오라클에서 NULL 이란...
NULL in Oracle 오라클에서 NULL 이란... 그리고 NULL 컬럼을 인덱스를 사용하여 빨리 찾기 까지..
오라클 DB를 사용하게 될때 테이블에서 NULL 값 비교이 IS NULL 또는 IS NOT NULL 을 이용해야 한다는 것은 대부분 잘 알고 있다.
그런데... NULL이 있을수있는 컬럼 값 비교시에 NULL에 대한 고려를 잊는 경우가 있다.
예를 들어 다음과 같은 상황을 보자
========
CREATE TABLE GOODIDEAS
(
IDNO VARCHAR2(15 BYTE),
TITLE VARCHAR2(256 BYTE),
OWNER_ID VARCHAR2(256 BYTE),
AREA_NM VARCHAR2(256 BYTE)
);
=======
=======
INSERT INTO GOODIDEAS VALUES (1,'제목1','김길동','서울');
INSERT INTO GOODIDEAS VALUES (2,'제목2','부길동','부산');
INSERT INTO GOODIDEAS VALUES (3,'제목3','강길동','강원');
INSERT INTO GOODIDEAS VALUES (4,'제목4','마길동','마산');
INSERT INTO GOODIDEAS VALUES (5,'제목5','모길동',NULL);
=======
이렇게 데이터를 넣고 지역(AREA_NM)이 서울인 사람을 조회한다면 다음과 같을것이다.
SELECT * FROM GOODIDEAS WHERE AREA_NM='서울';
======= 쿼리 결과 =======
IDNO TITLE OWNER_ID AREA_NM
1 제목1 김길동 서울
=========================
만일 반대로 서울이 아닌사람을 찾고자 할때는 어떻게 하면 될까..?
이렇게 하면 ?
SELECT * FROM GOODIDEAS WHERE AREA_NM<>'서울';
======= 쿼리 결과 =======
IDNO TITLE OWNER_ID AREA_NM
2 제목2 부길동 부산
3 제목3 강길동 강원
4 제목4 마길동 마산
=========================
만일 원하는 봐가 지역이 없는 사람은 제외한다는 의도라면 위의 결과가 맞겠지만.. 단지 '서울'이라고 되어있지 않은 사람 모두를 의미 하는 거라면? 문제가 있게 된다.
AREA_NM 컬럼이 NULL인 사람은 빠졌다는 것이다.... 뭔가 이상하지 않은가...? 서울이 아닌 (WHERE AREA_NM <> '서울') 이라고 했으므로 NULL 도 서울이 아니라는 사실을 알기때문에
위에 5번도 조회되어야 하는게 아닌가? 아니다...
다음을 조회해보자
SELECT * FROM dual WHERE NULL = NULL;
==== 쿼리 결과 =====
No rows selected.
No rows selected.
===================
그럼 반대는 ?
SELECT * FROM dual WHERE NULL <> NULL;
SELECT * FROM dual WHERE NULL <> NULL;
==== 쿼리 결과 =====
No rows selected.
No rows selected.
====================
NULL 은 비교자체가 안된다... ORACLE은 그냥 모른다이다라는 것이다. 그래서 NULL이 들어갈수있는 값에 대한 비교를 할때는 이점을 반드시 염두해 두어야 한다.
당연히 NULL 비교할때는 IS NULL 을 쓰거나 (이미 잘쓸것이다.. NULL을 찾을 경우)
위와 같이 아닌 케이스를 찾고 싶을때는 NVL 또는 NVL2를 사용해서 다음과 같이
SELECT * FROM GOODIDEAS WHERE NVL(AREA_NM,'모름')<>'서울';
하면 진짜 의도 하는 AREA_NM 이 서울이 아닌 모든 레코드가 나오게 된다.
근데 NVL 사용시 주의 할 문제가 있는데 위처럼 아닌것(<>)찾는경우에는 어차피 다 뒤지게 되므로 상관 없겠지만 같은것(=) 을 찾을 때 사용했을 경우 해당 컬럼에 인덱스가 되어있더라도 인덱스 사용을 할수가없게 된다.
왜나면 모든 값을 비교해봐야 NVL 결과를 알수있기 때문이다.
혹 NULL 인 것을 INDEX를 통해 빨리 찾고 싶을때는 다음과 같이 할수있다.
create index GOODIDEAS_null_ix on GOODIDEAS( NVL(AREA_NM,'null'));
이렇게 해두면 NVL(AREA_NM,'null) 형태로 인덱스가 되므로 이후 위 테이블에서 AREA_NM이 NULL인 것을 찾고자 할때.
==========쿼리 =============
SELECT * FROM GOODIDEAS WHERE NVL(AREA_NM,'null')='null';
============================
인덱스를 적용받게 되어 빨리 찾을 수 있다.
PLAN 을 보면 인덱스를 사용하게 되는 것을 알수있다
| Plan | ||
|---|---|---|
| SELECT STATEMENT FIRST_ROWS Cost: 1 Bytes: 399 Cardinality: 1 | ||
| 2 TABLE ACCESS BY INDEX ROWID TABLE GOODIDEAS Cost: 1 Bytes: 399 Cardinality: 1 | ||
| 1 INDEX RANGE SCAN INDEX GOODIDEAS_NULL_IX Cost: 1 Cardinality: 1 | ||
여기서 하고싶은얘기는 NULL 사용시 주의할 사항이므로 여기까지만 정리해둔다.