All Forums Tools
chill3che 99 posts Joined 10/12
12 Sep 2013
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.

Thanks, Cheeli
dhirajpalse 36 posts Joined 01/08
12 Sep 2013

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.


You must sign in to leave a comment.