|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to tune oracle table in sorted order [message #462141 is a reply to message #462140] |
Wed, 23 June 2010 00:45   |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #462207 is a reply to message #462159] |
Wed, 23 June 2010 03:36 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
