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 )
