Re: High Water Mark (was Re: Large deletes (Help))

In article <1993Sep10.010729.11789_at_qiclab.scn.rain.com> tcox_at_qiclab.scn.rain.com (Thomas Cox) writes:

[stuff deleted]

   In v7, a large table that has been shrunk down because of deletions will    have any unused extents (or is that segments?) de-allocated. However,    if the table is fragmented and there are some rows scattered around such    that no extents are actually empty, then nothing would get freed up --    so you would indeed want to de-frag the table in that case.

Unfortunately, this is *not* true. A segment is just a synonym for a table, index, cluster, rollback-segment, temporary-segment, etc. E.g. any database object which requires storage (contrary to things like stored procedures, views, synonyms, etc, which are stored in *rows* in Data-Dictionary tables). All segments have one thing in common: they can grow by allocating new extents (collections of contigous blocks). So a segment may consist of a collection of extents scattered over a tablespace and even different datafiles  (if the tablespace has multiple files). The consequences of having a "fragmented" segment is as follows:

• you might exceed the maximum number of extents (depends on blocksize) forcing you to reorganize. Check dba_segments.
• you might fragment free space in the tablespace, making it impossible for other segments, which require large extents, to grow. Check dba_free_space.
• there is no impact on performance when accessing a fragmented table through an index. The index contains rowids.
• there is no impact on performance when scanning a fragmented table or index. If your disk is not hardware-striped or mirrored, the disk-heads will move a couple of extra times. Big deal.

When a segment is deleted (table, index, ... is dropped), all of its extents are returned to the tablespaces freespace, and may be re-used by other segments. Extents are *never* de-allocated from a segment. There is only one exception to this rule: in Oracle7 a rollback-segment may have an optimal size. If it grows beyond the optimal size, it *will* de-allocate extents as soon as they are not needed any more. This de-allocating of extents does not work for any other type of segment, e.g. tables.

The consequences of deleting rows from a table are as follows:

• the blocks containing rows marked for deleting, will get cleaned out during the next query. The space occupied by these rows is returned to the blocks freespace, and may be reused by other (new) rows. (hint hint: do a full-table-scan after massive deletes to enable immediate space-reusage)

The consequences of deleting *many* rows from a table are as follows:

• the table-segment may contain many blocks, which are only partially filled with row-data. This means that a full-table-scan must read as many blocks as was needed *before* deleting rows, but fewer rows will be found. That *is* a performance hit.
• if rows were deleted at the "end" of the segment, a full-table- scan will continue to read the now empty blocks up to the "high-water-mark". That *is* a performance hit.

Both of these problems will get remedied automatically if the deleted rows are replaced with new rows. But if they are not replaced (you accidently loaded a million rows and then deleted them), you can only remedy this situation by physically re-organizing the rows in the table. Currently there is no utility which will do this (you have to create a new table). Maybe something to ask for at IOUW in Orlando ?

PS: Everything described above is true for Oracle v6 and Oracle7, except the part about optimal size of rollback-segments, which is only true for Oracle7.

"Keep those segments organized!"

Magnus Lonnroth
Tech. Sales & Consultant
Oracle Sweden
Mail: mloennro_at_oracle.com

[My opinions are not supported] Received on Fri Sep 10 1993 - 02:16:14 CEST