CODEWITHSUNDEEP
sqloracle-databaseplsql
Victor Suarez
Victor Suarez

Reputation: 45

How can I make this pl/sql cursor more efficient?

Hi guys I have a pl/sql cursor that takes too long to execute. I want to know how can I make the same process but with better performance and probably better code. I am new to PL/SQL.

Declare
       Cursor Cursor1 is
       select * from table1 where
        field1 IS NULL
        or 
        field2  IS NULL or field3 IS NULL or field4 is null or field5 IS NULL or field6 IS NULL;

    Begin
       For i in Cursor1 loop

       if i.field1 IS NULL then

       update table1 set field1=0 where recordId=i.recordId;

       end if;

       if i.field2 IS NULL then

       update table1 set field2=0 where recordId=i.recordId;

       end if;  

       if i.field3 IS NULL then

       update table1 set field3=0 where recordId=i.recordId;

       end if;              

       if i.field4 IS NULL then

       update table1 set field4=0 where recordId=i.recordId;

       end if; 

       if i.field5 IS NULL then

       update table1 set field5=0 where recordId=i.recordId;

       end if;  

       if i.field6 IS NULL then

       update table1 set field6=0 where recordId=i.recordId;

       end if;               

       End loop;
    End;

The question basically is how can I update a field of one specific record, taking into account the conditions of the field. The thing is that the update can occur in the same record many times if the condition apply for many fields in the record.

Thanks...

Upvotes: 0

Views: 197

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Here's another take on this:

UPDATE TABLE1
  SET FIELD1 = NVL(FIELD1, 0),
      FIELD2 = NVL(FIELD2, 0),
      FIELD3 = NVL(FIELD3, 0),
      FIELD4 = NVL(FIELD4, 0),
      FIELD5 = NVL(FIELD5, 0),
      FIELD6 = NVL(FIELD6, 0);

Rationale: any query which performs this update is going to do a full table scan anyways because it's looking for NULLs, which won't be indexed in the usual case, and even if they ARE indexed there's a fair chance the optimizer will choose a full table scan anyways. Why waste time checking six different fields for NULLs?

Share and enjoy.

Upvotes: 3

Serpiton
Serpiton

Reputation: 3684

It's possible to do the same with one UPDATE

UPDATE table1 SET
  field1 = COALESCE(field1, 0)
, field2 = COALESCE(field2, 0)
, field3 = COALESCE(field3, 0)
, field4 = COALESCE(field4, 0)
, field5 = COALESCE(field5, 0)
, field6 = COALESCE(field6, 0)
WHERE field1 IS NULL OR field2 IS NULL OR field3 IS NULL
   OR field4 IS NULL OR field5 IS NULL OR field6 IS NULL

Upvotes: 3

Ivaylo Toskov
Ivaylo Toskov

Reputation: 4021

Try executing couple of updates like this, avoiding the usage of a cursor:

update table1 set field1=0 where field1 IS NULL;
update table1 set field2=0 where field2 IS NULL;
update table1 set field3=0 where field3 IS NULL;
update table1 set field4=0 where field4 IS NULL;
update table1 set field5=0 where field5 IS NULL;
update table1 set field6=0 where field6 IS NULL;

I don't think that there is a more efficient way to do this.

Upvotes: 0

Related Questions