Home » SQL & PL/SQL » SQL & PL/SQL » Multiple updates of same table (10g)
Multiple updates of same table [message #305605] Tue, 11 March 2008 06:17 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Is there a way I can optimize this statement? MY_FACT table has 13M rows:
    UPDATE MY_FACT
       SET active_tab1 = 1
     WHERE my_id IN (SELECT id FROM TAB1)
       AND comp = 'D';

    UPDATE MY_FACT
       SET active_tab2 = 1
     WHERE my_id IN (SELECT id FROM TAB2)
       AND comp = 'D';

    UPDATE MY_FACT
       SET active_tab3 = 1
     WHERE my_id IN (SELECT id FROM TAB3)
       AND comp = 'D';

    UPDATE MY_FACT
       SET active_tab4 = 1
     WHERE my_id IN (SELECT id FROM TAB4)
       AND comp = 'D';

Thanks in advance.
Re: Multiple updates of same table [message #305608 is a reply to message #305605] Tue, 11 March 2008 06:23 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How many records you will be updating.
What are the indexes on the fact table?
It's worthwhile checking this link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330

Regards

Raj
Re: Multiple updates of same table [message #305641 is a reply to message #305605] Tue, 11 March 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think it is already optimized.

Regards
Michel
Re: Multiple updates of same table [message #305756 is a reply to message #305641] Tue, 11 March 2008 21:51 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is not tested code, but it should give you the idea. Its intent is to perform a single pass over my_fact.


MERGE INTO my_fact
USING (
  SELECT id
  ,      max(a1) AS a1
  ,      max(a2) AS a2
  ,      max(a3) AS a3
  ,      max(a4) AS a4
  FROM (
    SELECT id, 1 AS a1, NULL AS a2, NULL AS a3, NULL AS a4
    FROM   tab1
    UNION ALL
    SELECT id, NULL AS a1, 1 AS a2, NULL AS a3, NULL AS a4
    FROM   tab2
    UNION ALL
    SELECT id, NULL AS a1, NULL AS a2, 1 AS a3, NULL AS a4
    FROM   tab3
    UNION ALL
    SELECT id, NULL AS a1, NULL AS a2, NULL AS a3, 1 AS a4
    FROM   tab4
  )
  GROUP BY id
) x
ON (my_fact.my_id = x.id AND my_fact.comp = 'D')
WHEN MATCHED THEN UPDATE SET
    active_tab1 = decode(x.a1, NULL, my_fact.active_tab1, x.a1)
,   active_tab2 = decode(x.a2, NULL, my_fact.active_tab2, x.a2)
,   active_tab3 = decode(x.a3, NULL, my_fact.active_tab3, x.a3)
,   active_tab4 = decode(x.a4, NULL, my_fact.active_tab4, x.a4)


If you are updating more than (say) 1-10% of the rows in total, then it may be faster to bebuild the table from scratch.

If you are updating a small number of rows, then it would be better to create indexes on my_fact (my_id, comp, active_tab1), (my_id, comp, active_tab2), (my_id, comp, active_tab3), (my_id, comp, active_tab4).

If you cannot achieve either of these optimal solutions, the above SQL may or may not improve your performance.

Ross Leishman
Previous Topic: User Defined Aggregate with Multiple Inputs?
Next Topic: report body generation
Goto Forum:
  


Current Time: Tue Dec 06 08:37:36 CST 2016

Total time taken to generate the page: 0.12009 seconds