Home » RDBMS Server » Server Administration » direct insert and referential integrity constraints
direct insert and referential integrity constraints [message #272661] Fri, 05 October 2007 19:09 Go to next message
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 #272662 is a reply to message #272661] Fri, 05 October 2007 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>if not ,please explain how does oracle check the referential integrity.
Oracle does the referential check correctly.
Tom Kyte gave a detailed explanation of a similar operation involving BULK LOAD; which is essentially an insert /*+ append */ .
IIRC the data is loaded above the HWM & a "mini-index" is created & maintained for the new rows by themselves.
After the data has been completed loaded, the old & the new indexes are "merged".
If there no errors, the HWM is adjusted.
If a constraint violation occurs, the changes to the index are rolled back & HWM left as original.
Re: direct insert and referential integrity constraints [message #272663 is a reply to message #272662] Fri, 05 October 2007 19:50 Go to previous messageGo to next message
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 #272664 is a reply to message #272661] Fri, 05 October 2007 20:05 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
I honestly won't spend my time trying to quantify any answer.
It was what it is. As long as data integrity is maintained, I have other issues where my time will be better spent.
Re: direct insert and referential integrity constraints [message #272665 is a reply to message #272664] Fri, 05 October 2007 20:14 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Thanks ,Sir.
But I just want a confirm.
Regards!
Alan
Re: direct insert and referential integrity constraints [message #272680 is a reply to message #272665] Sat, 06 October 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: direct insert and referential integrity constraints [message #272702 is a reply to message #272680] Sat, 06 October 2007 05:25 Go to previous message
alantany
Messages: 115
Registered: July 2007
Senior Member

The test is cool~~
So ,yes,in this case,oracle will ignore the hint and does a traditional insert.
Thanks,Michel!
Regards!
Alan
Previous Topic: can i have another instance in the server
Next Topic: Uninstall Oracle prob.
Goto Forum:
  


Current Time: Tue Dec 06 04:08:26 CST 2016

Total time taken to generate the page: 0.08544 seconds