Hi Team


I was just wondering how the index being used for 'order by' clause. For one of my table while accessing data & using Order By caluse in the query, the response time improves when there's index for the column i am using order by clause.
But why it's not showing that index usage in the expalin plan?

Any Views?
Navdeep

-----Original Message-----
From: Nick Cowe
[mailto:oracledba-ezmlmshield-x66670596.[Email address protected]
Sent: Thursday, July 15, 2004 3:47 PM
To: LazyDBA Discussion
Subject: RE: order by is slow


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