Hi,

I am working on a Archiving application. Aim is to bring year and years of data from Production DB into another DB named as Archiving DB and then delete from the Production DB. Currently I am testing deletion on an Archiving DB to test my package performance. I am running I query like mentioned below.

DELETE FROM IBE_BOOKEDPRODUCT p
   WHERE 
   parentbookingreference IS NULL AND
   EXISTS (
    SELECT 1
    FROM ARCHIVING_TEMP_TABLE_BOOKINGS a
    WHERE p.BOOKINGREFERENCE = a.BOOKINGREFERENCE
   );

Now the table ARCHIVING_TEMP_TABLE_BOOKINGS will have 50,000 booking references. Table IBE_BOOKEDPRODUCT has 100 of Million of rows. Currently where I am stuck, it should be removing around 450K rows from IBE_BOOKEDPRODUCT.

I have gone through your article on indexing Foreign Keys. 20 tables are using this table IBE_BOOKEDPRODUCT's columns. Mostly were unindexed. All these 20 tables also had Millions of rows.

I have tested thing thing earlier where my query was taking 40 minutes and once I indexed FKey (though here it was comprised of 1 column), it completed in 2 minutes.

However, in case of IBE_BOOKEDPRODUCT, it has around 10-12 columns which are being used as FKey. I indexed all and then it took strange more time like 3 hours and still not completed. Previously it was taking about 40 minutes. I have now removed indexes I created and am trying the logic again.

However, I need your suggestion how to optimize my logic.


Note: below is the explain plan of the running query
Query Plan, Rows, Rowsource Time(s)

DELETE STATEMENT   Cost = 175300        361
DELETE  IBE_BOOKEDPRODUCT         594
    NESTED LOOPS      413K 0
        NESTED LOOPS      413K 0
            SORT UNIQUE      50K 0
                TABLE ACCESS FULL ARCHIVING_TEMP_TABLE_BOOKINGS     50K 0
            INDEX RANGE SCAN XXBCF_IBEBOOKPROD002      8  0
        TABLE ACCESS BY INDEX ROWID IBE_BOOKEDPRODUCT      8  0

RowSource time is increasing in each refresh I do for top 2 lines.

DELETE FROM IBE_BOOKEDPRODUCT p
   WHERE 
   parentbookingreference IS NULL AND p.BOOKINGREFERENCE in 
    SELECT a.BOOKINGREFERENCE
    FROM ARCHIVING_TEMP_TABLE_BOOKINGS a
   );