Optimize oracle update query

Question

How to optimize an update query:

UPDATE frst_usage vfm1 SET
  (vfm1.account_dn,
   vfm1.usage_date,
   vfm1.country,
   vfm1.feature_name,
   vfm1.hu_type,
   vfm1.make,
   vfm1.region,
   vfm1.service_hits,
   vfm1.maint_last_ts,
   vfm1.accountdn_hashcode) = (
    SELECT
      (SELECT vst.account_dn FROM services_track vst WHERE vst.accountdn_hashcode = vrd1.account_dn_hashcode AND rownum = 1),
      min(usage_date),
      country,
      feature_name,
      hu_type,
      make,
      region,
      service_hits,
      SYSDATE,
      account_dn_hashcode
    FROM raw_data vrd1
    WHERE vrd1.vin_hashcode = vfm1.vin_hashcode
          AND vrd1.usage_date IS NOT NULL AND rownum = 1
    GROUP BY account_dn, country, feature_name, hu_type, make, region, service_hits, vfm1.maint_last_ts, account_dn_hashcode
  );

the tables have indexes on all the columns available in the where conditions.

Still the execution is taking more than 4 hours. Below is the explain plan

From the execution plan i could see that the select is good but the update is consuming more time resources, Is there a way i could optimize this.

Answer 1

I think correlated subqueries may be an issue:

WHERE vrd1.vin_hashcode = vfm1.vin_hashcode

You should try merge clause, it could have dramatic impact on performance http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm

Below is example similar to yours. 10k sample rows, all columns indexed and statistics gathered:

Update (16s)

 SQL> update x1 set (v1, v2, v3, v4) =
  2  (
  3    select v1, v2, v3, min(v4)
  4    from x2
  5    where x1.nr = x2.nr
  6    group by v1,v2,v3
  7  );

9999 rows updated.

Elapsed: 00:00:16.56

Execution Plan
----------------------------------------------------------
Plan hash value: 3497322513

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |  9999 |   859K|  1679K  (5)| 05:35:59 |
|   1 |  UPDATE             | X1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL | X1   |  9999 |   859K|    40   (0)| 00:00:01 |
|   3 |   SORT GROUP BY     |      |     1 |    88 |    41   (3)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| X2   |     1 |    88 |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   4 - filter("X2"."NR"=:B1)

Merge(1,5s)

SQL> merge into x1 using (
  2    select nr, v1, v2, v3, min(v4) v4
  3    from x2
  4    group by nr, v1,v2,v3
  5  ) xx2
  6  on (x1.nr = xx2.nr)
  7  when matched then update set
  8  x1.v1 = xx2.v1, x1.v2 = xx2.v2, x1.v3 = xx2.v3, x1.v4 = xx2.v4;

9999 rows merged.

Elapsed: 00:00:01.25

Execution Plan
----------------------------------------------------------
Plan hash value: 1113810112

---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |  9999 |    58M|       |   285   (1)| 00:00:04 |
|   1 |  MERGE                 | X1   |       |       |       |            |          |
|   2 |   VIEW                 |      |       |       |       |            |          |
|*  3 |    HASH JOIN           |      |  9999 |    58M|       |   285   (1)| 00:00:04 |
|   4 |     TABLE ACCESS FULL  | X1   |  9999 |   859K|       |    40   (0)| 00:00:01 |
|   5 |     VIEW               |      |  9999 |    57M|       |   244   (1)| 00:00:03 |
|   6 |      SORT GROUP BY     |      |  9999 |   859K|  1040K|   244   (1)| 00:00:03 |
|   7 |       TABLE ACCESS FULL| X2   |  9999 |   859K|       |    40   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - access("X1"."NR"="XX2"."NR")

Answer 2

Able to solve this using below query, and now the explain plan looks good.

    merge INTO FRST_USAGE vfu USING
    (SELECT tmp1.*
    FROM
      (SELECT ROW_NUMBER() OVER (PARTITION BY tmp.vin_hash ORDER BY tmp.usage_date) AS rn,
        tmp.*
      FROM
        (SELECT vrd.VIN_HASHCODE  AS vin_hash,
          vrd.ACCOUNT_DN_HASHCODE AS actdn_hash,
          vst.ACCOUNT_DN          AS actdn,
          vrd.FEATURE_NAME        AS feature,
          vrd.MAKE                AS make,
          vrd.COUNTRY             AS country,
          vrd.HU_TYPE             AS hu,
          vrd.REGION              AS region,
          vrd.SERVICE_HITS        AS hits,
          MIN(vrd.USAGE_DATE)     AS usage_date,
          sysdate                 AS maintlastTs
        FROM RAW_DATA vrd,
          SERVICES_TRACK vst
        WHERE vrd.ACCOUNT_DN_HASHCODE=vst.ACCOUNTDN_HASHCODE
        GROUP BY vrd.VIN_HASHCODE,
          vrd.ACCOUNT_DN_HASHCODE,
          vst.ACCOUNT_DN,
          vrd.FEATURE_NAME,
          vrd.MAKE,
          vrd.COUNTRY,
          vrd.HU_TYPE,
          vrd.REGION,
          vrd.SERVICE_HITS,
          sysdate
        ORDER BY vrd.VIN_HASHCODE,
          MIN(vrd.USAGE_DATE)
        ) tmp
      )tmp1
    WHERE tmp1.rn              =1
    ) tmp2 ON (vfu.VIN_HASHCODE = tmp2.vin_hash)
    WHEN matched THEN
      UPDATE
      SET vfu.ACCOUNTDN_HASHCODE=tmp2.actdn_hash,
        vfu.account_dn          =tmp2.actdn,
        vfu.FEATURE_NAME        =tmp2.feature,
        vfu.MAKE                =tmp2.make,
        vfu.COUNTRY             =tmp2.country,
        vfu.HU_TYPE             =tmp2.hu,
        vfu.REGION              =tmp2.region,
        vfu.SERVICE_HITS        =tmp2.hits,
        vfu.usage_date          =tmp2.usage_date,
        vfu.MAINT_LAST_TS       =tmp2.maintlastTs;

below is the Explan plan:

Suggestions are allowed if there is any more optimizations i can do on this.

Answer 3

Indexes on the the target table reduce the performance.Disable the index before updating the table and rebuild the index once the update completed.