Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Marie-Christine.

Asked: May 29, 2008 - 11:01 am UTC

Last updated: July 30, 2012 - 7:58 am UTC

Version: 10.0.0

Viewed 1000+ times

You Asked

i created a test table (id), it has no index no pk, i have noarchive log mode,
i'm executing the following:
alter table test nologging;
insert /*+append*/ into test values (4);

then when i retrieve the redo size after each insert, it's always increasing!Why what's missing???


here is how i'm retrieving the redo size:
select a.name,
b.value
from v$statname a,
v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size';

and Tom said...

insert /*+ append */ only works with

insert /*+ append */ into t SELECT ......

never never never with

insert into t VALUES


which is a really good thing. Imagine if it did work - the insert would never reuse any existing space, it would place one row on the block and no one else would ever ever use it. It would be useless.


And you'd have redo generated for any indexes regardless.

And for a single row, the redo is - well - just the row, not very much at all - so you'd be saving approximately NOTHING because we would still have to log the changes to the dictionary that we did to advance the high water mark.

And you'd serialize, you'd make it so that one transaction at a time inserts into this table, as direct pathing is supported for one person at a time.



funny thing that - you would generate more redo if it worked...


ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> alter table t nologging;

Table altered.



ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                   39448

ops$tkyte%ORA10GR2> insert /*+ append */ into t values ( 4 );

1 row created.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
         4

<b>that shows append was ignored - we wouldn't be able to read the table if it was appended...</b>

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                   40532            1,084

<b>remember that value...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                   40532

ops$tkyte%ORA10GR2> insert /*+ append */ into t select 4 from dual;

1 row created.

ops$tkyte%ORA10GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

<b>Ok, we appended this time...</b>

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                   44516            3,984

<b>that would be more....</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                   44516

ops$tkyte%ORA10GR2> insert into t select 4 from dual;

1 row created.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
         4
         4
         4

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                   45188              672

<b>the amount of redo will vary - but for this single row, it'll be about 400-700 bytes of redo overall</b>

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                   80068              468

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "redo size"
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE
---------------------- ----------
redo size                   80068

ops$tkyte%ORA10GR2> insert into t values (4 );

1 row created.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
         4
         4
         4
         4
         4

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
redo size                   80608              540



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

New feature coming with Oracle 11g Release 2

Frank Puechl, December 18, 2009 - 10:10 am UTC

The new Oracle 11g Release 2 can solve your problem. You can use the new optimizer hint APPEND_VALUES. However, as Tom already mentioned, it makes only sense in combination with bulk operations.
Tom Kyte
December 18, 2009 - 12:42 pm UTC

correct (and there is a bug in 11gr1 that accidentally allows an append hint to be used and followed with insert values :( )

why the data can't be read, if inserted with append hint?

raju, July 20, 2012 - 10:23 am UTC

Surprised to know that data inserted with APPEND hint can't be read. Then what is the use of inserting data that can't be read? May be it can't be read in the same session, but other sessions can read it?
Please clarify? or it can't be readi, until the change is commited?
Tom Kyte
July 30, 2012 - 7:58 am UTC

you use append to direct path load data, bulk load data, like into a warehouse.

it is a special option for a special task, it is not for everyday use, it is very very special.


other sessions cannot read it either - it isn't committed!!!!! how could they see it, it doesn't exist yet.


you cannot read it because there is no undo generated for it, hence - multi-versioning and read consistency are totally "broken" with respect to it - you have to commit it after bulk loading it in order to play with it.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions