Home » RDBMS Server » Server Administration » append hint with dblink.
append hint with dblink. [message #274381] Mon, 15 October 2007 21:57 Go to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hello,all:
I know
insert /*+ append */ into t select * from t@dblink

will use a direct insert,but if some one tell me does:
insert /*+ append */ into t@dblink select * from t

do a direct insert?
Regards!
Alan

[Updated on: Tue, 16 October 2007 01:13] by Moderator

Report message to a moderator

Re: append hint with dblink. [message #274432 is a reply to message #274381] Tue, 16 October 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try the exercise I did in http://www.orafaq.com/forum/m/272680/102589/?srch=insert+append#msg_272680.
What is the result?

Regards
Michel
Re: append hint with dblink. [message #274442 is a reply to message #274432] Tue, 16 October 2007 01:40 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Dear Michel:
If I do ,insert /*+ append */ into t@dblink select from t,where should I do you test? local box or remode box?
Regards!
Alan
Re: append hint with dblink. [message #274451 is a reply to message #274442] Tue, 16 October 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is this mean? What local and remote boxes?
You execute on the database you want to execute it in the end.

Regards
Michel
Re: append hint with dblink. [message #274472 is a reply to message #274451] Tue, 16 October 2007 03:24 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Dear,Michel:
I have finished the test,oracle will not use direct insert in this case :
  insert into t@dblink select * from t

Razz
Regards!
Alan
Re: append hint with dblink. [message #274476 is a reply to message #274472] Tue, 16 October 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the full test.
Don't you want to help others?

Regards
Michel
Re: append hint with dblink. [message #274487 is a reply to message #274476] Tue, 16 October 2007 04:00 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hi,Michel:
Sorry ,I cann't get the test message from my db server because my pc is not connect to the db server.
But here is my test step:
DB A
create table t(x int);

DB B
sql>create dblink connect to db1;
sql>insert into t@dblink select object_Id from user_objects;
sql>select * from t@dblink;
Here ,it return the rows ,so it means oracle does a conventional insert --if oracle does a direct insert ,when select before commit,we will get :
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Regards!
Alan
Re: append hint with dblink. [message #274504 is a reply to message #274487] Tue, 16 October 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't use the append hint in your test.

Regards
Michel
Re: append hint with dblink. [message #274545 is a reply to message #274504] Tue, 16 October 2007 06:52 Go to previous messageGo to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Sorry,it is a typing mistake,actually I used.
insert /*+ append */ into t@dblink select object_Id fro t;

Regards!
Alan
Re: append hint with dblink. [message #274640 is a reply to message #274545] Tue, 16 October 2007 12:07 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
Dear Alan,

"Insert /*+ APPEND */ into t@dblink select .." command loads the data using direct inserts. This is fatest way to load the data on hetrogenous platforms. It is very helpful when used with parallel hint.

Caution :
-Use the parallel hint with select only..Do not use with insert..That will create performance problems.

There are my findings...I am already using this to load my test server data from production and its lightening FAST..

Can you please verify you test?

Qustions. Holler. Suggestions.

Regards
Anup
Re: append hint with dblink. [message #274646 is a reply to message #274640] Tue, 16 October 2007 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Use the parallel hint with select only..Do not use with insert..That will create performance problems.

Which ones? Precise!

Quote:

Qustions. Holler. Suggestions.

What does that mean?

Regards
Michel
Re: append hint with dblink. [message #274650 is a reply to message #274646] Tue, 16 October 2007 12:46 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
Use the parallel hint with select only.
Do not use with insert. Inserting the data using parallel hint will create performance problems.

Sorry for the typo, replace 'Qustions' with 'Questions'.

Regards,
Anup
Re: append hint with dblink. [message #274651 is a reply to message #274650] Tue, 16 October 2007 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Inserting the data using parallel hint will create performance problems.

Which ones? Precise!

Quote:

Sorry for the typo, replace 'Qustions' with 'Questions'.

Even with typo corrected I still don't understand what you meant with "Questions. Holler. Suggestions."

Regards
Michel

Re: append hint with dblink. [message #274660 is a reply to message #274651] Tue, 16 October 2007 13:40 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
Do we really need to bother about that? We are techie people and what matters to us is a solution to a problem.

If I can understand your "Precise!", cann't you understand my "Questions, Holler, Suggestions" ?

I got the lot of help/hints from the orafaq to solve my problems and so I am trying to help others. I am sorry to say but I am sick of your 'micro management' for this website. If I need to answer the questions like yours, I am out of here.

And answering your question, your last to reply were 'Hollers'.

-Anup
Re: append hint with dblink. [message #274662 is a reply to message #274660] Tue, 16 October 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You said:
Quote:

Inserting the data using parallel hint will create performance problems.

I ask to precise when, why it will create performances problem.
Is this difficult to understand.
You affirm something.
I ask you to precise your source, post a test case, demonstrate what you affirm.
Will not this be useful for all to know why and when using something will create performances problems?
Do you really expect we trust you just because you say something?
I'm really interesting to know what kind of problems this can be and when it happens. And I'm pretty sure many of readers also are.
Are my thoughts clear now for you?

And my last answers were just to clarify. Not all readers are as smart as you are to understand what is not written and what is missing.

Regards
Michel

Re: append hint with dblink. [message #274698 is a reply to message #274662] Tue, 16 October 2007 19:32 Go to previous message
alantany
Messages: 115
Registered: July 2007
Senior Member

Dear Anup:
Thanks for your kindly replay.
But you see,I have posted my test ,I have proved it to be a conventional insert in case:
insert into t@dblink select * from t


But in this case ,it will be a direct insert:

insert into t select * from t@dblink


Do you agree with my test? and could you explain this sentence for me:
  Caution :
-Use the parallel hint with select only..Do not use with insert..That will create performance problems.

Do you mean
insert /*+ append parallel */ into t@dblink select ....

will do a direct insert?
Regards!
Alan
Previous Topic: Rename datafile With spaces
Next Topic: Error 997
Goto Forum:
  


Current Time: Thu Dec 08 22:18:13 CST 2016

Total time taken to generate the page: 0.16711 seconds