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

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Sat, 19 Jun 1999 21:57:28 -0400
Message-ID: <376C4A87.DECCC224@bigfoot.com>

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.

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 - 20:57:28 CDT