Re: Q: Imports

Jonathan Lewis (Jonathan_at_jlcomp.demon.co.uk) wrote:
: In article <3sbc04$ens_at_bell.ukonline.co.uk>
: chris_at_perihelion.co.uk "Chris Selwyn" writes:
 

: :
: : The time appears to be going in the creation of the hash cluster : not in the
: : population of it. Any ideas?
: :
 

: Okay, theory B:
 

: When you drop a table, only the dictionary information is deleted, the
: data blocks themselves are unchanged. Similarly, when you create a
: table, the datablocks that go into that table are not changed, other
: than the segment header block and the first datablock of the table.
 

: For tables this is safe because the segment header block maintains a
: list that says things like the 'high water mark' and free lists, so
: that Oracle knows which blocks have been touched by actions for that
: table. When a previously untouched block in the table is first
: used, it is blanked, and correct table/block header information is written
: to it.
 

: In the case of a HASH Cluster, inserts are completely randomly posistioned,
: so there is no conept equivalent to the 'highwater mark', so when visiting
: a block, Oracle cannot determine whether or not it is the first visit for
: that hash cluster. My hypothesis then is that when you create a hash cluster,
: every single block of the hash cluster is cleaned out as part of the creation
: process. (Mind you, several hours still seems too long for 150M, although
: I guess that redo log would have to be generated for this too).

I think this is closer to the mark but it still seems a long time.

Using 'monitor' I was measuring the transfers to/from the disk at ~500K/sec. To write each block of 150M at this rate would take 5 mins not 45+!  

--
Chris Selwyn
Senior Consultant
Perihelion Software
email: chris_at_perihelion.co.uk tel: +44 1749 344203