Re: SELECT statement efficiency question

On Sun, 08 Apr 2007 06:37:37 -0700, Charles Hooper wrote:

> Good advice given so far. I would caution against creating too many
> indexes, as this will likely negatively impact performance of other
> parts of the system

Charles, this is the line I frequently find in many books, CBT and manuals and yet I have never seen insert or delete slowed down to the unacceptable levels because of too many indexes. The only method to diagnose that this is indeed happening would be to observe significant increase in average I/O time on the underlying data file. Again, I've never even seen this happening. I believe that this thing with too many indexes is dangerous only in the extreme situations and it is very hard to diagnose because the process that waits for writing the index blocks is DBWR so the users never wait for the blocks to be written. Users may wait for checkpoints or log file sync but not for the index

-- 
http://www.mladen-gogala.com