While you are sleeping, there are lots of programs are running based on it’s scheduling. They are often called “Batch Jobs“. And one of batch jobs is to get rid of old transactions.

In Biz apps, we often need to do performance tuning. According to my experience, the SQL is usually the biggest reason to cause a performance issue.

I have a memory that I have a performance issue for this SQL.
This is to get the target records for Data Purge (Oracle):

SELECT
*
FROM TABLE_TO_BE_PURGED A
WHERE
EXISTS ( 
    SELECT '*' 
    FROM  
          PURGE_TARGET_TABLE TGT
    WHERE 1=1
    AND  TGT.INV_NUM  = A.REF_TXT_01
) OR EXISTS ( 
    SELECT '*' 
    FROM 
          INTEREFACE_TABLE ITFC, 
    PURGE_TARGET_TABLE     TGT 
    WHERE 
         ITFC.REF_NUM     = B.REF_NUM
    AND  ITFC.REF_SUB_NUM = B.REF_SUB_NUM
    AND  ITFC.INV_NUM     = TGT.INV_NUM
) 

And came up with this after struggling hours…

SELECT
*
FROM TABLE_TO_BE_PURGED A1
WHERE EXISTS (
    SELECT '*'
    FROM (
        SELECT 
            B1.PURGED_PK
        FROM 
            TABLE_TO_BE_PURGED  B1
        ,   PURGE_TARGET_TABLE  TGT
        WHERE 1=1
        AND TGT.INV_NUM = B1.REF_TXT_01
        UNION
        SELECT 
            B2.PURGED_PK
        FROM 
            TABLE_TO_BE_PURGED  B2
        ,   INTEREFACE_TABLE    ITFC
        ,   PURGE_TARGET_TABLE  TGT
        WHERE 
            ITFC.REF_NUM     = B2.REF_NUM
        AND ITFC.REF_SUB_NUM = B2.REF_SUB_NUM
        AND ITFC.INV_NUM     = TGT.INV_NUM
    ) T1
    WHERE T1.PURGED_PK = A1.PURGED_PK
)

The performance improved dramatically, but the interesting thing was that the execution plan (cost) was pretty much the same. The one above get slower when having more records. And the one below was fast enough although having many records.