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

If I'm reading you guys right, deallocate unused essentially only knocks out things that overlap the hwm and are empty on the other side or are just over it and have nothing in them? In that case, the only way to solve the problem of the hwm is a truncate, or a reorg with compress, also, and this is the first question, if you bounce the database? If you shutdown and startup, that wasted space is gone, no?

• Dc.

Thomas Kyte wrote:

> A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
> (if that email address didn't require changing)
> On Sat, 19 Jun 1999 21:57:28 -0400, you 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?
> >
>
> no it won't. only space that has never been used (stuff above the hwm) is freed
> using this.
>
> >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
> >
> >
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> 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 Sun Jun 20 1999 - 19:25:23 CDT