Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Export times: affected by size of table or amount of data?
On Tue, 15 Jun 1999 19:20:32 +0100, Andy Hardy
<aph_at_ahardy.demon.co.uk> wrote:
>In article <3769872b.7762822_at_newshost.us.oracle.com>, Thomas Kyte
><tkyte_at_us.oracle.com> writes
>>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.
>
>OK. So if the table is set-up with a large initial extent and has ever
>contained some data, a full scan will be done.
Well, full scan will be performed in any case. The problem is how long will it take a full scan to complete. Full scan will scan each and every block from the first block of the first extend up to the block where the high water mark is set at the moment.
So, if all the data of the huge table has been DELETEd, the full table scan will still scan all the blocks up to the HWM, and this might take some time. But if the table was TRUNCATEd, it will end instantaneously, as the HWM has been reset to the first block of the first segment.
>Andy
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)