Re: Sorting Oracle tables

Carlos García wrote:

> I have a table with a column called product_id. This column is used for
> identify the products in my app.

I suppose you have a unique index key_sampletable_product_id on that column.

> Well, i need to access to one row using a select.... where product_id =
> 1111, and i need to movenext and moveprevious over this table, in order to
> obtain the next an previous product row ordered bye product_id.
> I can make this using "order by product_id", but the query is too slow.

If you have a unique key key_sampletable_product_id, you can use the following:

select min (next.product_id)
from sampletable next
where next.product_id > actual.product_id;

This is even ANSI SQL. You can speed up this command a lot by adding the clause

and rownum <= 1

If you want the next 20 rows according to the sorting in your primary index, you should use

select /*+ index (key_sampletable_product_id) */ next.product_id from sampletable next
where next.product_id > actual.product_id and rownum <= 20
order by product_id;

> if i don't use the "order by product_id" clause, the rows are not sorted in
> my query.

Don't be afraid of order by.

> Can i make this table sorted bye product_id, and obtain the rows ordered by
> product_id when i call movenext and moveprevious method?

Previous works similar to next. Just substitute min by max, and > by <.

--
Martin Haltmayer