I had a revelation a couple of years ago when optimizing a database table that was running slow. The original query would take around 20 minutes in a table with large data-fields. After finally realizing that noone had bothered to index the table in the first place I built an index for a couple of the keys. Lo and behold, the query ran in 0.15 seconds!
What are some of your biggest improvements when adding an index for a table?
i had a quite similar case with a table which had no primary key set - so joining to this table (containing 5 row's or so) took about 10minutes (yes - the other table was quite big)
all happening on an MSSQL2k
after setting a PK it took less then a tenth of a second...
so the query-optimizer really f***cks up when no PK is present :)
This relates to a very old DBMS product, similar to Oracle but not Oracle itself. This product made it very easy to create a table with no indexes. Oracle is different in that, if you declare a primary key, Oracle will automatically create an index on the primary key. This product didn't do that.
I was called in to speed up a database that was crawling. There was a table called "CostCenters" with 900 rows in it and no indexes. A couple of years earlier, that table had had 20 rows in it. Referential integrity lookups on this table were requiring a table scan. The system was on its knees.
Creating the index took five minutes. It speeded things up by a factor of 100. We did some other things, like defragmenting the disks, and rebuilding some indexes that had become overpopulated. A task that had been taking 10 minutes before the speedup took two seconds after the speedup.
Having said this, don't let concerns about speed blind you to simple and sound design. You need simple and sound tables, indexes, database objects, application code, and queries. It's easy to speed up things that are simple and sound. It's much harder to take things designed only for speed and make them simple and sound.
I once modified an analytic function to include a couple of logically redundant columns in the windowing clause, allowing partition and subpartition pruning and index-based access.
A one hour query was reduced to 0.02 seconds, that being 180,000 times faster.
http://oraclesponge.wordpress.com/2006/03/15/predicate-pushing-and-analytic-functions/
Do I win? :D