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

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

my car won't go
Please help me to understand this

We can't say what is wrong since you did not show exactly what you have & do.
post DDL for both the table & all involved indexes.
post actual SQL that does not behave they way you expect it perform.
Realize that ORDER BY clause require a sort operation.
INDEX exist to speed up row filtering (WHERE clause) & not ORDER BY clause.

John WatsonMessages: 8274Registered: 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.

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