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

A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com> (if that email address didn't require changing) On Sun, 20 Jun 1999 20:25:23 -0400, you wrote:

>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?
>

deallocate unused knocks out things that *haven't been used* -- not overlap the hwm. these are extents beyond the hwm.

truncate can reset the hwm.
exp/drop/imp will reset it (not really since its a brand new object, its not a reset).
create table as select can do the same as exp/drop/im

alter table ... MOVE .. (new in Oracle8i) can do it (alter table move is very cool, no more exp/imp to fix freelists and the like)

bouncing the database has nothing to do with it. the space is not wasted -- its just currently not filled with data. wasted space would be space you could never use for anything. this space (space below the hwm that is not currently used) will eventually be used if you fill the table back up again.

>
>- 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
>
>

--
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:55:54 CDT