CODEWITHSUNDEEP
sqloracle-database
dee
dee

Reputation: 609

How can I combine multiple update statements in Oracle?

I've been working on improving the performance of a stored procedure and there is a big slow down due to having to run four very similar updates.

I've found information on similar troubles in MySql which was helpful but I could not implement what I had learned, perhaps Oracle operates differently.

Among the things I've tried are; a batch insert through merge, creating a secondary temp_table and working with no temp tables at all but there has been no improvement.

Being new to Oracle it's entirely possible I'm just going about it the wrong way.

Any advice or answers or directions to answers would be greatly apreciated as I've run out of ideas and working now with nothing but brute force and ignorance.

UPDATE TEMP_TABLE TI SET T.ItemPrice_One =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_One'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

UPDATE TEMP_TABLE TI SET T.ItemPrice_Two =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Two'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

UPDATE TEMP_TABLE TI SET T.ItemPrice_Three =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Three'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

Upvotes: 2

Views: 11721

Answers (4)

Anjan Biswas
Anjan Biswas

Reputation: 7912

A merge is a best bet in this scenario.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

What you're trying to do is essentially a pivot operation -- turning multiple row values into multiple column values in a single row. Below is one method using a common technique in Oracle for doing pivot queries (untested but I think it should work as-is). As of version 11g there is a built-in PIVOT operation, but I have not really looked at it yet -- it may be a more direct method of doing what you need.

UPDATE TEMP_TABLE TI SET ( T.ItemPrice_One, T.ItemPrice_Two,T.ItemPrice_Three )  =
    (
     SELECT
       MAX( CASE WHEN V.Item_Name='Item_Name_One' THEN ItemPrice ELSE NULL END ) Item_Price_One,
       MAX( CASE WHEN V.Item_Name='Item_Name_Two' THEN ItemPrice ELSE NULL END ) Item_Price_Two,
       MAX( CASE WHEN V.Item_Name='Item_Name_Three' THEN ItemPrice ELSE NULL END ) Item_Price_Three
     FROM   Temp.View_Items V
     WHERE  V.ID                           =          T.ID
     AND    V.Date                         =          T.Date
    );

Upvotes: 2

Glenn
Glenn

Reputation: 9150

Maybe you can try exploring the use of the MERGE statement. It may allow you to avoid doing three scans of the view table. I don't have an instance to experiment with, but here is a rough guess of the idea:

MERGE INTO TEMP_TABLE t
   USING (SELECT Item_name, ItemPrice
            FROM Temp.View_Items V
            WHERE V.Item_Name IN ('Item_Name_One', 'Item_Name_Two', 'Item_Name_Three')
              AND V.ID = T.ID
              AND V.Date = T.Date) s
   ON (...join condition here... t.item_name = v.item_name??)
   WHEN MATCHED THEN
     UPDATE SET t.ItemPrice_One = DECODE(s.ItemPrice, 'Item_Name_One', v.ItemPrice, t.ItemPrice_One)
               ,t.ItemPrice_Two = DECODE(s.ItemPrice, 'Item_Name_Two', v.ItemPrice, t.ItemPrice_Two)
               ,t.ItemPrice_Three = DECODE(s.ItemPrice, 'Item_Name_Three', v.ItemPrice, t.ItemPrice_Three);

Upvotes: 1

Roger Cornejo
Roger Cornejo

Reputation: 1547

UPDATE TEMP_TABLE TI SET T.ItemPrice_One =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_One'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        )
,       T.ItemPrice_Two =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Two'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        )
,       T.ItemPrice_Three =
        (
         SELECT ItemPrice
         FROM   Temp.View_Items V
         WHERE  V.Item_Name                    =          'Item_Name_Three'
         AND    V.ID                           =          T.ID
         AND    V.Date                         =          T.Date
        );

Upvotes: 0

Related Questions