Home » SQL & PL/SQL » SQL & PL/SQL » Improve Performance of Insert and Update on same table (12.1.0.2.0)
Improve Performance of Insert and Update on same table [message #682356] Mon, 19 October 2020 07:35 Go to next message
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 #682358 is a reply to message #682356] Mon, 19 October 2020 08:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

The best way to know the answer is to add PRIMARY KEY & measure the time difference before & after change was made
Re: Improve Performance of Insert and Update on same table [message #682359 is a reply to message #682356] Mon, 19 October 2020 09:41 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
It's impossible to evaluate code that you cannot see.

Adding indexes _may_ improve the performance of queries, but _will_ degrade the performance of inserts and updates, because the index itself has to be maintained in addition to the base table. I doubt that the addition of a PK, in and of itself, will improve performance. But as BlackSwan said, the best way to find out is add it and test. What would that cost you?
Re: Improve Performance of Insert and Update on same table [message #682369 is a reply to message #682356] Tue, 20 October 2020 07:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Shre wrote on Mon, 19 October 2020 13:35
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?
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.

Re: Improve Performance of Insert and Update on same table [message #682592 is a reply to message #682369] Fri, 30 October 2020 10:51 Go to previous message
Shre
Messages: 4
Registered: October 2020
Junior Member
Thank you all for your help.

I tried adding primary key on table, it didn't help. But adding the indexes did help.

Previous Topic: What is the proper termination for a stored procedure created from C# code?
Next Topic: How to display only one row with max no. of count?
Goto Forum:
  


Current Time: Thu Mar 28 11:26:41 CDT 2024