it isn't an optimizer hint really truy - it doesn't change the plan.
/*+ APPEND */ only works if
a) there are no triggers
b) there is no RI regarding this table in effect
c) you used "as select", not values
otherwise it was silently ignored.
ops$tkyte%ORA11GR2> create table p ( x int primary key );
Table created.
ops$tkyte%ORA11GR2> create table c ( x references p );
Table created.
ops$tkyte%ORA11GR2> insert into p values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> select * from p;
X
----------
1
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> insert /*+ APPEND */ into p values ( 2 );
1 row created.
ops$tkyte%ORA11GR2> select * from p;
X
----------
1
2
ops$tkyte%ORA11GR2> commit;
Commit complete.
<b>in a conventional path insert, you can select from the table right
after modifying it. Not so with direct path:</b>
ops$tkyte%ORA11GR2> insert /*+ APPEND */ into p select 3 from dual;
1 row created.
ops$tkyte%ORA11GR2> select * from p;
select * from p
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
<b>so, if we direct path for real - we cannot from it select until we commit...</b>
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> create or replace trigger p
2 before insert on p for each row
3 begin
4 dbms_output.put_line( 'hello world' );
5 end;
6 /
Trigger created.
ops$tkyte%ORA11GR2> insert /*+ APPEND */ into p select 4 from dual;
hello world
1 row created.
<b>that shows trigger prevents direct path</b>
ops$tkyte%ORA11GR2> select * from p;
X
----------
1
2
3
4
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c select 1 from dual;
1 row created.
ops$tkyte%ORA11GR2> select * from c;
X
----------
1
ops$tkyte%ORA11GR2> commit;
Commit complete.
<b>and that shows if you have RI, you cannot direct path with insert append</b>