DELETE SQL performance

Delete Performance and ROWID 

We discovered slow performance on a DELETE statement that was using an EXISTS joining a related table and temporary table.  The problem is the entire subselect is executed returning a lot of  data to be evaluated only to delete a few rows from the source table.

To speed this up I added the original source table to the join and the driving table that gave me the smallest set of data.  In this case it was to join SAD_EXT_CRS_COM to EXT_COURSE followed by joining to the temporary table.  Now the the optimizer also help the subselect run extremely fast.  At this point we just need to convert the EXISTS to an IN statement and use the oracle pseudocolumn ROWID which is the fastest way to access a single row. The delete query now executes in no time.

Slow version using WHERE EXISTS

 DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE EXISTS ( SELECT NULL FROM %Table(EXT_COURSE) p 
                     , %Table(M_ET_PST_TMP) t 
               WHERE t.process_instance = %Bind(process_instance) 
                 AND t.oprid = %OperatorId 
                 AND p.emplid = t.emplid 
                 AND p.ext_org_id = t.ext_org_id 
                 AND p.ls_data_source = 'OET' 
                 AND p.ext_data_nbr = t.ext_data_nbr 
                 AND c.emplid = p.emplid 
                 AND c.ext_org_id = p.ext_org_id 
                 AND c.EXT_COURSE_NBR = p.EXT_COURSE_NBR ) 

Faster version using ROWID IN

DELETE FROM %Table(SAD_EXT_CRS_COM) c 
WHERE ROWID IN ( SELECT C.ROWID FROM %Table(SAD_EXT_CRS_COM) C 
                   JOIN %Table(EXT_COURSE) P ON C.EMPLID = P.EMPLID 
                    AND C.EXT_ORG_ID = P.EXT_ORG_ID 
                    AND C.EXT_COURSE_NBR = P.EXT_COURSE_NBR 
                   JOIN %Table(M_ET_PST_TMP) T ON P.EMPLID = T.EMPLID 
                    AND P.EXT_ORG_ID = T.EXT_ORG_ID 
                    AND P.EXT_DATA_NBR = T.EXT_DATA_NBR 
                   WHERE P.LS_DATA_SOURCE = 'OET' 
                     AND T.PROCESS_INSTANCE = %Bind(process_instance) 
                     AND T.OPRID = %OperatorId )