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 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 - 08:56:32 CDT