Description of problem: This query: delete from RHQ_CONFIG where id in ( select resourceco1_.CONFIGURATION_ID from RHQ_CONFIG_UPDATE resourceco1_, RHQ_RESOURCE resource2_ where resourceco1_.DTYPE='resource' and resourceco1_.CONFIG_RES_ID=resource2_.ID and (resourceco1_.CONFIG_RES_ID in (564877)) and resourceco1_.CONFIGURATION_ID<>resource2_.RES_CONFIGURATION_ID) Explain plan: DELETE STATEMENT 0.0 % RHQ.RHQ_CONFIG DELETE 0.0 % NESTED LOOPS 0.9 % SYS.VW_NSO_1 VIEW 0.0 % SORT (UNIQUE) 0.0 % HASH JOIN 0.0 % RHQ.RHQ_RESOURCE TABLE ACCESS (BY INDEX ROWID) 0.1 % RHQ.SYS_C0012508 INDEX (UNIQUE SCAN) 0.1 % RHQ.RHQ_CONFIG_UPDATE TABLE ACCESS (FULL) 98.8 % ^^^ RHQ.SYS_C0012490 INDEX (UNIQUE SCAN) 0.1 % My RHQ_CONFIG_UPDATE contains 600,000+ rows. Actually think the bigger problem is RHQ_CONFIG_UPDATE never seems to be purged. I have rows from 2012 in there...
See Bug 1131726 on the purge process need. Probably okay to do a table scan if the data is being purged.
(In reply to Elias Ross from comment #1) > See Bug 1131726 on the purge process need. Probably okay to do a table scan > if the data is being purged. Resource config data purge will be possible when Bug 1131726 is fixed.