Re: Not null and index

Charles Hooper schrieb:
> How would you index NULL values? Function based index?
>
> Thanks again for your contribution.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>

One of alternatives ?

SQL> create index emp_comm on emp(comm,1);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'emp',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select count(*) from emp where comm is null;

   COUNT(*)

         10

Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)     1 0 SORT (AGGREGATE)
    2 1 INDEX (RANGE SCAN) OF 'EMP_COMM' (NON-UNIQUE) (Cost=1 Card=10 Bytes=20)

Best regards

Maxim Received on Wed Sep 19 2007 - 07:12:04 CDT