| Append Hint in oracle [message #661436] |
Fri, 17 March 2017 10:38  |
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
|
Member |
|
|
Hi All,
what would be the impact if I use PARALLEL & APPEND hint together in INSERT OR MERGE statement?
Also what would be the impact if I use APPEND hint in insert statement and same insert or merge statement executed in parallel in multiple session? Also the impact of APPEND hint use in different dml statement like MERGE OR INSERT which will be executed in parallel in different sessions?
Thanks
Amit
[Updated on: Fri, 17 March 2017 10:44] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Append Hint in oracle [message #661454 is a reply to message #661437] |
Fri, 17 March 2017 14:59  |
amit.sonar
Messages: 98
Registered: December 2009
Location: Mumbai
|
Member |
|
|
Well Not exactly. I do use both the hints. As per my understanding APPEND can cause high water mark in the tables since it uses DIRECT PATH to write the data and it writes the the at the end of a table which creates sparse blocks in a table. I want know more consequences of APPEND hint and if run it parallaly.
Thanks.
[Updated on: Fri, 17 March 2017 15:00] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Append Hint in oracle [message #661460 is a reply to message #661455] |
Sat, 18 March 2017 03:44 |
John Watson
Messages: 8974
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
amit.sonar wrote on Fri, 17 March 2017 20:01APPEND requires a table lock Could you please explain it?
Thanks Amit, if a statement requires a table lock then
A) the statement cannot run if any other session has a row lock already
B) when the statement is running and has not committed, no other session can acquire a row lock
This is very basic, you know. There is a chapter in the Concepts Guide titled Data Concurrency And Consistency that you may want to read.
|
|
|
|
| Re: Append Hint in oracle [message #661480 is a reply to message #661442] |
Mon, 20 March 2017 07:31 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
John Watson wrote on Fri, 17 March 2017 13:38APPEND requires a table lock. Do you really want that?
John are you sure? All the append hint does is tells oracle to start inserting after the high water mark so it doesn't have to search for available extents. All indexes are maintained during the inserts and only the new rows would be locked until the commit. It is very possible that I am wrong, can you provide a link to the information?
|
|
|
|
| Re: Append Hint in oracle [message #661481 is a reply to message #661480] |
Mon, 20 March 2017 07:45 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
You can't even query direct path loaded objects until you commit. Same for parallel DML.
It will throw
ORA-12838: cannot read/modify an object after modifying it in parallel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
