Hi Rick, I'm not actually answering your question, but I still hope
this can be useful for you (and your feedback useful for me).

Even though your query was pretty simple, it confused me a bit. Hence,
I changed it to something that I think produces the same result:

Select *
From CLAIMSPAIDREVERSED T1
Where not exists
(Select *
From CLAIMSPAIDREVERSED T2
Where T2.RXCLAIMNBR=T1.RXCLAIMNBR
And T2.CLMSEQNBR>T1.CLMSEQNBR)

Check and see if this produces the same plan, result and execution
time as the query Ded (Alexander) showed you. If you still are not
satisfied, you could try creating a new field CLMSEQNBRNEG and fill it
with the negative equivalents of CLMSEQNBR, create a combined index on
RXCLAIMNBR, CLMSEQNBRNEG and change the query to

Select *
From CLAIMSPAIDREVERSED T1
Where not exists
(Select *
From CLAIMSPAIDREVERSED T2
Where T2.RXCLAIMNBR=T1.RXCLAIMNBR
And T2.CLMSEQNBRNEG<T1.CLMSEQNBRNEG)

Note that an index on (RXCLAIMNBR, CLMSEQNBR) is not the same as
(CLMSEQNBR, RXCLAIMNBR) and that they could provide different plans
and execution time depending on the data. There's no way an index
could be used on T1.

HTH,
Set
-a Firebird Foundation member who never even applied through
http://www.firebirdsql.org/ff/foundation

--- In firebird-support@yahoogroups.com, "Rick DeBay" <rdebay@r...>
wrote:

> Select *
> From CLAIMSPAIDREVERSED T1
> Where T1.CLMSEQNBR=
> (Select First 1 T2.CLMSEQNBR
> From CLAIMSPAIDREVERSED T2
> Where T2.RXCLAIMNBR=T1.RXCLAIMNBR
> Order By T2.CLMSEQNBR DESC)
>
> With an ascending index on RXCLAIMNBR, this is the plan that is
> generated and the query runs OK:
> PLAN SORT ((T2 INDEX (RXCLAIMNBR)))
> PLAN (T1 NATURAL)
>
> With an additional descending index on CLMSEQNBR, this is the plan

that

> is generated, and the query doesn't return and the CPU is pegged:
> PLAN (T2 ORDER CLMSEQNBR)
> PLAN (T1 NATURAL)
>
> Adding an index that contains both columns is never used by the

plan.

> What do I need to do to improve the plan?
>
> Thanks, Rick DeBay