Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: function w/ or w/out SQL

    We're migrating a system into Oracle, but it won't be there for awhile. We'd like to go ahead and build a complex function in Oracle in the meantime, though, instead of writing it once in T-SQL and then again in PL/SQL. Actually, it would be ideal if this were eventually a VIEW, and so here's my question:

    Would we really be creating an unscalable solution by embedding a SQL statement against DUAL in a FUNCTION, when that FUNCTION would be called about 10,000x per report?

    Code:
    create or replace function what_number (p_num number) return varchar2
    as
    begin
      if nvl(p_num, 1) < 5 then
        return 'under 5'
      else
        return 'over 5'
      end if;
    end;
    vs

    Code:
    create or replace function what_number (p_num number) return varchar2
    as
      rtn varchar2(20);
    begin
      select (case when p_num < 5
                  then 'under 5'
                  else 'over 5
              end) as range
             into rtn
      from dual;
    
      return rtn;
    end;
    In my head, when we finally get everything migrated over to Oracle, then we can convert the second approach more easily into a VIEW, since the SQL structure is already in place, and then avoid context switching between SQL & PL/SQL.

    Code:
    create or replace view what_number_v 
    as
    select (case when order_cnt < 5
                 then 'under 5'
                 else 'over 5
            end) as range       
    from orders;
    Is the select against DUAL in the meantime bad? I know that embedding SQL in a PL/SQL FUNCTION is poor design, since the optimizer cannot include it when designing an explain plan. And it basically sets up a SQL-PL/SQL-SQL context switch.

    Does it sound like a bad approach?

    ---=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You could get resourceful & be like Tom Kyte & benchmark both possible solutions to actually measure for yourself;
    rather than depend upon free advice from folks who won't pay any price if they are wrong.

    How hard would it be to write a FOR LOOP that executes each a million times & measures how it takes to complete each?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I thought about that right after I posted. It looks like it takes about 7 times as long to execute the FUNCTION containing SQL against dual for 10,000 executions, and 100,000.

    Oh, and the view is about 2.5x faster then the FUNCTION that doesn't contain SQL.

    ---=Chuck
    Last edited by chuck_forbes; 05-02-07 at 18:42.

Posting Permissions

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