On Wed, 21 Jan 2004 17:30:26 +0000 (UTC), tractaylor wrote:

> I have a question about one of my sql statements in my applications. The
> sql will be below. Basically, I have this query that runs fast normally.
> But as soon as I put a order by clause, it takes lots of time. I do have
> a index of the order by column I am using. Let me show you the sql and
> the plans that are being used. I also have the stats that I will show
> below.

Do you have an index on HTF.DATE_RECEIVED? If so, try to remove it.
If HTF.DATE_RECEIVED needs to be indexed, try this:

select HTF.DATE_RECEIVED, HTF.DATE_RECEIVED+0
...
order by 2 desc

(or use HTF.DATE_RECEIVED||'' if it is a varchar/char field)


--
Aage J.

>
> //----------------------------------
> SQL QUERY:
>
> SELECT HTF.CONTRACT_SYSID
>
> FROM HUD_TRACS_FILES HTF
> inner Join CONTRACTS_HAS_ACCESS CHA on HTF.CONTRACT_SYSID =
> CHA.CONTRACT_SYSID and CHA.User_Sysid = 1007 /* UserKey */
> inner join Lookup_Codes SubsidyType on HTF.SUBSIDY_TYPE =
> SubsidyType.Code_Id
> inner join Lookup_Codes LC on HTF.File_Type = LC.Code_Id
> inner join Lookup_Codes LC1 on HTF.MADE_BY = LC1.Code_ID
>
> order by HTF.DATE_RECEIVED DESC
>