Article Original Creation Date: 2010-12-10

Overview

This article explains if it is safe to remove the following statements out of the sprt_ec_record_purging table or used script.

DELETE FROM 
 sprt_sg_policy_device_history 
WHERE 
 guid IN (
 SELECT 
 dh.guid 
 FROM 
 sprt_sg_policy_device_history dh 
 LEFT JOIN sprt_sg_policy_exec_history eh ON dh.sg_policy_exec_history_guid = eh.guid 
 WHERE 
 eh.guid IS NULL
 ) 
 AND rownum <= & rows;

SELECT 
 ah.guid 
FROM 
 sprt_sg_policy_action_history ah 
 LEFT JOIN sprt_sg_policy_exec_history eh ON ah.sg_policy_exec_history_guid = eh.guid 
WHERE 
 eh.guid IS NULL

It appears that these two queries will never delete anything because they have a clause WHERE eh.guid IS NULL, and eh.guid is a non-null column.

Environment

• Solaris 10
• Oracle 10
• SG 4.0.12
• WL 9.2 MP1
• Tomcat 5.5.25
  
  

Information

The queries listed above are used to clean up history from the deleted policies. The foreign key constraint with a cascade delete between policy_exec and the history tables will take a long time to complete and affect overall performance. Thus these queries are used.

Even though the exec_history guid can never be null in a record; the left join will still create a null for that value, which these queries use to test the non-existence of the parent record.

You seldom see these queries remove any records because policy deletion is rare.

It is fine to remove these queries and let the regular record history purging delete the orphaned records for any deleted policies.