Clustered vs Non clustered indexes

[OrangeHopper]

Very slightly off topic but I am very sceptical about indexes. We had a database designed by a DBA with lots of indexes for this that and the other. This was an Oracle database and I read somewhere that only under certain conditions would the index be used. Did an explain plan on all the business related queries and none were utilising an index. So the end result was that all insertions, and subsequent deletions, where running slower than necessary because of of the creation and deletion of indexes. Got rid of the indexes and things ran significantly faster. Oh, and of course, the database storage requirements were also significantly reduced.

[threaded]

Good point there OrangeHopper. It's an example of what I was saying: you really have to suck it and see, and rationalise after the fact. What you'd think would improve things often turns out to have quite the opposite effect.

[jmo21]

So the end result was that all insertions, and subsequent deletions, where running slower than necessary because of of the creation and deletion of indexes. Got rid of the indexes and things ran significantly faster.

Inserts being a little slower obviously makes sense without an index as something extra has to happen.

[DimPrawn]

I think what OH is saying, is some inexperience DBA types/developers think, "If an index on a column speeds things up, I'll stick an index on every column of every table and the DB will run like lightning".

The database then has to maintain these indexes on every insert, update and delete.

The only true way to tune a database is against a representative set of captured SQL (a workload).

[Spacecadet]

So the end result was that all insertions, and subsequent deletions, where running slower than necessary because of of the creation and deletion of indexes. Got rid of the indexes and things ran significantly faster.

don't forget the updations

[The Wikir Man]

I think what OH is saying, is some inexperience DBA types/developers think, "If an index on a column speeds things up, I'll stick an index on every column of every table and the DB will run like lightning".

WHS - many developers just ask them to be put on too many columns, and not enough DBAs (in my experience) question WHY they want them.