Re: Export times: affected by size of table or amount of data?

QTFM:
"Unused space is deallocated from the end of the object toward the high-water mark at the beginning of the object. If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent, and the remaining unused space is freed for reuse."

In other words, you can never DEALLOCATE any space below the HWM. The DEALLOCATE UNUSED only deallocates never-used!

Doug Cowles wrote:

> Speaking of which, will a script that runs "deallocate unused" on all the tables
> every so often get rid of this high water mark problem?
>
> I tried the following -
> A) Packed a table with 250,000 rows
> B) deleted all of them (couple minutes)
> C) select count(1) from bigtable (noticable delay) - scanning to HWM
> D)alter table bigtable deallocate unused;
> E) select count(1) from bigtable - pretty quick, but not instant. (Answer= 0)
> F) Then tried - truncate table bigtable drop storage;
> G) select count(1) from bigtable - instant response
> Question is 2 fold - 1) Why does truncate on an empty table cause faster response
> than deallocate unused, when that includes the whole table and
> 2) Would it be a good idea to deallocate unsed on a nightly basis.
>
> - Dc.
>
> Thomas Kyte wrote:
>
> > A copy of this was sent to Andy Hardy <aph_at_ahardy.demon.co.uk>
> > (if that email address didn't require changing)
> > On Tue, 15 Jun 1999 14:28:29 +0100, you wrote:
> >
> > >Hi,
> > >
> > >If I declare a 100Mb table with no data, will it export in less time
> > >than the same table full of data?
> > >
> > >I thought that export times were only dependent on the quantity of data
> > >stored, but my customer is telling me otherwise...
> > >
> > >Andy
> >
> > it'll depend. If the 100mb table HAD data at some point and was deleted from --
> > we will scan to the high water mark looking for data. export tends to full scan
> > tables -- if a select count(*) against that empty table doesn't come back
> > immediately -- neither will export.
> >
> > See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
> > Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
> >
> > Current article is "Fine Grained Access Control", added June 8'th
> >
> > 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 Sat Jun 19 1999 - 21:32:31 CDT