SQL Update Query running forever. Oracle Performace Tuning Tips required

Question

I have a table which has 9 million records. The requirement is as below : 1. Table is inserted with 9 million records. Each entry will have a rowid which is inserted into table using trigger. 2. we have two columns which has to be updated seperately in the same table called First_Insatance and First_Instance_Date. 3. These First Instance columns should hold the first Rowid number of the duplicate records present in the table.

we are using the below code to update

  UPDATE TABLE_A T4
      SET (T4.ROW_ID_FIRST_INS, T4.ROW_ID_DT_FIRST_INS) =
             (  SELECT MIN (T3.ROW_ID), MIN (T3.UPDATE_DATE)
                  FROM TABLE_A T3
                 WHERE     T3.SOURCE(+) = T4.SOURCE
                       AND    SUBSTR (T3.TABLE_NAME,
                                      1,
                                      REGEXP_INSTR (T3.TABLE_NAME,
                                                    '\_[0-9]{8}T',
                                                    1,
                                                    1,
                                                    0))
                           || SUBSTR (T3.TABLE_NAME,
                                      REGEXP_INSTR (T3.TABLE_NAME,
                                                    '\_[0-9]{8}T',
                                                    1,
                                                    1,
                                                    1),
                                        LENGTH (T3.TABLE_NAME)
                                      - REGEXP_INSTR (T3.TABLE_NAME,
                                                      '\_[0-9]{8}T',
                                                      1,
                                                      1,
                                                      1)) =
                                 SUBSTR (T4.TABLE_NAME,
                                         1,
                                         REGEXP_INSTR (T4.TABLE_NAME,
                                                       '\_[0-9]{8}T',
                                                       1,
                                                       1,
                                                       0))
                              || SUBSTR (T4.TABLE_NAME,
                                         REGEXP_INSTR (T4.TABLE_NAME,
                                                       '\_[0-9]{8}T',
                                                       1,
                                                       1,
                                                       1),
                                           LENGTH (T4.TABLE_NAME)
                                         - REGEXP_INSTR (T4.TABLE_NAME,
                                                        '\_[0-9]{8}T',
                                                         1,
                                                         1,
                                                         1))
                       AND NVL (T4.I_NAM, 'xx') =
                              NVL (T3.I_NAM, 'xx')
                       AND NVL (T4.J_NAM, 'xx') = NVL (T3.J_NAM, 'xx')
                       AND NVL (T4.SYS_NAM, 'xx') =
                              NVL (T3.SYS_NAM, 'xx')
                       AND NVL (T4.TG_TAB_NAM, 'xx') =
                              NVL (T3.TG_TAB_NAM, 'xx')
                       AND NVL (T4.PK, 'xx') = NVL (T3.PK, 'xx')
                       AND NVL (T4.ERR, 'xx') =
                              NVL (T3.ERR, 'xx')
                       AND NVL (T4.VAL, 'xx') =
                              NVL (T3.VAL, 'xx')
                       AND NVL (T4.ID, 'xx') = NVL (T3.ID, 'xx')
              GROUP BY T4.FIELD,
                       T4.ERR,
                       T4.VAL,
                       T4.ID,
                       T4.PK,
                       T4.I_NAM,
                       T4.SYS_NAM,
                       T4.J_NAM)
    WHERE T4.CURRENT_LOAD_ID = some number FROM CURSOR;

The query is taking 35 secs for 3050 records, but when run for 4L records. Its not stopping and running from past 2 days.

Row_ID is PK and have created NU index on all group by columns except few which had size 4000.

Thanks.

Answer 1

You seem to be using an overly complex method of determining duplicates using aliases. I'd try to integrate something more simple along these lines. select id from table1 group by id having count(id ) > 1

Without your table structure, some sample data, and a full explanation of the problem it is hard to do more.