Re: Q: Imports

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

-- 
Jonathan Lewis