I need some help with some performance issues:

I have a table CONTRATOS with 27 millions of records. This table has multiple columns but the important ones are: CONTRATO, CODIGO\_ORIGEN.

It can exist more than one CONTRATO for each CODIGO\_ORIGEN so i need to do this:

```
SELECT DISTINCT 
    CODIGO_ORIGEN,
    FIRST_VALUE(CONTRATO) OVER (PARTITION BY CODIGO_ORIGEN ORDER BY FECHA DESC) MAX_CONTRATO
FROM TABLE CONTRATOS;

```

After this we have 6 millions of records, the good ones...

What i need to do is update the "NOT FOUND" records (CONTRATO) in the table CONTRATOS with the value 'E' on the column ESTADO.

At first, i tried to do it everything in one step (query):

```
UPDATE / NOT EXISTS METHOD
--------------------------

    UPDATE CONTRATOS C
    SET ESTADO = 'E'
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM
        (
            SELECT DISTINCT 
                CODIGO_ORIGEN,
                FIRST_VALUE(CONTRATO) OVER (PARTITION BY CODIGO_ORIGEN ORDER BY FECHA DESC) MAX_CONTRATO
            FROM TABLE CONTRATOS
        ) C2
        WHERE C.CONTRATO = C2.MAX_CONTRATO
    );

MERGE METHOD
---------------

    MERGE INTO CONTRATOS C
    USING 
    (
        SELECT DISTINCT 
            CODIGO_ORIGEN,
            FIRST_VALUE(CONTRATO) OVER (PARTITION BY CODIGO_ORIGEN ORDER BY FECHA DESC) MAX_CONTRATO
        FROM TABLE CONTRATOS
    ) C2 ON (C.CONTRATO = C2.MAX_CONTRATO)
    WHEN MATCHED THEN UPDATE SET ESTADO = 'E';

```

But after seeing that it was too slow (both methods) i tried to do it using a temporal table where i can insert the valid rows: TEMP\_CONTRATOS

```
    Note: This point goes pretty fast (15 min)

    INSERT INTO TEMP_CONTRATOS (MAX_CONTRATO, CODIGO_ORIGEN)
    SELECT DISTINCT 
            CODIGO_ORIGEN,
            FIRST_VALUE(CONTRATO) OVER (PARTITION BY CODIGO_ORIGEN ORDER BY FECHA DESC) MAX_CONTRATO
    FROM TABLE CONTRATOS;

    Note: Here is the problem again

    UPDATE CONTRATOS C
    SET ESTADO = 'E'
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM TEMP_CONTRATOS TMP
        WHERE C.CONTRATO = TMP.MAX_CONTRATO
    );

```

I have an index on both tables with the columns (CONTRATO/MAX\_CONTRATO and CODIGO\_ORIGEN) so i can improve the performance (right?) but im still getting too slow results...

The execution plan gives me over 700.000 of cost for 27 millions of records... Is it too much or is it ok considering the amount of records? It´s doing a TABLE FULL SCAN of both tables.

Is it a good idea using hints? is it a good idea going through indexes?... What is the best option? using Merge or Exists/Not exists / Not in??

Also, i´m not sure about creating an index for the ESTADO column because im updating that field and i read it´s not a good idea to create a index in a column where you perform updates... but i have to do a lot of queries joining with it.

Im using the V$SESSION\_LONGOPS view and the session view of the sysdba user to monitorize the process... Any better alternative?

I don´t know if i forget something important, i guess i do.

Bring me some light please. Any advice is appreciated.

Thank you in advance

Regards.