Re: Oracle indexes nulls in the leading part of a composite index???

A copy of this was sent to mbriganti_at_my-deja.com (if that email address didn't require changing) On Thu, 06 Jan 2000 20:03:00 GMT, you wrote:

>Here is an interesting situation for anyone who cares. Why does Oracle
>index nulls in the leading part of a composite index when it can never
>use them?
>
>For example ...
>
>create table test (
> non_mandatory_column varchar2(10) null,
> mandatory_column varchar2(10) not null
>)
>
>create index composite_index on
>test (non_mandatory_column, mandatory_column);
>
>insert into test values (NULL, 'ABC');
>
>THIS ROW GETS INDEXED, but can Oracle ever use this entry in the
>index? Can anyone think of a WHERE clause?
>
>Thanks in advance for all replies.
>
>Mimmo Briganti
>i2 Technologies
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

CBO can use it:

tkyte_at_8.0> create table t ( x int, y int not null );

Table created.

tkyte_at_8.0> create index t_idx on t(x,y);

Index created.

tkyte_at_8.0>
tkyte_at_8.0> insert into t values ( null, 1 );

1 row created.

tkyte_at_8.0> 
tkyte_at_8.0> set autotrace on explain
tkyte_at_8.0> select /*+ index( t t_idx ) */ * from t where x is null and y = 1;

         X          Y
---------- ----------
                    1

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=52)    1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1

          Bytes=52)

tkyte_at_8.0> set autotrace off

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jan 07 2000 - 07:26:48 CST