How I should fix the following update statement to make it run faster?

Question

I have the following DML:

UPDATE rc_usutb011 u 
  SET cod_ant=(
    SELECT cod_hst 
      FROM rc_pdptb101 p 
      WHERE ocip_hst=ocip_ant 
        AND p.sector=u.sector
    );

The explain plan looks as follows:

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2656911336

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                  |    17M|  1042M|    71M (25)|238:59:58 |        |      |            |
|   1 |  UPDATE                      | RC_USUTB011      |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR             |                  |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10000         |    17M|  1042M|  7632   (3)| 00:01:32 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR        |                  |    17M|  1042M|  7632   (3)| 00:01:32 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL       | RC_USUTB011      |    17M|  1042M|  7632   (3)| 00:01:32 |  Q1,00 | PCWP |            |
|   6 |   TABLE ACCESS BY INDEX ROWID| RC_PDPTB101      |     1 |    61 |     3   (0)| 00:00:01 |        |      |            |
|*  7 |    INDEX UNIQUE SCAN         | I_RC_PDPTB101_12 |     1 |       |     2   (0)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("OCIP_HST"=:B1 AND "P"."SECTOR"=:B2)

19 rows selected.

Elapsed: 00:00:00.11
SQL>
---------------------------------------------------

My parallel configuration is the following

SQL> SELECT * FROM v$px_process_sysstat WHERE statistic LIKE 'Servers%';

STATISTIC                           VALUE
------------------------------ ----------
Servers In Use                          8
Servers Available                       0
Servers Started                         8
Servers Shutdown                        0
Servers Highwater                       8
Servers Cleaned Up                      0

6 rows selected.

Elapsed: 00:00:00.45
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     40
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL>

Buffers:

SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

STATISTIC                           VALUE
------------------------------ ----------
Buffers Allocated                      24
Buffers Freed                           0
Buffers Current                        24
Buffers HWM                            24

Elapsed: 00:00:00.07
SQL>

cpu info

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     2
SQL>

Please help.

Thanks.

Answer 1

You're updating lots of rows. It will take time no matter what.

You could try to update the join though (since we have an INDEX UNIQUE SCAN on the lookup table), it will surely work better than an inline index loop:

UPDATE (SELECT u.cod_ant, p.cod_hst
          FROM rc_usutb011 u
          JOIN rc_pdptb101 p 
            ON ocip_hst = ocip_ant 
           AND p.sector=u.sector)
   SET cod_ant = cod_hst
 WHERE cod_ant != cod_hst

This is assuming that all records in rc_usutb011 have an existing recode in rc_pdptb101. If this is not the case, you'll have to use a LEFT OUTER JOIN to have an equivalent query.

Answer 2

The problem are not your indexes but that this statement will update every row in table RC_USUTB011 and that are about 17 millions.

How many rows should be updated?

SELECT cod_hst FROM RC_PDPTB101 p WHERE ocip_hst=ocip_ant;

I would rewrite the update statment in the following way:

UPDATE RC_USUTB011 u
SET cod_ant=(SELECT cod_hst FROM RC_PDPTB101 p WHERE ocip_hst=ocip_ant AND p.sector=u.sector)
WHERE EXISTS (SELECT 1 from RC_PDPTB101 p1 WHERE ocip_hst=ocip_ant AND p1.sector=u.sector);