Re: Oracle not using indexes on char/varchar columns

Howard J. Rogers wrote:
>
> So, sorry Kamil... but what's the question now?
>
> The full table scans have disappeared, and index usage is consistent. Or is
> it the varying cost that's bothering you? I wouldn't worry about it,
> largely because (from memory) costs take account of index pre-fetching and
> caching... so you first cost is higher than subsequent ones, because (I'd
> imagine) it realises the index nodes will have to be physically fetched the
> first time, and will be in the cache the second and subsequent times.
> Not sure why you threw a select from dual in there, either.

In that example it helped a bit, but in our real DB(all statistics computed) there's a query, that takes about 3 minutes to execute (even when run multiple times), but after *ANY* explain plan is run (even 'EXPLAIN PLAN FOR SELECT * FROM DUAL') the same query takes 100msec to execute.

Kamil Received on Wed Oct 08 2003 - 07:28:54 CDT