[이벤트 둘!] 당신의 오라클 내공을 보여주세요~

Oracle ACE "Dion Cho" 가 직접 출제하는 주옥같은 문제!
당신의 오라클 내공을 보여주세요~ 
가장 좋은 정답을 제출한 분께 5만원 상품권을 드립니다! 

지금 바로 도전하세요~!                                   지난달 당첨자 및 정답발표

이번 문제는 아래와 같습니다.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1
  2  select 1 from dual connect by level <= 10000
  3  union all
  4  select null from dual connect by level <= 10;

10010 rows created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', no_invalidate=>false);

PL/SQL procedure successfully completed.

SQ> -- WHERE C1 IS NULL 은 반드시 Table Full Scan!
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
-----------------------------------
Plan hash value: 3617692013

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

8 rows selected.

SQL> -- 하지만 아래와 같이 인덱스를 만들면 Index Range Scan 가능!
SQL> { 여기에 들어갈 SQL 문장을 완성해주세요 };

Index created.

SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 677322570

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N2 |
----------------------------------

8 rows selected.

 

위의 테스트 케이스에서 { 여기에 들어갈 SQL 문장을 완성해주세요 } 부분에 들어갈 적절한 SQL 문장을 보내주시면 됩니다. 해답을 보내주신 분들 중 다음과 같은 기준으로 정답자 한 분을 선정합니다.

• 가장 먼저
• 가장 정확한 정답을
• 기타 재미있는 아이디어가 있으면 가산점!

정답제출: quiz@ex-em.com
정답자 발표 : 2010년 9월 1일 수요일 오후 3시

많은 참여 부탁드립니다^^
------------------------------------------------------------------------------------------------------
퀴즈 정답 접수가 마감되었습니다. 

<퀴즈 당첨자>

남**  14***@naver.com

많은 분들이 정답을 보내주셨습니다~
참여해주신 모든 분들께 진심으로 감사드립니다. 
정답이 아래에 공개됩니다!  

<퀴즈 정답> 

 

1. 퀴즈의 정답은 아래와 같이 무의미한 Dummy 키를 추가한 일종의 복합 인덱스를 만드는 것입니다.

SQL> create index t1_n2 on t1(c1, '');

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

Explained.

SQL> -- 아래의 문법은 Oracle 11g에서만 동작합니다. 
SQL> -- 10g까지는 select * from table(dbms_xplan.display) 로 호출하면 됩니다.
SQL> select * from table(dbms_xplan.display(format=>'basic'));

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N2 |
----------------------------------

NULL 값을 다루는 몇가지 다른 방법을 볼까요?

2. 우선 비트맵 인덱스는 NULL 값을 포함합니다. 단, 이것은 이론적인 설명으로 실제 운영환경에서 NULL 값을 인덱싱하기 위한 목적으로 비트맵 인덱스를 함부로 사용해선 안됩니다.

SQL> create bitmap index t1_n1 on t1(c1);

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where c1 is null;

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  BITMAP CONVERSION TO ROWIDS |       |
|   2 |   BITMAP INDEX FAST FULL SCAN| T1_N1 |
----------------------------------------------

3. IS NULL 조건을 변경할 수 있다면 아래와 같이 NVL과 같은 함수를 이용한 Function Based Index도 사용할 수 있습니다.

SQL> create index t1_n1 on t1(nvl(c1,-1));

Index created.

SQL> 
SQL> explain plan for
  2  select * from t1
  3  where nvl(c1,-1) = -1;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |
|   2 |   INDEX RANGE SCAN          | T1_N1 |
---------------------------------------------

4. 또 하나의 방법은 NAN 값을 이용하는 것입니다. NAN은 Not-A-Number의 약자로 Number는 아니지만 Null도 아닌 값을 의미합니다. 아래와 같이 사용할 수 있습니다. 단, Number 타입에 대해서만 지원된다는 제약이 있습니다.

SQL> create table t1(c1 binary_float);

Table created.

SQL> 
SQL> insert into t1
  2  select 1 from dual connect by level <= 10000
  3  union all
  4  select binary_float_nan from dual connect by level <= 10;

10010 rows created.

SQL> create index t1_n1 on t1(c1);

Index created.

SQL> explain plan for
  2  select /*+ index(t1) */ * from t1
  3  where c1 = binary_float_nan;

----------------------------------
| Id  | Operation        | Name  |
----------------------------------
|   0 | SELECT STATEMENT |       |
|   1 |  INDEX RANGE SCAN| T1_N1 |
----------------------------------

역시 1번 정답이 최고의 방법이군요!