Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Export times: affected by size of table or amount of data?

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

From: Doug Cowles <dcowles_at_bigfoot.com>
Date: Sun, 20 Jun 1999 20:53:07 -0400
Message-ID: <376D8CF3.2376BAE6@bigfoot.com>


One more thing.. alter drop unused?
Better?

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:53:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US