At 04:32 AM 15/03/2007, you wrote:

>I´m looking for a faster way to read backwards inside a table.
>
>The Table has more than 400.000 entries.
>
>
>unique index key bsysid,bclid,order_no Ascending
>
>1. Way
>
>SELECT first 1 order_no FROM ORDER_HEADER WHERE bsysid = 3 AND bclid =
>1 AND order_no < 300361 order by order_no desc
>
>Plan
>PLAN SORT ((ORDER_HEADER INDEX (ORDER_HEADER_KEY)))
>
>Adapted Plan
>PLAN SORT ((ORDER_HEADER INDEX (ORDER_HEADER_KEY)))
>
>------ Performance info ------
>Prepare time = 0ms
>Execute time = 579ms
>Avg fetch time = 579,00 ms
>Current memory = 2.402.128
>Max memory = 2.484.652
>Memory buffers = 8.192
>Reads from disk to cache = 8.348
>Writes from cache to disk = 0
>Fetches from cache = 226.038
>
>2. Way better bad not good.....
>
>SELECT max(order_no) FROM ORDER_HEADER WHERE bsysid = 3 AND bclid = 1
>AND order_no < 300361
>
>Plan
>PLAN (ORDER_HEADER INDEX (ORDER_HEADER_KEY))
>
>Adapted Plan
>PLAN (ORDER_HEADER INDEX (ORDER_HEADER_KEY))
>
>------ Performance info ------
>Prepare time = 0ms
>Execute time = 500ms
>Avg fetch time = 500,00 ms
>Current memory = 2.401.612
>Max memory = 2.484.652
>Memory buffers = 8.192
>Reads from disk to cache = 8.348
>Writes from cache to disk = 0
>Fetches from cache = 226.038
>
>
>I´ve created an other INDEX like
>
>unique index key bsysid,bclid,order_no Descending
>
>and same combinations of this indexes inside the SQL select
>statment, but the result is the same...
>
>
>My question now has someone a better idea to save this problem
>with a better response time ?

Yes, don't make composite indexes in the belief
that they are needed for multiple-key
searches. Remove the compound indexes. Instead,
index the search columns individually and include
a descending index for any column that needs it - order_no in this case.

./heLen