Does database index need to be made for "ORDER BY" clause??

[fletchsod]

You know, I know that the database index need to be made for the "WHERE" clause start from left to right (B-Tree Index)..

I wonder if I need to create a database index for the "ORDER BY" clause? I just thought of that because we have to make indexes for the "WHERE" clause, you know... So, I was wondering if that mean we need to do one for the "ORDER BY" clause..

Thanks...

 

[gregsimpson]

fletchsod,

you don't need to make indexes for either the where clause or the order by clause.

However 9 times out of 10, indexes which have been defined to support your processing, will greatly enahnce the performance you get from your database.

You may wish to consider other performance related information around runstats, db2advis and db2expln.

Cheers
Greg

 

[ddiamond]

I know indexes can be used in the from clause and the where clause. I was under the impression that indexes were not used for the group by or order by clause. Is that correct?

 

[fletchsod]

Um, in that case I'll leave out the "order by" clause. Less stuffs for me to do. I'll just focus on the "where" clause instead. It's a web site I'm working on that use database. So, I'll just grab all of the SQL Queries that use the "where" clause and decide on which and what need to be index that would be helpful with the table with many rows.

Thanks...

 

[fletchsod]

Hey ddiamond, Here's the link from another forum posting that would answer your question about "group by" and "order by" clause.

http://www.dbazine.com/db2/db2-mfarticles/mullins-db2indexing

 

[ddiamond]

Thanks fletchsod. I stand corrected. In DB2, indexes can improve the perfomance of group by and order by. I'm most experienced with Oracle 8.0. In that database, indexes only apply to from and where.

 

[blom0344]

I am tempted to add: 'DB2 rules' , but then that would be pretty childish, wouldn't it?

Ties Blom