Home » RDBMS Server » Performance Tuning » Update in a Loop (Oracle 11g )
Update in a Loop [message #498849] Fri, 11 March 2011 10:49 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,

Can this be optimized, in dev and Ist we didn't realize since 1000 rows were there, but in PERF since 2 mil rows are there this is taking a long time,
can some one suggest a good approach for this

SET SERVEROUTPUT ON
DECLARE
  counter number := 0;
  
  CURSOR insertValues IS select roleid, productcode, functioncode, typecode, restrictiontype, value1 from restrictions where actionmode = 'INSERT';
  
BEGIN
  DBMS_OUTPUT.put_line('Copying insert limit values to modify limits...');

  FOR rec IN insertValues
  LOOP
   update restrictions set value1 = rec.value1 where roleid = rec.roleid AND productcode = rec.productCode AND restrictiontype = rec.restrictionType AND ACTIONMODE = 'MODIFY' AND value1 = '0';
   counter := counter + 1;
   COMMIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(counter || ' rows have been updated.');
END;
/



can this be done in a single update since Selects /Updates are happening on same table
Thanks

[Updated on: Fri, 11 March 2011 10:57]

Report message to a moderator

Re: Update in a Loop [message #498850 is a reply to message #498849] Fri, 11 March 2011 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Update in a Loop [message #498851 is a reply to message #498849] Fri, 11 March 2011 10:59 Go to previous messageGo to next message
ThomasG
Messages: 3100
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One way to speed up the whole process would be not to do it with PL/SQL but just with one SQL update statement. That would probably be the fastest way to to the overall update, but the rows in restrictions would be locked longer.

Is the reason you commit after each update that users are working with that table and they would run into the locked rows when you do it in one update?

Also, the information BlackSwan asked for would be needed to see how it could be done.


Re: Update in a Loop [message #498853 is a reply to message #498851] Fri, 11 March 2011 11:04 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
I tried to do in 1 single also getting this error
 update restrictions d set d.value1    = (select value1 from restrictions where roleid = d.roleid AND productcode = d.productCode AND restrictiontype = d.restrictionType AND
   ACTIONMODE = 'MODIFY' AND value1 = '0');


ORA-01427: single-row subquery returns more than one row


table structure if required
CREATE TABLE RESTRICTIONS
(
  USERGROUP          VARCHAR2(8 BYTE)           NOT NULL,
  ROLEID             VARCHAR2(8 BYTE)           NOT NULL,
  PRODUCTCODE        VARCHAR2(8 BYTE)           NOT NULL,
  FUNCTIONCODE       VARCHAR2(8 BYTE)           NOT NULL,
  TYPECODE           VARCHAR2(8 BYTE)           NOT NULL,
  ENTRYMETHOD        NUMBER(10)                 NOT NULL,
  ACTIONMODE         VARCHAR2(8 BYTE)           NOT NULL,
  RESTRICTIONTYPE    VARCHAR2(16 BYTE)          NOT NULL,
  RESTRICTIONCOLUMN  VARCHAR2(64 BYTE)          NOT NULL,
  DEPENDENCYCOLUMN1  VARCHAR2(64 BYTE),
  DEPENDENCYCOLUMN2  VARCHAR2(64 BYTE),
  DEPENDENCYCOLUMN3  VARCHAR2(64 BYTE),
  VALUE1             VARCHAR2(64 BYTE),
  VALUE2             VARCHAR2(64 BYTE),
  ACTIONTIMESTAMP    DATE,
  PROCESSORDER       NUMBER(10)
)
It has a 9 column Composite PK.


thanks for looking into it.

[Updated on: Fri, 11 March 2011 11:05]

Report message to a moderator

Re: Update in a Loop [message #498862 is a reply to message #498853] Fri, 11 March 2011 12:08 Go to previous message
flyboy
Messages: 1771
Registered: November 2006
Senior Member
The single UPDATE you posted is not equivalent with the initial LOOP. It does not contain WHERE clause, condition on ACTIONMODE = 'INSERT' and the other conditions are misplaced. So, it should look like this:
update restrictions d
set d.value1 = (select value1
                from restrictions
                where roleid = d.roleid
                  AND productcode = d.productCode
                  AND restrictiontype = d.restrictionType
                  AND ACTIONMODE = 'INSERT')
WHERE ACTIONMODE = 'MODIFY' AND value1 = '0';
Anyway, if the primary key contains more columns than (ROLEID, PRODUCTCODE, RESTRICTIONTYPE and ACTIONMODE), ORA-01427 error may still appear. In that case, either add all other primary key columns to join conditions, or (if thir equality is not required) determine, which of multiple values with ACTIONMODE = 'INSERT' shall be taken to UPDATE corresponding row(s) with ACTIONMODE = 'UPDATE'. The cursor actually would UPDATE the same rows multiple times, so the last fetched value outstayed there.
Previous Topic: help me tune this
Next Topic: help me tune this
Goto Forum:
  


Current Time: Tue Sep 30 08:28:18 CDT 2014

Total time taken to generate the page: 0.12957 seconds