Re: Not null and index

From: Richard Foote <richard.foote_at_nospam.bigpond.com>
Date: Thu, 20 Sep 2007 11:55:35 GMT
Message-ID: <XctIi.29$H22.4@news-server.bigpond.net.au>

"Maxim Demenko" <mdemenko_at_gmail.com> wrote in message news:46F11214.1050307_at_gmail.com...
> 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
>

Hi Maxim

Well done ;)

Cheers

Richard Received on Thu Sep 20 2007 - 06:55:35 CDT