Re: Problem with understanding Optimization methods.

I don't know, but it's been in Steve Adams' seminar material for a long time.

It doesn't apply to a single-column b-tree index - you need at least one column available to be the mandatory column.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 08, 2004 1:59 PM

> Hi Jonathan,
>
> What release did this NULL_CHECK start with?
>
> I used to see:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
> 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=1 Bytes=6)
>
>
> As I recall, I used-to need an FBI on column with NULL values
>
> create index
> i1
> on
> t1
> (nvl(n1,'null'))
> ;
>
>
> Regards,
>
> Donald K. Burleson
> www.dba-oracle.com
> www.remote-dba.net
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, January 08, 2004 8:14 AM
>
>
> >
> > Conversely, the CBO is a lot smarter with
> > this scenario that people realise. How many
> > people knew that Oracle can resolve a query
> > of the type:
> > where colX is null
> > using a b-tree index ?
> >
> > Try this --
> >
> > drop table t1;
> >
> > create table t1 (n1 number, n2 number not null, n3 number);
> > create index i1 on t1 (n1, n2);
> >
> > set autotrace traceonly explain
> > select /*+ first_rows */ * from t1 where n1 is null;
> > set autotrace off
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=4
> > Bytes=156)
> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card=4
> > Bytes=156)
> > 2 1 INDEX (RANGE SCAN) OF 'I1' (INDEX) (Cost=4 Card=4)
> >
> >
> > Regards
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > The educated person is not the person
> > who can answer the questions, but the
> > person who can question the answers -- T. Schick Jr
> >
> >
> > Next public appearance2:
> > March 2004 Hotsos Symposium - Keynote
> > March 2004 Charlotte NC - OUG Tutorial
> > April 2004 Iceland
> >
> >
> > One-day tutorials:
> > http://www.jlcomp.demon.co.uk/tutorial.html
> >
> >
> > Three-day seminar:
> > see http://www.jlcomp.demon.co.uk/seminar.html
> > ____UK___February
> >
> >
> > The Co-operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, January 08, 2004 11:54 AM
> >
> >
> > Bambi,
> >
> > Yes it is expected behaviour, but only when it is guaranteed that no
rows
> > will be missed because of unindexed null entries.
> > I wanted to point out that RBO is too "dumb" to realize that even though
> it
> > ordered by column A which could be null, the column B in composite index
> was
> > not null, thus causing every row to be indexed and RBO didn't use the
> index.
> >
> > Tanel.
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Lewis
> > INET: jonathan_at_jlcomp.demon.co.uk
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Don Burleson
> INET: don_at_burleson.cc
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).