Re: Help - Simple SQL query doing full table scans

I hold no candles for the optimizer, but it really isn't as bad as people make out, and gets better and subtler every release.

The idea of returning a fistfull of records, when Users are only interested in the first x-dozen of them, sounds like distinctly dodgy application design to me. In any case, if you want the majority of the rows returned in a sorted order, that's what an order by clause is for. It's a misuse of an index otherwise, involving excess I/O that's totally not required.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message
news:vac%7.5676$1s6.1112053_at_news6-win.server.ntlworld.com...


> > You're then using the incorrect syntax for the index hint.

> >

> > But the real point is that forcing the thing to use the index is not

going


> > to help you very much (because if it did, the optimizer would have

chosen


> to

> > use it in the first place!).

>

> When was the optimiser made so good!

>

> Just because the query would return all rows doesn't mean that is what is

> going to be fetched.

>

> It is quite common (in my experience) to run queries to populate a list of

> rows, one of which is to be selected. The users generally look at only the

> first few pages so it is very advantageous to use the index to do the

sort.


>

>