SQL tuning & optimization queries

[Chengwei Lee]

Hi all,

I understand that by indexing a table, it would improve on the performance of a query. So if thats the case, won't it be good that I create indexes for all columns of all my tables? Is there any pitfalls in doing so?

Read from some optimization guides that ORDER BY does affect the performance negatively, but why?

Hopefully someone could enlighten me on these issues.

[Mahesh Kuhile]

Hi,

Maintaining index is quite difficult thing. U need extra storage for that.
Again when there is DML operation on that table subsequently index need to be updated.

When user want to retrive data more than 5% it is not recommended to use index.

Second thing u r talking is about the order by. When order by is specified in the query sorting is done in memory which consumes lot of resoureses and makes the exection slow.

HTH..

--Mahesh