Re: Table Fragmentation

This is a topic worth at least a good
two or three seminars, if not a full
day tutorial.

Think about the implications of rebuilding the table (as some dba's do from time to time in that peculiar search for the single- extent database ;) What do you do with PCTFREE when 30% of the rows are
new at 50 bytes, 40% are have used at
800 bytes and the rest are old at 2000
bytes ?

There is also the question of how the final size of the rows might affect the choice of optimum block size.

Then there is the important distinction to be made between row chaining and row
migration.

I spotted the table access by index in your first set of stats - not by tablescan. It made me do a couple of quick tests of my own though:

If a row is migrated (i.e. you grow it, and it is still small enough to fit a single block, but too big to fit in the current block then the WHOLE ROW is migrated, and only
a forwarding rowid is left behind.

In this case a tablescan testing the first and last columns will not have to acquire the row header and do a chained row fetch to the tail.

If the row extension makes it too large to fit a single block at all, then as much as will fit in the current block is left behind along with a forwarding rowid, and the rest is copied into a further block (or more).

In this case a tablescan checking the
values of columns in the two pieces
will have to do a continuation fetch.

(TEST QUESTION (and I haven't
checked the answer yet): if the first test applies to the first piece or the row
and causes the row to be rejected
will this avoid the continuation test ? In which case for an unindexed tablescan on a table with properly chained rows you should put the test on the earlier columns in the table at the end of the where clause. (see my article on ordering where clauses on unindexed queries).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

>NAME VALUE
>------------------------------ ----------
>table scans (short tables) 4
>table scan rows gotten 2
>table scan blocks gotten 2
>table fetch by rowid 15466
>table fetch continued row 15207
>
>
Received on Sun Jul 04 1999 - 14:59:01 CDT