Overall performance for SQL Vs PL/SQL Engine
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Overall performance for SQL Vs PL/SQL Engine

  1. #1
    Join Date
    Jan 2002
    Posts
    148

    Overall performance for SQL Vs PL/SQL Engine

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    What is your benchmark results?

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width