Re: Preventing delayed block cleanout due to loading a datawarehouse

From: Donatello Settembrino <>
Date: Tue, 12 Apr 2011 02:05:13 -0700 (PDT)
Message-ID: <>

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.


Donatello Settembrino Received on Tue Apr 12 2011 - 04:05:13 CDT

Original text of this message