Re: why index is not used in query execution plan ?

ajallen_at_ameritech.net wrote:
>
> Ok, here we go.
> 1. I assume your stats are current.
> 2. Look at the query plan when you use an index hint. The index
> range scan tells me that the index you are trying to use is not
> unique.
> 3. The Oracle optimizer knows, through the current table and index
> stats as well as the fact that the index is not unique.
> 4. The optimizer figured out that it would be less costly to do a
> table scan than an index range scan and then a table access for each
> hit in the index scan.
>
> I would suggest that you keep your stats current and let the
> optimizer do its job. It seems to have gotten it right here.

Why do you think that? The CBO seems to have gotten it right because the CBO says the other has a higher cost? I've seen the 'best' CBO method take >1000 times longer than hinted methods which had a CBO cost >10 times higher. It seems to me the CBO got it wrong in this case, or else the poster probably wouldn't be posting.

> Since you are using 8i, I would suggest that you change to
> DBMS_ANALYZE instead of analyze table.
>
> Finally, does the application really submit queries with literals?
> It should be using bind variables. Using literals in queries like
> this forces a costly hard parse every time it is submitted.

How costly is a hard parse on such a simple query, compared to the ever-present soft parse? I don't know how to benchmark it, but I'd be surprised if was substantially longer. If course, I'm frequently surprised :)

I like using bind variables, but on the occasions I've rewritten old code to use them, I expected performance improvement and didn't see any. Perhaps the system isn't under enough stress for that to show up. Also, bind values prevent the CBO from making data-specific optimizations. (In 8i, anyway.)

> Using
> literals instead of binding variables is the most efficient way of
> making an application non scalable because each user adds more
> one-time sql to the shared pool.

Aye. Is there a way to prevent non-bind-using SQL from being cached, or at least sticking into the LRU end?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
                    Usenet Newsgroup Service