Re: SELECT statement efficiency question

On Mon, 09 Apr 2007 19:42:09 +0100, Jonathan Lewis wrote:

>
> Inserts into indexes are (except for your sequential key example) more
> likely to be scattered randomly across all leaf blocks.

Jonathan, that depends on the application. Especially in OPS world, I remember going to great lengths ensuring that there were enough FREELIST_GROUPS which were making sure that people were not inserting into the same blocks. The ITL lists were also extremely important. Having too many people insert rows into the same block was a constant fear.

> Unless you have
> a large enough memory to buffer most of the leaf blocks of most of the
> indexes,

You will probably not be surprised but in these days of gigantic memories, that is very frequently the case, especially if the DBA is using STATSPACK as a tuning tool. You tend to see cache hit ratios of 99.99% in spreport.txt whenever that is the case. You'd probably be surprised to learn how many of DBA's do not have a clue about event based tuning and response time tuning and are still using BCHR as an important metrics for measuring the system performance.

> 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.
>

Oh, no doubt about that, but negative impact of indexes was an important boogey man in the tales of the Oracle crypt. In reality, there are many other factors that will hit an application before over-indexing. C'ya on May 16th.

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