Re: Perfomance Help

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
Date: 1995/09/17
Message-ID: <811370966snz_at_jlcomp.demon.co.uk>#1/1


In article <43euak$aig_002_at_ujf-grenoble.fr>

           Gilles.Bruno_at_ujf-grenoble.fr "Gilles Bruno" writes:

: your table MUST be reorganized - chained rows are always a perf. bottleneck
: (they imply 2 i/o insted of one for each chained rows... Try to figure out
: the impact on table-scans...)

    ^^^^^^^^^^^^^^^^^^^^^^^^^

Pardon me for wandering away from the original question, but remember that there are two types of 'chaining' in v7.

Where rows are longer than the block-size, a single row will be split across blocks. This is the v7 definition of chaining.

Where a row is smaller than a single block but an update to that row would not allow it to remain in the block, it is moved to a new block in its entirety but a row-entry (i.e. the original rowid) is left in the original block. Under v7 this is known as migration rather than chaining.

Unfortunately the various stats reports on tables use the term chaining to cover both types of structure.

Enough intro...
The reason I mention this is that migration (you really need long columns before you hit chaining problems) does not impact on tablescans  it only impacts on indexed-accesses.

On an indexed access, the index supplies the original row location, which then supplies the new row location. On a tablescan, Oracle 7 simply ignores row entries that are pointing to other row entries and picks up the pointed-to row when it gets to that block.

Gilles is, of course, right: finding 6,000 migrated rows in a 30,000 row (or even 200,000 row) table is worth a little investigation.

-- 
Jonathan Lewis
Received on Sun Sep 17 1995 - 00:00:00 CEST

Original text of this message