| append hint with dblink. [message #274381] |
Mon, 15 October 2007 21:57  |
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 #274487 is a reply to message #274476] |
Tue, 16 October 2007 04:00  |
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 #274640 is a reply to message #274545] |
Tue, 16 October 2007 12:07 |
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 #274650 is a reply to message #274646] |
Tue, 16 October 2007 12:46 |
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 #274660 is a reply to message #274651] |
Tue, 16 October 2007 13:40 |
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 #274698 is a reply to message #274662] |
Tue, 16 October 2007 19:32 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
