Re: SELECT statement efficiency question

"Mladen Gogala" <mgogala.SPAM-ME.NOT_at_verizon.net> wrote in message news:pan.2007.04.09.18.06.20_at_verizon.net...
> Mostly, for the OLTP transactions, indexes will extend
> the
> transaction duration from 300 milisec to 500 milisec or 1/2 of a second.
> Most users will not complain about 1/5 of a second.

It's not always that simple.

Inserts into tables tend to be focused over a very small number of blocks, which are therefore cached quite well.

Inserts into indexes are (except for your sequential key example) more likely to be scattered randomly across all leaf blocks. Unless you have a large enough memory to buffer most of the leaf blocks of most of the indexes, then inserts into (cached) table blocks cause increasing numbers of physical reads of index leaf blocks as the number of indexes increases. An insert into a table with 10 indexes could easily result in 9 or 10 physical read requests - which shouldn't affect the performance for a relatively low insert rate; but if you push the concurrency up you will find a lot of interference between inserts as the discs fail to respond to a high demand for random I/O.

It is still true that you should be cautious about adding indexes because of the maintenance overheads; but the balance of power has changed over the years , and can be quite hard to determine in general, due to the opposing effects of increased memory, and decreased spindle counts.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html