Skip to Main Content
  • Questions

  • correlational update statement giving performance issue

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Shubhodipto.

Asked: January 15, 2016 - 5:27 am UTC

Last updated: January 27, 2016 - 5:35 pm UTC

Version: 10G

Viewed 1000+ times

You Asked

The below update statement is taking lot of time. Can you please help me to tune this query? I will prefer re-structuring the query rather than using any HINT. My only constraint is, I can’t use index since there is no index in the table

UPDATE table_main a
SET column_to_update =
(SELECT DISTINCT b.column_from_update
FROM table1 b
WHERE b.key1 = a.key1
AND b.type1 = a.type1);

Please find the plan for the query:
Plan
UPDATE STATEMENT ALL_ROWSCost: 386 Bytes: 1,369,332 Cardinality: 44,172
4 UPDATE Table_main
1 TABLE ACCESS FULL TABLE table_main Cost: 386 Bytes: 1,369,332 Cardinality: 44,172
3 HASH UNIQUE Cost: 283 Bytes: 28 Cardinality: 1
2 TABLE ACCESS FULL TABLE table1 Cost: 282 Bytes: 28 Cardinality: 1


Table_main volume :44K
Table1 volume : 53k

and Connor said...

Are you updating ALL rows ? Because that is what is currently happening

Maybe try this for starters

UPDATE table_main a
SET column_to_update =
  (SELECT DISTINCT b.column_from_update
   FROM table1 b
   WHERE b.key1 = a.key1
   AND b.type1 = a.type1)
where (a.key1,a.type1) in 
  ( select key1,type1 from table1 )


The DISTINCT is interesting because if you were to get more than 1 distinct value, the update crashes anyway. Which means they must all be the same. You could explore the inner query being:

SELECT b.column_from_update
FROM table1 b
WHERE b.key1 = a.key1
AND b.type1 = a.type1
and rownum = 1

but you can only do this if you can guarantee the data is valid (ie, you would *not* get an error if there were indeed more than 1 distinct value).

The other thing to experiment with is a join-update, ie

UPDATE 
 (  select a.key1, a.type1, a.column_to_update old_val, b.column_from_update new_val
    from table_main a,
        (SELECT DISTINCT key1, type1, column_from_update
         FROM table1
         ) b
   WHERE b.key1 = a.key1
   AND b.type1 = a.type1
 )
SET old_val = new_val


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not Working any of the solution

Shubhodipto Roy, January 15, 2016 - 8:45 am UTC

To answer your 1st question, yes we are updating all rows. I tried all 3 option and none of the solution is working

1. & 2. Both are taking same time
3. Getting error - ORA-01779: cannot modify a column which maps to a non key-preserved table

Waiting for some more solutions.
Connor McDonald
January 15, 2016 - 12:50 pm UTC

You'll to send us

- the ddl to create the tables including indexes etc
- some sample data that we can use to extrapolate out to full size

and ideally a sql trace file (email it to: asktom_us@oracle.com)


Merge instead of update

Rob van den Berg, January 16, 2016 - 9:34 am UTC

Hi Shubhodipto,

Same disclaimers Connor mentioned apply. I had a great time anyway constructing a merge update on a table I do hope resembles yours:

SQL> drop table table_main purge;
Table dropped.
SQL> drop table table1 purge;
Table dropped.
SQL> create table table_main
  2  ( key1    varchar2(10)
  3  , type1   varchar2(10)
  4  , column_to_update number
  5  )
  6  /
Table created.
SQL> create table table1
  2  ( key1    varchar2(10)
  3  , type1   varchar2(10)
  4  , column_from_update number
  5  )
  6  /
Table created.
SQL> insert into table1
  2  ( key1
  3  , type1
  4  , column_from_update
  5  )
  6  ( select  'KEY'||level
  7    ,  'TYPE'||level
  8    , mod(level,1000)
  9    from dual
 10    connect by level <= 53000
 11  )
 12  /
53000 rows created.
SQL> insert into table_main
  2  ( key1
  3  , type1
  4  )
  5  ( select key1, type1 from table1 where rownum <= 44000)
  6  /
44000 rows created.
SQL> select count(*) from table_main where column_to_update is not null;
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> set timing on
SQL> MERGE INTO table_main a
  2  USING ( SELECT  b.key1
  3        ,      b.type1
  4        ,      b.column_from_update
  5        FROM    table1 b
  6        JOIN    table_main a
  7        ON      b.key1 = a.key1
  8        AND     b.type1 = a.type1
  9        GROUP BY b.key1, b.type1, b.column_from_update
 10      ) b
 11  ON    ( a.key1 = b.key1
 12        AND a.type1 = b.type1
 13      )
 14  WHEN MATCHED THEN
 15  UPDATE SET a.column_to_update=b.column_from_update
 16  ;
44000 rows merged.
Elapsed: 00:00:00.66
SQL> set timing off
SQL> 
SQL> select count(*) from table_main where column_to_update is not null;

  COUNT(*)                                                                      
----------                                                                      
     44000

Connor McDonald
January 16, 2016 - 12:32 pm UTC

nice work

Not Working...

Shubha, January 19, 2016 - 6:56 am UTC

I have already used this merge query to update my result set but unfortunately this is updating my entire table. Instead of updating 44172 records it is only updating 44100 records...

Done

Shubha, January 19, 2016 - 12:33 pm UTC

Nice...finally working...ignore my last comment

Will that work too???

Shubha, January 27, 2016 - 8:23 am UTC

How about this query?? I think this will work as good as above!! let me know your thought on this...

merge table_main a
using ( SELECT DISTINCT a.rowid as main_rowid,b.column_from_update
FROM table1 b ,table_main a
WHERE b.key1 = a.key1
AND b.type1 = a.type1) UPD
on (a.rowid=upd.main_rowid)
WHEN MATCHED THEN
UPDATE
SET a.column_to_update = UPD.column_from_update
Chris Saxon
January 27, 2016 - 5:35 pm UTC

Seems to work as well as the previous example. Not sure why you feel the need to use rowids though. I don't see what benefit it gives.

Chris
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.