Update in a Loop [message #498849] |
Fri, 11 March 2011 10:49 |
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 #498851 is a reply to message #498849] |
Fri, 11 March 2011 10:59 |
ThomasG
Messages: 3212 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 |
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 |
flyboy
Messages: 1903 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.
|
|
|