Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert /* +append */ always ?
As usual Mr. Kyte is correct! (Would we have it any other way?)
I must apologize for my mis-information. I was assuming that the table was set to nologging when I stated that redo is not generated. I did not realize direct path insert would work when the table was set to logging (The default). My Bad!
This is my lesson for the day. Thanks Tom!
Per the 8i concepts manual:
Also I admit my wording was poor when I was trying to explain that blocks on the freelist would not be reused when using direct path load.
Once again from the 8i concepts manual:
Steve
Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<ab1phu01fpa_at_drn.newsguy.com>...
> In article <ud8onhkf0meg1d_at_corp.supernews.com>, "Steve says...
> >
> >No.
> >The append hint does not generate redo log information.
>
> Sure it does! It is 100% safe. It is "undo" that it skips. It'll skip REDO if
>
> o you are no archivelog mode (doesn't matter in this case, no media recovery
> anyhow)
>
> o your table is nologging
>
> consider (in an archivelog mode database)
>
> ops$tkyte_at_ORA8I.WORLD> create table t as select * from all_objects where 1=0;
>
> Table created.
>
> ops$tkyte_at_ORA8I.WORLD>
> ops$tkyte_at_ORA8I.WORLD> set autotrace on statistics
> ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
>
> 34950 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 643 recursive calls
> 119 db block gets
> 476174 consistent gets
> 270 physical reads
> 3895776 redo size <<<<<<<<<<<<<<<<=========================
> 991 bytes sent via SQL*Net to client
> 827 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 15 sorts (memory)
> 0 sorts (disk)
> 34950 rows processed
>
> ops$tkyte_at_ORA8I.WORLD> drop table t;
>
> Table dropped.
>
> ops$tkyte_at_ORA8I.WORLD> create table t NOLOGGING as select * from all_objects
> where 1=0;
>
> Table created.
>
> ops$tkyte_at_ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
>
> 34950 rows created.
>
>
> Statistics
> ----------------------------------------------------------
> 151 recursive calls
> 116 db block gets
> 476062 consistent gets
> 24 physical reads
> 10208 redo size <<<<<<<<<<<<<==============================
> 994 bytes sent via SQL*Net to client
> 827 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 34950 rows processed
>
>
>
> >So the only time you should use it is when you can afford
> >to loose the data in the table.
>
> AND the table is NOLOGGING needs be added here. APPEND does not by itself
> bypass redo -- undo, and the related redo for the undo, YES. REDO -- no.
>
> In NOarchivelog -- append bypasses redo, but that doesn't matter.
>
> > Once your next hot or cold
> >is taken than you off course are no longer at risk.
> >
> >So if you are loading right before a backup the risk is minimal. The longer
> >you wait the bigger the risk.
> >
> >Also you need to be aware that when you use the append hint the data is
> >inserted beginning at the high water mark of the table. So you have just
> >deleted a bunch of data those blocks will not be reused. If the tables was
> >just truncated than it will start loading at the beginning.
> >
>
> I don't understand the "so you have just deleted a bunch of data those blocks
> will not be reused". I think you were trying to say "the direct path insert
> will not reuse blocks that are on the freelist"
>
>
>
> >Hope this helps.
> >
> >Stephen C. Ashmore
> >Brainbench MVP for Oracle Administration
> >http://www.brainbench.com
> >
> >Author of 'So You Want to be an Oracle DBA?'
> >
> >"R Chin" <rchin_at_panix.com> wrote in message
> >news:ab1l09$94b$1_at_reader1.panix.com...
> >> Should I ALWAYS do
> >> Insert /* +append */ into select .....
> >> as opposed to
> >> Insert into select...... ???
> >>
> >> Under what circumstances should I NOT use the hint ?
> >>
> >> Thanks
> >>
> >> Rob
> >>
> >>
> >>
> >
> >
Received on Mon May 06 2002 - 06:52:59 CDT
![]() |
![]() |