Oracle-Java | Update 6000 rows in 30 Mins to 1 hour| A Non Primary Non Indexed composite key

Question

It take

2021-03-10 15:48:07,593 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch Processing Started ********************************
2021-03-10 15:48:07,593 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch No. 22| Total 182| Size:5000 Started ********************************
2021-03-10 16:14:11,227 INFO  [com.ery.integrator.erpcrm.schedular.CustomerTeamsJmsServiceImpl] (EJB default - 2) In CustomerTeamsJmsServiceImpl: ******************************** Batch No.22 Pushed and Flushed ********************************

There is no Composite Key involved at Database Level in which we insert and update.

 CREATE TABLE "MDATA"."CUSTOMER_TEAMS" 
   (    "ACCOUNT_REGISTRY_ID" VARCHAR2(30 BYTE), 
    "EDGE_ACCOUNT_ID" VARCHAR2(150 BYTE), 
    "ACCOUNT_OWNER_EMAIL" VARCHAR2(250 BYTE), 
    "CRT_MEMBER_EMAIL" VARCHAR2(250 BYTE), 
    "ROLE_NAME" VARCHAR2(150 BYTE), 
    "SOURCE" VARCHAR2(30 BYTE), 
    "PERSON_PARTY_ID" NUMBER(15,0), 
    "DELETED_IN_TERP" VARCHAR2(30 BYTE), 
    "DELETED_DATE" DATE, 
    "LAST_MODIFIED_DATE" DATE NOT NULL ENABLE
   )

but in JPA we have composite key on First four columns. @Id and update happens on those four composite key

How to do performance tuning of Database Table so that process goes fast? As of now there is no primary key at database level as well as there is no indexes. Kindly suggest

Answer 1

It is a good practice (default practice I would say) to create some constraints and indexes for your table in the first place. If your UPDATE has a WHERE clause(probably It has), the presence of indexes is an important factor.

Then you can consider the following: In Oracle there is something called "bulk processing". If you use this when you are selecting/inserting/updating a lot of records at the same time the performance of the operation is much better.

• This is a question here on Stack Overflow about how to bulk insert with JPA: How to do bulk (multi row) inserts with JpaRepository?
• This is an article in Oracle blog explaining bulk processing in detail with PL/SQL: Bulk Processing with BULK COLLECT and FORALL

There are other factors to consider as well when talking about performance in Oracle:

• Oracle version: There is a huge gap of optimizing features in Oracle Enterprise and Oracle XE for example.
• Hardware: The hard drive I/O speed, CPU and RAM are important as well.

If you still cannot get an acceptable time, you may try to dig a bit deeper and enable the trace for the your sessions so you can have more data to work with: Using Oracle Trace