Home » RDBMS Server » Performance Tuning » How to tune order by clause without changing sort area (Oracle 9i,9.2.0.6.0,Windows XP)
How to tune order by clause without changing sort area [message #317400] Thu, 01 May 2008 01:17 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear all,

How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.

Regards,
Rajat Ratewal
Re: How to tune order by clause without changing sort area [message #317403 is a reply to message #317400] Thu, 01 May 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The sort performances are given by only one memory parameter sort_area_size (or the value derived from pga_aggregate_target).
So...

Regards
Michel

[Updated on: Thu, 01 May 2008 01:29]

Report message to a moderator

Re: How to tune order by clause without changing sort area [message #317407 is a reply to message #317403] Thu, 01 May 2008 01:35 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
So are you trying to say that you can't tune your order by clause in a query without changing this parameter.

Re: How to tune order by clause without changing sort area [message #317410 is a reply to message #317400] Thu, 01 May 2008 01:56 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Yes you can. Add an index so a sort is not neccessary.
Re: How to tune order by clause without changing sort area [message #317411 is a reply to message #317410] Thu, 01 May 2008 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This can be done in very seldom cases without speaking of performances drawbacks for ALL other statements if you build an index just for this.

Regards
Michel

[Updated on: Thu, 01 May 2008 02:05]

Report message to a moderator

Re: How to tune order by clause without changing sort area [message #317419 is a reply to message #317411] Thu, 01 May 2008 02:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I can't create the index than in that case what can be done.
Re: How to tune order by clause without changing sort area [message #317421 is a reply to message #317419] Thu, 01 May 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing as I already said.

Regards
Michel
how to tune oracle table in sorted order [message #462140 is a reply to message #317400] Wed, 23 June 2010 00:38 Go to previous messageGo to next message
dibakar
Messages: 4
Registered: December 2009
Location: kolkata
Junior Member
how to tune oracle table in sorted order in oracle 10g .So that when inserting data automatically store in shorted order.
Re: how to tune oracle table in sorted order [message #462141 is a reply to message #462140] Wed, 23 June 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way.
In addition, what does mean "store in sorted order"? There is no order at the physical level.
Table segment is build on several extents stored on several files stored on several disks for each block; so what is the physical level order and how could you relate it with your logical sort?

Regards
Michel

[Updated on: Wed, 23 June 2010 00:46]

Report message to a moderator

Re: how to tune oracle table in sorted order [message #462159 is a reply to message #462141] Wed, 23 June 2010 01:30 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Look at an index clustered table in order to physically store data with the same access key.

Not exactly stored in order, but as close as you are going to get to what you want.
Re: how to tune oracle table in sorted order [message #462207 is a reply to message #462159] Wed, 23 June 2010 03:36 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are few things you can do to avoid a sort when you specify an order by clause.

An order by can be skipped, but only under special circumstances:

1a) you have an index on a table that has the same ordering you want already
1b) your query uses that index to get data in the order implied by your order by and thus does not need to sort

Obviously this is difficult to achieve with today's sophisticated sql queries.

2) you are using oracle analytics and doing a window sort. If the window sort matches your order by and this window sort is the last window sort you do (in the case of multiple window sorts required for multiple analytics), then oracle will not bother to do the order by since the window sort will have put the data in the needed order already. As a side note, this is one of the reasons why Oracle Analytics are so great. In my experience half the time, the Analytic Sort (window sort) needed to build analytics matches the ordering of the query's order by clause. This means the Analytics are just about free in terms of cost. Beat that.

3) it may be possible to construct a materialized view or table that is an IOT in the ordering you want. This might also offer a way to avoid the sort similar to #2 above.

4) as was also suggested, you might consider clustering a MVIEW or table according to your order by criteria to once again try to take advantage of clustered rows. The idea here is not to skip the sort, but to have rows that sort close to each other already positioned close to each other.

5) as was also suggested, you could rebuild your table feeding rows to the rebuild operation in sorted order to achieve a one time ordering of rows that matches your order by clause. This will also put rows that sort via your order by clause, in the same order as the order by clause. You will not avoid the sort, but the sort will do less work. This is only a one time fix though and performance will degrade as the table is updated.

I get the sense that none of these things is true for you currently, and also that you cannot modify your system to make any of them true. In which case you cannot avoid the sort that is needed for the order by.

You may be able to "tune" the sort by reducing your query items. If there are columns being returned in your query that you do not actually need, remove them. This will make your sorted rows smaller and thus the sort cheaper.

So tuning a sort without changing the amount of memory available to the sort:

1) reduce the size of your row set by reducing columns returned
2) reduce the size of your row set by reducing number of rows returned
3) use some storage technique to put rows that would sort close to each other, already close to each other in the table(partitioning, clustering, IOT)
4) combing the order by with a compatible analytic OVER() clause so you skip one of them or get rows somewhat sorted before you reach the order by

Kevin
Previous Topic: My indexes was not active
Next Topic: need help tuning this query
Goto Forum:
  


Current Time: Sat Dec 10 16:33:09 CST 2016

Total time taken to generate the page: 0.09861 seconds