Re: SQL statement

Without a sort by clause, the data is sent to the user 'on the fly'. With a sort by clause, it has to cache the data on disk, sort it and then send it to the user, even if the data is indexed or not. An index on the sort by column wil speed things up but you will still get a bit of a hang before the results are shown

Mark

hpcheong_at_ncs.com.sg wrote in message <7h864j$330$1_at_nnrp1.deja.com>...
>hi,
>
>if i do the following statement, the result is instant.
>
>select a.UIN, a.Name, a.Street_Code, a.Block, a.Storey_No, a.Main_Hse,
>a.Sub_Hse, a.Sex_Code, a.DOB, a.Race_Code, a.Country_of_Birth_Code,
>a.Bkgrd_Lang_Stream_Code, a.Edn_Code, a.Religion_Code from tb_EASY_RMF a
>where citizenship_code = '0' and to_number(rpad(dob,8,'0')) <=
>to_number(to_char(to_date('31/05/1978','DD/MM/YYYY'),'YYYYMMDD')) and
>Date_New_IC_Issued <= to_date('31/05/1999','DD/MM/YYYY');
>
>but is i add a order by at the end, it seems like hanging there, why? pls
>help cos, it's urgent. thanks..
>
>select a.UIN, a.Name, a.Street_Code, a.Block, a.Storey_No, a.Main_Hse,
>a.Sub_Hse, a.Sex_Code, a.DOB, a.Race_Code, a.Country_of_Birth_Code,
>a.Bkgrd_Lang_Stream_Code, a.Edn_Code, a.Religion_Code from tb_EASY_RMF a
>where citizenship_code = '0' and to_number(rpad(dob,8,'0')) <=
>to_number(to_char(to_date('31/05/1978','DD/MM/YYYY'),'YYYYMMDD')) and
>Date_New_IC_Issued <= to_date('31/05/1999','DD/MM/YYYY') order by
>Street_Code, Block, Storey_No, Main_hse, Sub_hse;
>
>
>
>
>
>
>--
>evelyn.
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
Received on Tue May 11 1999 - 00:00:00 CEST