Overall performance for SQL Vs PL/SQL Engine

[Jr]

Plan 1)

declare
.
.
for i in ( SELECT /*+FULL(T) */ COL2 From TABLE1 Where COL1='SSSS') loop

Update TABLE2 ...Where COL1 = I.COL2;
.
.
end loop;
end;

Plan 2)
declare
.
.
for i in ( SELECT /*+FULL(T) */ CO1,COL2 From TABLE1 ) loop
If I.COL1='SSSS' Then
Update TABLE2 ...Where COL1 = I.COL2;
.
.
End If;
end loop;
end;

If aprx 30% of data is being referenced from table TABLE1 ( a VVLarge table over 800Million Records) , then which one would be a better approach ?

What are PROS and CONS in between the above plans ?

Thanks
Jr.

[tamilselvan]

What is your benchmark results?

[DaPi]

I'd expect plan(1) to be more efficient because you are eliminating the unwanted data as early as possible in the processing. However the saving is likely to be tiny fraction of the whole select & update work-load. As Tamil says, benchmark.

Can't you do it with just one sql statement?

BTW the hint won't do anything - you don't have an alias T.