Re: Large table and buffer cache concerns

From: <devalos_at_gmail.com>
Date: 15 Apr 2007 14:57:18 -0700
Message-ID: <1176674238.903424.326410@e65g2000hsc.googlegroups.com>

> Some of the discussion in this thread suggests that your code may,
> however, expect to update all 1,000 rows simultaneously because
> of the nature of the application - or possibly the implementation.
> This is potentially a more serious performance threat than the basic
> co-location issue because of the overhead in redo generation.

Mr. Lewis,

Thank you for you contributions to the discussion.

A single customer is only ever handled by a single application server, so there should not be situations in which multiple application servers are attempting to access a single block assuming that block only contains data for a single customer.

As for the updates, a customer/video record is updated alone, no bulk updates per customer are performed.

Much like Raoul, I'd be interested in hearing your experiences in working with IOTs vs single table b*tree cluster tables.

My impressions thus far contracsing the two: IOTs reduce storage and Disk I/O more so than a single table b*tree cluster. (no table access I/O and compression of the key) IOTs can be rebuilt and secondary indexes are still usable if not a bit slower until rebuilt. Moving a cluster table invalidates the secondary indexes.
A cluster might be in order if the candidate table's records (row size) require the use of the IOT's overflow as the overflow detracts from some of the savings inherent by using an IOT. One may be able to create a reverse key index upon a cluster tables cluster key to alleviate a hot "right edge" index leaf block.

I am still unclear if the IOT's leaf block splits require any additional overhead in comparison to that of the cluster key of the cluster table. I would suspect the IOT to split more often as it is less densly packed than the cluster tables, cluster key. Received on Sun Apr 15 2007 - 16:57:18 CDT