Re: Table Fragmentation

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 05 Jul 1999 14:01:21 GMT
Message-ID: <3784b08f.5171596@newshost.us.oracle.com>

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Sun, 4 Jul 1999 20:59:01 +0100, you wrote:

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

agreed.

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

yes and you caught my error! In my test, as it turns out, i didn't CHAIN every row -- i apparently MIGRATED every row.

I reran the test with a CHAINED table. the original question was "Agreed but I think your post gives the impression that full table scans ("hundreds of fetches") are affected by chaining"

This test below shows that full scans can be affected by chaining (massively). This one is correct (in that is demonstrates the issue where as the previous post was interesting but didn't answer the question :)

SQL> create table test ( id1 int,
2 filler1 char(2000), filler2 char(2000), filler3 char(2000), filler4 char(2000),
3 id2 int );

Table created.

this table is 'special'. I have an 8k blocksize. with non-null values in each column, this row must span blocks in my system. YMMV -- for smaller blocksizes adjust. For oracle7 -- use more fillers as char(2000) won't fly in those releases.

SQL> insert into test select rownum, 'a', 'b', 'c', null, null from all_objects 2 where rownum < 500
3 /

499 rows created.

fill up ~500 rows. put in about 6k of data. this should get 1 row per block

SQL> commit;
Commit complete.

SQL> analyze table test list chained rows into chained_rows 2 /
Table analyzed.

SQL> select count(*) from chained_rows
2 /

COUNT(*)

so, no rows are chained -- this is what we expect...

SQL> update test set id2 = -id1, filler4 = 'd' 2 /

499 rows updated.

SQL> delete from chained_rows
2 /

0 rows deleted.

SQL> analyze table test list chained rows into chained_rows 2 /

Table analyzed.

SQL> select count(*) from chained_rows
2 /

COUNT(*)

499

Now, every row is chained -- and I'm pretty sure they are chained -- not migrated this time :)

SQL> disconnect
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> connect tkyte/tkyte
Connected.
SQL>
SQL> select count(*) from test
2 /

COUNT(*)

499

SQL>
SQL> select a.name, b.value
  2 from v$statname a, v$sesstat b
  3 where a.statistic# = b.statistic#
  4 and a.name like 'table %'
  5 and sid = ( select sid from v$session where audsid = userenv('sessionid') )   6 and b.value > 0
  7 /

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten                501
table scan blocks gotten             1001
table fetch by rowid                    5

count(*) works as expected. no fetch continued rows counted. just a nice fast full scan.

COUNT(*)

499

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten                501
table scan blocks gotten             1001
table fetch by rowid                    5
table fetch continued row             499

6 rows selected.

Now, we see how a chained row can affect a full scan. in order to resolve the predicate -- each row forces a table fetch by continued row.

COUNT(*)

499

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten                501
table scan blocks gotten             1001
table fetch by rowid                    5

this one doesn't for the continued row fetch. It finds the first row piece and discovers it can resolve the predicate with it and ignores the 2cnd row piece.

COUNT(*)

499

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten                501
table scan blocks gotten             1001
table fetch by rowid                    5
table fetch continued row             499

6 rows selected.

SQL> spool off

Here this shows the table fetch continued row again. We don't find id2 in the first row piece, we do a fetch continued row to get it and go on.

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

see, i said my first post was interesting (too bad it didn't answer the question tho :)

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

agreed. thats what my first tests showed..

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

correct, thats what this one clearly shows.

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

Apparently not:

COUNT(*)

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten              15483
table scan blocks gotten            30968
table fetch by rowid                    5
table fetch continued row           15481

6 rows selected.

SQL> disconnect
SQL> connect tkyte/tkyte
Connected.

SQL> select count(*) from test where id2 > 0 and id1 < 0;

COUNT(*)

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              4
table scans (long tables)               1
table scan rows gotten              15483
table scan blocks gotten            30968
table fetch by rowid                    5
table fetch continued row           15481

6 rows selected.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jul 05 1999 - 09:01:21 CDT