Just Create the Index on the Column being used in Order by Clause.As the
Index Entries are always sorted hence while sorting it will refer the
sorted data of Index rather than table Segment.

Best Regards,

Rajeev Joshi.







"Deepa S. Kale " <oracledba-ezmlmshield-x87778460.[Email address protected]
07/15/2004 03:15 PM

To: "LazyDBA Discussion" <[Email address protected]
cc:
Subject: order by is slow



select vc_comp_code ,dt_voucher_date,vc_voucher_no,nu_account_code from
dt_transaction
where vc_comp_code='01'
and dt_voucher_date between '01-APR-2003' and '31-MAR-2004'
and nu_account_code in ( select nu_account_code
from mst_account
where vc_comp_code='01'
and vc_bank_code is NOT NULL )
and nu_serial_no=0 order by nu_account_code;

if i give order by its very slow... i hav enough sort_area_size :
|-------------------------------------------------+--------------+----------------|
| sort_area_retained_size | integer | 133120
|
|-------------------------------------------------+--------------+----------------|
| sort_area_size | integer | 133120
|
|-------------------------------------------------+--------------+----------------|
| sort_multiblock_read_count | integer | 2 |
|-------------------------------------------------+--------------+----------------|



i hav 2 temporary tblspace... and i m on 8.1.7....



Deepa Kale -DBA
Jyoti Structures - Nasik(IT)




--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html



Oracle LazyDBA home page