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