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

>Yes, this is true. But only for access using the index. The table may still
>be "slow" during full-table-scans also. This is because every table-segment
>contains a "high-water-mark" pointing to the highest block-address, which
>has ever contained data. Full-table-scans continue to read up to the high-
>water-mark regardless if the table contains any rows or not. Even if you
>deleted every row in the table, every block up to the high-water-mark would
>be inspected during a full-table-scan. When you truncate a table, you are
>just moving the high-water-mark pointer to the beginning of the segment.
>That's why it's so fast. You need to reorganize the table in order to
>compress all rows towards the beginning of the segment (and use each block
>effectively), and move the high-water-mark. You can do this with export/import
>or by creating a new table with the rows you want to keep (instead of
>deleting the ones you want to get rid of), dropping the old table, and
>renaming.

Thanks to everyone for this thread; I'll be putting parts of it into the VLDB FAQ. As for the above comments, they are mostly true, I think.

Some clarifications:

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.

To understand how space is used in an Oracle v7 database, look at Part III of the _ORACLE7 Server Concepts Manual_, especially chapter 3, "Data Blocks, Extents, and Segments".

Cheers.

 -Tom

-- 
Thomas Cox      DoD #1776   '91 CB 750 Nighthawk   tcox_at_qiclab.scn.rain.com
    Worldcon:  Arkham in '98.