| Improve Performance of Insert and Update on same table [message #682356] |
Mon, 19 October 2020 07:35  |
 |
Shre
Messages: 4
Registered: October 2020
|
Junior Member |
|
|
Hi All,
I'm currently looking to improve performance of a PLSQL package which is been called by batch process, which executes concurrently.
This package which is been called in batch process first updates and then inserts into same table and commits the changes.
The table which it updates and inserts doesn't have any primary key or index on it.
The table has total 9 million of records.
Will adding the primary constraint or any index's will enhance the performance of this package?
[Updated on: Mon, 19 October 2020 08:29] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Improve Performance of Insert and Update on same table [message #682369 is a reply to message #682356] |
Tue, 20 October 2020 07:47  |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Shre wrote on Mon, 19 October 2020 13:35Hi All,
I'm currently looking to improve performance of a PLSQL package which is been called by batch process, which executes concurrently.
This package which is been called in batch process first updates and then inserts into same table and commits the changes.
The table which it updates and inserts doesn't have any primary key or index on it.
The table has total 9 million of records.
Will adding the primary constraint or any index's will enhance the performance of this package? For INSERT, as you are making concurrent inserts you would need to be sure that adding a primary key is not going to cause issues with row locking.
For UPDATE, how are you finding the rows to be updated? If you are updating individual rows, an index should certainly help.
If you post the code in this PL/SQL package, perhaps you will get better advice.
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
