you could index dt_transaction.nu_account_code, but then you would have
extra reads to get at the other data columns, yes they will be in right
order (if the index is used) but your reads will be slower. You could index
ALL the columns involved in the query to cut down the number of reads, but
you end up with a bigger index to maintain.

have a look at the execution plan to see why adding the sort affects the
query

-----Original Message-----
From: rajeev
[mailto:oracledba-ezmlmshield-x58001328.[Email address protected]
Sent: 15 July 2004 10:47
To: LazyDBA Discussion
Subject: Re: order by is slow


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




--------
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