Index on order by column [message #671716] |
Sat, 15 September 2018 09:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/ddaec7c9b7a301f4a3bd588d31872015?s=64&d=mm&r=g) |
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I had created index on column , but in our query we are not using indexed column in the where condition , but we are using it in the order by clause,
Here my question is will that index will help full when the indexed column is in order by clause ?
If I see the explain plan and cost before and after creating the index , i didn't find much difference
Please help me to understand this
Thanks
Sai Pradyumn
|
|
|
|
|
|
|
|
|
Re: Index on order by column [message #671726 is a reply to message #671718] |
Sun, 16 September 2018 04:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You ORDER BY clause is on INTERNAL_REF_NUM but your index is on BRANCH,INTERNAL_REF_NUM. Unless you include a filter on BRANCH, I do no see how your index can be used other than as an index full scan (or fast full scan) followed by a sort.
[Updated on: Sun, 16 September 2018 04:42] Report message to a moderator
|
|
|
Re: Index on order by column [message #671737 is a reply to message #671726] |
Mon, 17 September 2018 02:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Try removing the parallel hint, although based on what has been posted I don't expect major gains to be honest.
from the data supplied your options are:
FTS + sort
Index FFS + sort
Index full scan
Breaking these down you're looking at two approaches using multiblock access to read the object and one using single block access. Multiblock is going to win all day when it comes to that. An index FFS+sort might be marginally quicker than a table FTS, but I wouldn't expect miracles given the table described and it'll not do that in parallel (hence my suggesting the hint removal)
Perhaps if you added an access predicate or something else to help the database limit the rows?
All that being said, you seem to have skipped ahead to this approach being the solution to the problem, but is it really the correct solution? Again based on what has been supplied at best this is a band aid which likely won't work long term (if at all).
|
|
|
Re: Index on order by column [message #671754 is a reply to message #671737] |
Tue, 18 September 2018 02:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/ddaec7c9b7a301f4a3bd588d31872015?s=64&d=mm&r=g) |
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
Thanks for your suggestions.Finally we able to get the improvement by adding wherecondition on branch column and
stats gather on the table before firing this query.
Thank you very much for your valuable suggestions
Thanks
Sai Pradyumn
|
|
|