Results 1 to 4 of 4

Thread: SQL query

  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: SQL query

    Consider a table with two columns

    Table Name= table1
    name varchar2 => this is the primary key
    value varchar2

    Two PL/SQL blocks

    declare
    v_name table1.name%type;
    v_value table1.value%type;
    begin
    v_name:='123';
    select value into v_value from table1 where name=v_name;
    end;
    /

    and another block

    declare
    v_name table1.name%type;
    v_value table1.value%type;
    begin
    v_name:='123';
    select value into v_value from table1 where name=''||v_name||';
    end;
    /

    Will there be any difference in performance b/w the two blocks

    cheers
    pushp

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL query

    Yes - the second block won't compile! Perhaps you meant:

    declare
    v_name table1.name%type;
    v_value table1.value%type;
    begin
    v_name:='123';
    EXECUTE IMMEDIATE 'select value from table1 where name='''||v_name||'''' into v_value;
    end;
    /

    ... in which case the answer is that there will be negligible difference for such a small program, but that the first is MUCH better than the second as a general approach, or if the code is to be invoked MANY times with different values of v_name. This is because the first uses BIND VARIABLES.

    The second could also be re-written to use bind variables:

    declare
    v_name table1.name%type;
    v_value table1.value%type;
    begin
    v_name:='123';
    EXECUTE IMMEDIATE 'select value from table1 where name=:x' into v_value USING v_name;
    end;
    /

    But the first block still has the edge performance-wise, because it doesn't parse the statement each time it is called.

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    sorry the second block shud have been
    declare
    v_name table1.name%type;
    v_value table1.value%type;
    begin
    v_name:='123';
    select value into v_value from table1 where name=''||v_name||'';
    end;
    /

    where the '' in (''||v_name||'' ) are two single quotes and not a single double quote.
    The block compiles fine.

    Well, the reason I do ask this is bcoz the first block causes a full table scan if value of v_name parameter contains a number.
    whereas the second one does not.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Now they are the same, as far as I can see. You are concatenating NULL ('') onto the front and back of the bind variable v_name.

    I don't see how the optimizer could "know" that v_name contains a number, since it is a varchar2 bind variable.

    If you got this information from TKPROF, perhaps you could post the output here?

Posting Permissions

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