I am executing following sql from stored procedure and it is taking more than 20 minutes to delete 5 million records from table.
Can some one please advise?
WHERE HOST_ID IN
FROM AGMS_USER_HOST UH
WHERE UH.IP_INT IN
(SELECT * FROM TABLE(:B2 )
AND UH.SUBSCRIPTION_ID = :B1
AND ASSET_GROUP_ID IN
(SELECT * FROM TABLE(:B3 )
The binding (:B2) is an oracle array which has 10_000 ids and I am collecting these ids in batch on 10_000 only.
The binding (:B3) is an oracle array but it has only 5-10 ids.
The table AGMS_HOST_ASSET_GROUP has 12 million rows where as AGMS_USER_HOST has 70 million rows.
Execution plan of this sql is attached below.
After gather stats: