Re: Table Fragmentation

A copy of this was sent to Connor McDonald <connor_mcdonald_at_yahoo.com> (if that email address didn't require changing) On Thu, 01 Jul 1999 22:38:05 +0800, you wrote:

>Thomas Kyte wrote:
>>
>> A copy of this was sent to pauldb <luapdb_at_yahoo.com>
>> (if that email address didn't require changing)
>> On Thu, 01 Jul 1999 02:39:31 -0800, you wrote:
>>

[snip]

>
>Agreed but I think your post gives the impression that full table scans
>("hundreds of fetches") are affected by chaining - and I thought that a
>full scan just went from start block to hwm - and thus wasn't affected
>by chaining...
>

they can and are in many (but not all) cases.

consider:

select * from T where x = 'x' and y = 'y';

So, that will full scan T. In order to be able to resolve the predicate -- we must have the full row (or at least x and y and lets say that x is on one block and y is chained to another). In order to get the row, we must get all|some of the blocks the row is on. In order to do that -- we must perform scattered reads to reconstruct the row. When we read X blocks in a read, we might have to do N more scattered IO's to reconstruct the row. Row chaining really can affect full table scans negatively.

I did a quick test. Its long but the gist is:

• created a table that looks like all-objects, pctfree=0
• insert into that table only object id's
• update every row with the rest of the data
• analyze list chained rows -- see that most every row is in fact chained
• disconnect
• connect
• count the rows in test with a where clause on the last column and object id
• look at v$sesstat to see what kind of IO we've done

do the same disconnect/reconnect and query by last column

do the same disconnect/reconnect and query by object id

The first count (with columns across blocks) does full scan PLUS lots of table fetch continued row

The second count does a full scan, and no table fetch continued row

the third query does a full scan, and no table fetch continued row

SQL> connect tkyte/tkyte
Connected.

SQL> select count(*) from test where secondary is not null and object_id > 0   2 /

  COUNT(*)

     15461

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 scan rows gotten                  2
table scan blocks gotten                2
table fetch by rowid                15466
table fetch continued row           15207

SQL> connect tkyte/tkyte
Connected.
SQL> select count(*) from test where secondary is not null   2 /

  COUNT(*)

     15461

SQL> select a.name, b.value....

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              5
table scan rows gotten              15463
table scan blocks gotten              218
table fetch by rowid                    5

SQL> connect tkyte/tkyte

SQL>
SQL> select count(*) from test where object_id+0 > 0   2 /

  COUNT(*)

     15461

SQL> select a.name, b.value....

NAME                                VALUE
------------------------------ ----------
table scans (short tables)              5
table scan rows gotten              15463
table scan blocks gotten              218
table fetch by rowid                    5

SQL>
SQL> spool off

As you can see -- its going to depend on WHAT YOU FETCH (how many columns do you need? we might need to assemble that from many blocks all over the place) and WHAT YOU PREDICATE on -- if the columns span blocks due to chaining = we have to assemble all of the needed row pieces.

>
>
>Cheers
>Connor

--
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 Fri Jul 02 1999 - 12:18:28 CDT