Re: Insert above HWM

"news1.trim.net" <chumly_at_jg2.org> wrote in message news:<wtqO7.8$0G6.2768_at_breeze>...
> I am consolidating a number of tables into a master table and I was planning
> to use
> INSERT /*+ APPEND */ into a (select (columns) from b)
>
> I was planning to create the new tablespace NOLOGGING and not create the
> index till all is complete. At which time I could alter tablespace logging.
> There are approx 3 million records involved.
>
> I understand that the insert will direct oracle to insert rows above the
> high water mark rather that blocks that are on the freelist.
>
> My question is do I need to export truncate and import this new table to set
> the HWM correctly? Or will that be done upon the first new regular insert
> into this table?
>
> Thanks for your help
> Barry

Oracle will reset the HWM as the inserts are done. Individual inserts after your loads will go into available blocks per the freelist(s).

If you export, truncate, and re-import the HWM will be set almost immediately after the last used block. I believe that Oracle rounds the location of the HWM to the nearest higher block evenly divisible by 5 so that in fact there may me 1 - 4 unused blocks between the last inserted row and the indicated position.

• Mark D Powell --