| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problems with sorting and hints in Oracle7.3
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.
Ok, so how to do what it is you want to do. Look at the INDEX() and INDEX_DESC() hints. INDEX( dictionary.nbo NAME_OF_INDEX_ON_SEARCH ) and INDEX_DESC( dictionary.nbo NAME_OF_INDEX_ON_SEARCH ) will typically cause the optimizer to read the index forwards or backwards. In order to ENSURE the correct answer, you must leave out the rownum clause and just fetch as many rows as you want. So your queries will look like:
select /*+ index( NBO INDEX_NAME ) */ * from dictionary.nbo nbo
where search > search_start
and search like constraint
order by search
select /*+ index_desc( NBO INDEX_NAME ) */ * from dictionary.nbo nbo
where search < search_start
and search like constraint
order by search desc
You should only fetch then the number of rows you are interested in. If you try to mix in the rownum clause AND the optimizer decides to ignore your hint (as it may) you will not get the answer you wanted....
On Fri, 17 Oct 1997 18:42:17 +0200, Paul.Meurer_at_hd.uib.no (Paul Meurer) wrote:
>Hi,
>
>I have a table whose rows are dictionary entries consisting of an id field,
>a text field, a search key derived from the text (by dropping diacritical
>marks and punctuation etc) and other stuff. I have created a (non-unique)
>index on the search field, and computed its statistics.
>What I want to be able to do is alphabetically (wrt the search field)
>moving through the table, both forward and backward.
>Each select statement should return either the next (forward search) or the
>previous row (or a few of them, and maybe satisfying other constraints),
>but clearly not all rows alphabetically above/below the current row.
>I tried using an optimizer hint like
>
>select /*+ first_rows */ * from dictionary.nbo
>where search > search_start
>and search like constraint
>and rownum < 2 (or another small number)
>order by search
>
>and
>
>select /*+ first_rows */ * from dictionary.nbo
>where search < search_start
>and search like constraint
>and rownum < 2
>order by search desc
>
>Both queries worked as expected in a trial version of Oracle 8.0 on NT4.0,
>but, misteriously, ceased to work when I reinstalled the same database and
>tables on an other machine (of the same type, namely Dell Optiplex GXPro).
>Neither do I get them to work under Oracle 7.3 Enterprise, which our
>institute recently purchased. The first query works in most cases, but
>sometimes jumps over some rows, whereas the second one consistently returns
>the alphabetically first few rows, in descending order.
>I run explain plan on the queries (in Oracle 7.3 only), which reads
>
>select statement goal: first_rows
> sort (order by)
> count (stopkey)
> table access goal: analyzed (by rowid) of 'nbo'
> index goal: analyzed (range scan) of ´nbo_search_index´ (non-unique)
>
>I am wondering if the feature I want to implement really is so rarely
>needed that you can´t get it reliably to work.
>
>One solution to the descending ordering problem I could imagine: as there
>are no descending indices in Oracle, make one yourself. That is, introduce
>a new row called search_desc which contains an ´inverted´ form of the
>search key, obtained by replacing a -> z, b -> y, c -> x and so on. An
>index on search_desc would work in the same way as a descending index on
>search. This, however, is going to get really messy when national sorting
>orders are involved, as in my case.
>
>Any suggestions/hints/solutions etc. are greatly appreciated.
>
>Yours, Paul
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.
I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Fri Oct 17 1997 - 00:00:00 CDT
 |
 |