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:53:07 -0400, you wrote:

>One more thing.. alter drop unused?
>Better?
>

No. That is used in oracle8i to drop columns that have been set unused. it goes through and reclaims space on each block consumed by a column that is no longer needed. it does not compress space in a table (rows are not migrated nor do their rowids change) and the extent allocation for the table is left unaffected.

>- 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 - 20:18:55 CDT