Join Index - Blocking sessions

Tools & Utilities
Enthusiast

Join Index - Blocking sessions

Greetings Experts,

We have a single join index that consists of 3 tables.  These 3 tables are being loaded by batch jobs individually.  Before the creation of join index, our 3 jobs that loads in to 3 tables were taking short span of time to complete.  job1 loading into table1,...  When the join index is created and while job1 is loading into table1, it has a WRITE lock on the involved JOININDEX (as expected it is updating the JI when the involved base table is undergoing DML), and other job (job2, job3) sessions are in BLOCKING state waiting for the WRITE lock on the join index there by delaying the job completion.  

For now, we are proceeding by dropping the JI, is there any other alternative to overcome this (Not changing the schedule of jobs so that they will not end up in BLOCKING state).  I know it's a harder to propose a solution without the DDL's involved, just checking for alternative solutions.

1 REPLY
Enthusiast

Re: Join Index - Blocking sessions

If you were using bulk load ie MLOAD/FLOAD scripts to load these 3 base tables then you were left with no other option but to drop JI and recreate after loading. :)

In the scenario you have described, where row by row DML operation on base tables is time consuming, i think you are already doing what is best, ie dropping and recreating JI.