Re: Optimizer execution-plan problem

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Fri, 02 Nov 2001 12:50:20 GMT
Message-ID: <3be295e4.1621621@news>

In a valiant and sublime effort,Stefan Olausson frowned, dipped a thumb in soot and doodled:

>
>SQL> CREATE TABLE tmp_n(a NUMBER, b NUMBER);
>SQL> CREATE INDEX tmp_n_ix ON tmp_n(a,b);
>SQL> SELECT MIN(b) FROM tmp_n WHERE a IS NULL;

                                     ^^^^^^^^^
Bingo,                        here is your problem.

That doesn't use an index, because NULL values are not kept in the index (they can't, they are not supposed to exist).

Although I still think this is a bit of a "stick to the rules" thing from the part of ORACLE. There is no reason why a special entry in the index b-tree cannot be kept (as an option) where rowid's for NULL values of a column could be found. To be used with discretion, of course.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Nov 02 2001 - 06:50:20 CST