At 01:00 PM 8/06/2003 +0000, you wrote:

>--- In ib-support@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
>Helen,
> I have one more field in the query called CUST.Cust_no.And this
>field is having a index on CUST table.If i give order by CUST_NO, now
>it takes 3 minutes 2 seconds for fetching the same 27019 records,

Does this column Cust_no happen to be the primary key of the table?

> If i remove the order by clause, then it takes 2 minutes 55 seconds
>for fetching 27019 records.

What you describe is a symptom of an index "stepping on" a primary key and
causing the optimizer not to use the index for the sort. With the ORDER
BY, it is using no index; without it, it is using the index on the primary
key.

Show us the plan - that is the only way to tell which indexes (if any) the
optimizer is using.

heLen