Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Tue, 12 Apr 2011 02:05:13 -0700 (PDT)
Message-ID: <00d83f5f-92c9-4eb9-a2b8-e2c418d3f6a3_at_a26g2000vbo.googlegroups.com>
As Randolf says,
create table as select ...
clean up the blocks.
The dbms_stats will clean the block after an insert (conventional).
I would add two things to what has been said by Randolf
A table created with insert / * + append * /, has the blocks of the
table
with the "ITL clean" but if there are indexes, the indexes will have
the "dirty ITL, will be
therefore necessary (delayed) block cleanout
select / * + index (.....) * / from table;
because the queries that follow,
if they have a where, probably, will access to the indexes.
Instead, for "Direct Reads"mentioned Randolf,
that happen (probably when it exceeds the threshold
of _small_table_threshold), it is worth adding that,
when Oracle decides to use them, can not do cleanout, the blocks
will be in the PGA, and not SGA. It will be necessary in fact, that
DBWR writes those blocks
on disk, so that the following reading may make the cleanout.
Regards
Donatello Settembrino Received on Tue Apr 12 2011 - 04:05:13 CDT