Pruning data from billing tables in EnergyIP 7.2

I've published live, custom, cross system Oracle Apex reports linking the billing request table in EnergyIP to the billing information in our Customer Information System (Ventyx).  Performance was starting to take a turn for the worse since I didn't want to further index the tables for fear of breaking a future application of service packs or upgrades.  Also,  the table was getting fairly large (1.5 million rows) and it is not partitioned.

The right thing to do is probably to use dbms_redefinition to partition the table(s).  I'll push for a product enhancement instead :).

Since the source of record for our billing was our CIS system I decided that pruning was in order.  I came up with the following to prune all but the last ~90 days of data from the billing tables (adhering to constraints).  Take a backup/data pump export first!


DELETE FROM emapp.billing_comp_tracking
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_data_change_event
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM EMAPP.BILLING_EXPORT_LOG
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_detail
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_sum_check_detail
      WHERE billing_sum_check_log_id IN
               (SELECT billing_sum_check_log_id
                  FROM emapp.billing_sum_check_log
                 WHERE billing_request_id IN
                          (SELECT billing_request_id
                             FROM emapp.billing_request
                            WHERE utc_request_start_time < SYSDATE - 90));

DELETE FROM emapp.billing_sum_check_log
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_request
      WHERE utc_request_start_time < SYSDATE - 90;