Re: Problems with sorting and hints in Oracle7.3

At 18:35 97/10/17 GMT, you wrote:
>what you are doing is dangerous in a word.
>
 

>HINTS are just that, hints -- and they may be used, but then again, they might
>not. the first_rows hint is a very vague hint as well.
>
>ROWNUM is always evaluated BEFORE the aggregates ORDER and GROUP by are applied.
>When the below works for you it is because the index was used (in the order by
>search case) and the rows happened to have been read sorted. when it "doesn't
>work" (although technically it is in fact working as it should) it is because
>the rows are read not sorted, the rownum is applied, the first N rows are taken,
>and then sorted.
>

Thanks for your answer -- that's how I feared it would be, and it is totally logical. My appearently positive experiences with my first try led me to believe that it really would work that way. And then: why isn't the problem I want to solve addressed in some way? Of course it falls out of relational calculus proper, but many features of RDBSs do. For your solution is not what I want, I'm afraid. A user of the dictionary does not know beforehand which (how many) rows he is interested in, he wants to be able to scroll back and forth, but also jumps a lot. If I execute the query you suggest, I maybe get in return thousands of rows, at least on the server if I use a stored procedure, having to wait quite some time; of those rows the user reads two or three, only to issue a totally different query soon afterwards. (Maybe I am mistaken; I don't have to tell you that I am rather new to RDBSs.)
Maybe I don't understand quite well how indices are working internally (I suppose they are B+-trees), but aren't they ordered and scanned alphabetically, and then, wouldn't it be easy to return the alphabetically first rows first?

Yours, Paul

Paul Meurer
Norwegian Term Bank
Allégaten 27
N-5007 Bergen
Norway Received on Fri Oct 17 1997 - 00:00:00 CDT