Home » SQL & PL/SQL » SQL & PL/SQL » update block issue (oracle,9.2.0.8,window)
update block issue [message #445535] Tue, 02 March 2010 06:47 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

Attached code is trying to update 4 lakh rows, and it is taking more then 12 hrs. While I am running the individual
Queries, they are taking time in millisecond. Could you please advice on this

Regards,
Amit
  • Attachment: PLSQL.sql
    (Size: 3.04KB, Downloaded 122 times)
Re: update block issue [message #445538 is a reply to message #445535] Tue, 02 March 2010 07:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Attachment seems not to be downloadable. Did you upload the file of are you linking to something on your local PC. I believe to attach correctly, you must upload your file and then insert a link to the uploaded document.

Kevin
Re: update block issue [message #445539 is a reply to message #445535] Tue, 02 March 2010 07:06 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's attached just fine Kevin, not sure what problem you are having. Anyway - here's the code:
/* Formatted on 2010/03/02 18:12 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c1
   IS
      SELECT DISTINCT inventory_item_id
                 FROM rule_table;

   TYPE x1 IS TABLE OF c1%ROWTYPE
      INDEX BY PLS_INTEGER;

   z1                  x1;
   l_strategy_source   NUMBER;
   l_psl_strategy      VARCHAR2 (20);
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
      BULK COLLECT INTO z1 LIMIT 50000;

      FOR indx1 IN 1 .. z1.COUNT
      LOOP
         flag := flag + 1;

         -- adding the Priority into logic for calculating the strategy source and Psl Strategy flag
         SELECT MAX (TO_NUMBER (temp.strategy_source))
           INTO l_strategy_source
           FROM rule_table temp, strategy_rule cs
          WHERE temp.strategy_rule_id = cs.strategy_rule_id
            AND inventory_item_id = z1 (indx1).inventory_item_id
            AND NVL (priority, -1) IN (
                   SELECT NVL (MIN (cs.priority), -1)
                     FROM rule_table temp, strategy_rule cs
                    WHERE temp.strategy_rule_id = cs.strategy_rule_id
                      AND inventory_item_id = z1 (indx1).inventory_item_id);

         SELECT DECODE (psl_strategy,
                        5, 'Non-PSL',
                        4, 'Mixed (P, LD, L)',
                        3, 'Mixed (P Only)',
                        2, 'PSL (P, LD, L)',
                        1, 'PSL (P Only)',
                        ''
                       )
           INTO l_psl_strategy
           FROM (SELECT   temp.psl_strategy
                     FROM rule_table temp, strategy_rule cs
                    WHERE temp.strategy_rule_id = cs.strategy_rule_id
                      AND inventory_item_id = z1 (indx1).inventory_item_id
                      AND NVL (priority, -1) IN (
                             SELECT NVL (MIN (cs.priority), -1)
                               FROM rule_table temp, strategy_rule cs
                              WHERE temp.strategy_rule_id =
                                                           cs.strategy_rule_id
                                AND inventory_item_id =
                                                  z1 (indx1).inventory_item_id)
                 GROUP BY temp.psl_strategy)
          WHERE ROWNUM < 2;

         UPDATE part_attributes empa
            SET strategy_source = l_strategy_source,
                psl_strategy = l_psl_strategy
          WHERE inventory_item_id = z1 (indx1).inventory_item_id;

         IF (flag > 5000)
         THEN
            INSERT INTO TEST
                 VALUES (SYSDATE, flag);

            COMMIT;
            flag := 0;
         END IF;
      END LOOP;

      EXIT WHEN c1%NOTFOUND;
   END LOOP;

   CLOSE c1;

   /*-----------------------------
   |                             |
   |       FINAL COMMIT          |
   |                             |
   +-----------------------------*/
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack ());
      ROLLBACK;
END;
/


@vickey_sagar1 - next time just post the code in the thread in code tags.
Re: update block issue [message #445547 is a reply to message #445535] Tue, 02 March 2010 07:33 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not sure why you're surprised by the 12 hours figure.
You're looping over 400,000 rows and doing 2 selects and an update for each. So that's 1,200,000 dmls in total (ignoring the initial fetch).
If we assumed an incredibly optimistic 0.01 second per dml that'd still take over three hours.
And some simple maths shows yours are running approx 0.03 sec per dml.

This select looks suspect:
SELECT DECODE (psl_strategy,
                        5, 'Non-PSL',
                        4, 'Mixed (P, LD, L)',
                        3, 'Mixed (P Only)',
                        2, 'PSL (P, LD, L)',
                        1, 'PSL (P Only)',
                        ''
                       )
INTO l_psl_strategy
FROM (SELECT   temp.psl_strategy
      FROM rule_table temp, strategy_rule cs
      WHERE temp.strategy_rule_id = cs.strategy_rule_id
      AND inventory_item_id = z1 (indx1).inventory_item_id
      AND NVL (priority, -1) IN (SELECT NVL (MIN (cs.priority), -1)
                                 FROM rule_table temp, strategy_rule cs
                                 WHERE temp.strategy_rule_id =
                                                           cs.strategy_rule_id
                                 AND inventory_item_id =
                                                  z1 (indx1).inventory_item_id)
      GROUP BY temp.psl_strategy)
WHERE ROWNUM < 2;


A top-n query without an order by is usually wrong.
I suspect you are relying on the group by to give the results in the right order but you should realise that oracle does not guarantee that a group by will order results.
So assuming it should have an order by temp.psl_strategy after the group by you can rewrite it like this:
SELECT DECODE (min(ptemp.psl_strategy),
               5, 'Non-PSL',
               4, 'Mixed (P, LD, L)',
               3, 'Mixed (P Only)',
               2, 'PSL (P, LD, L)',
               1, 'PSL (P Only)',
               ''
              )
INTO l_psl_strategy
FROM rule_table temp, strategy_rule cs
WHERE temp.strategy_rule_id = cs.strategy_rule_id
AND inventory_item_id = z1 (indx1).inventory_item_id
AND NVL (priority, -1) IN (SELECT NVL (MIN (cs.priority), -1)
                           FROM rule_table temp, strategy_rule cs
                           WHERE temp.strategy_rule_id = cs.strategy_rule_id
                           AND inventory_item_id = z1 (indx1).inventory_item_id);


You can then combine it with the 1st select, since the where clause of both is the same to give this:
SELECT DECODE (min(ptemp.psl_strategy),
               5, 'Non-PSL',
               4, 'Mixed (P, LD, L)',
               3, 'Mixed (P Only)',
               2, 'PSL (P, LD, L)',
               1, 'PSL (P Only)',
               ''
              ),
       MAX (TO_NUMBER (temp.strategy_source))
INTO l_psl_strategy,
     l_strategy_source
FROM rule_table temp, strategy_rule cs
WHERE temp.strategy_rule_id = cs.strategy_rule_id
AND inventory_item_id = z1 (indx1).inventory_item_id
AND NVL (priority, -1) IN (SELECT NVL (MIN (cs.priority), -1)
                           FROM rule_table temp, strategy_rule cs
                           WHERE temp.strategy_rule_id = cs.strategy_rule_id
                           AND inventory_item_id = z1 (indx1).inventory_item_id);


If you can then combine those selects into the update you can do it in a single statement and it should run a lot faster.
Re: update block issue [message #445560 is a reply to message #445535] Tue, 02 March 2010 08:19 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks CookieMonster.
Previous Topic: model-clause with interdependend parameters
Next Topic: Getting Insert Statements
Goto Forum:
  


Current Time: Wed Sep 28 07:25:23 CDT 2016

Total time taken to generate the page: 0.14565 seconds