| direct insert and referential integrity constraints [message #272661] |
Fri, 05 October 2007 19:09  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Dear all!
Hope you fine.
I want to know if a table have a fk ，when we do
insert /*+ append */ into t select ....
does oracle ignore the hint and use traditional insert or not?if not ,please explain how does oracle check the referential integrity.
Regards!
Alan
|
|
|
|
|
|
| Re: direct insert and referential integrity constraints [message #272663 is a reply to message #272662] |
Fri, 05 October 2007 19:50  |
alantany
Messages: 115
Registered: July 2007
|
Senior Member |
|
|
Thanks,Sir.
But I find this from oracle 10g DOC:
| Quote: |
With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
...
The target table cannot have any triggers or referential integrity constraints defined on it.
...
|
Please notice the last sentense,does it mean if there is a FK on the table ,oracle will ignore the hint and does a traditional insert?
Regards!
Alan
[Updated on: Sat, 06 October 2007 00:41] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: direct insert and referential integrity constraints [message #272680 is a reply to message #272665] |
Sat, 06 October 2007 01:34 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Just a small test case:
SQL> create table t1 as select object_id, owner, object_name, object_type from dba_objects;
Table created.
SQL> create table t2 as select * from t1 where 1=2;
Table created.
SQL> update t1 set object_id=1000000+rownum where object_id is null;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table t1 add primary key (object_id);
Table altered.
SQL> @mystat 'physical writes direct'
NAME VALUE
---------------------------------------- ----------
physical writes direct 917
SQL> insert /*+ append */ into t2 select * from t1;
47769 rows created.
SQL> @mystat 'physical writes direct'
NAME VALUE
---------------------------------------- ----------
physical writes direct 1237
SQL> truncate table t2;
Table truncated.
SQL> alter table t2 add foreign key (object_id) references t1 (object_id);
Table altered.
SQL> @mystat 'physical writes direct'
NAME VALUE
---------------------------------------- ----------
physical writes direct 1237
SQL> insert /*+ append */ into t2 select * from t1;
47769 rows created.
SQL> @mystat 'physical writes direct'
NAME VALUE
---------------------------------------- ----------
physical writes direct 1237
Regards
Michel
[Updated on: Sat, 06 October 2007 07:31] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
