Stephan,
the clause was WHERE lastdate IS NOT NULL, which activates the index on
lastdate. But the column summed (LEN) is not in the index. CBO just decided
it would be cheaper to do FTS than do an index range scan and table access
by rowid for this. I think hinting the query would do the trick. And table
should be fully analyzed for CBO to make correct cost computation, estimates
may not be accurate.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Stephan Bressler" <stephan.bressler_at_pdb.sbs.de> wrote in message
news:aa37i9$710$1_at_news.mch.sbs.de...
> Hi Ed,
>
> NULL Values are not stored in a btree index. Therefor, if you ask '... where
> colum is null', the index is unusable.
> A usual workaround is to use a dummy value, which semantically represents
> NULL, e.g. Jan. 1st 1900 for a
> unknown time value.
>
> Regards
> Stephan
>
>
>
>
Received on Tue Apr 23 2002 - 07:10:51 CDT
