Problems with sorting and hints in Oracle7.3

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

-- 


___________________________________________________________________________

Paul Meurer                             telephone:  +47 55 58 97 94
The Norwegian Term Bank /               telefax:    +47 55 58 97 85
Norwegian Computing Centre
for the Humanities                      web; http://www.uib.no/nt/index.htm
Strømgt. 53
5007 Bergen, Norway


___________________________________________________________________________