I believe it's allways useless to combine rownum and order by. In your
example a hint index_desc and rownum = 1 would do for the smaller one, but
make it impossible for the like to be used for the index (search || '' like
constraint). For the bigger one, also an index hint, no order by, rownum =
1 and search || '' like constraint
--
Kind reGards,
Gerard
(ghp_at_santens.be; ghp_at_skynet.be)
Paul Meurer <Paul.Meurer_at_hd.uib.no> schreef in artikel
<Paul.Meurer-ya02408000R1710971842170001_at_nntp.uib.no>...
> 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
>
___________________________________________________________________________
>
Received on Fri Oct 17 1997 - 00:00:00 CDT
