Drop in replacement for V and NV function

If you have read my previous posting about Caution when using PL/SQL functions in SQL statements and a related thread on the OTN forum, you know that using the V or NV function in your SQL-where clause can have a performance impact on your query.

I have created wrapper functions for V and NV which use the DETERMINISTIC optimizer hint, so that the optimizer/query engine just calls them once for the query and not for every row.

I have also added a DV function which is simular to NV. It will return the value as date.

Installation instructions can be found in the script header.

NOTE: This drop in replacement may improve performance, but it would be best if you avoid the V function in your SQL statement, always use the bind variable syntax (:PX_VARIABLE) instead! That’s is the best for the optimizer and it works with all Oracle DB versions.

DATABASE VERSION: I was just informed by Tom Kyte that

10gr2 supports deterministic as an optimization in SQL for the first
time. Prior to 10gr2 – deterministic was all about function based indexes only – the SQL engine ignored it.

So only if you have a 10.2.x database, this drop in replacement will give you a speed up of your query.

UPDATE: Seems that my link for the file doesn’t work. Added the content of the script to the posting.

--******************************************************************************
--
--  PROJECT:  Apex Library
--
--  FILE:     ApexLib_V_functions_replacement.sql
--
--  DESCRIPTION:
--
--    This script contains a drop in replacement for the APEX functions
--    V and NV which are used to read the value of an APEX item.
--
--    Why do I need a replacement for them? Checkout my blog posting at
--    /2006/11/caution-when-using-plsql-functions-in.html
--    and the OTN forum discussion at
--    http://forums.oracle.com/forums/thread.jspa?threadID=445423&messageID=1576799
--
--    This script also contains a DV function (for date) to complete the existing
--    V (for string) and NV functions (for number).
--
--  NOTE:
--
--    The package has to be loaded into the schema where your application tables
--    are located. That should be the same schema as it is specified by
--    "First Schema Provisioned" at "Manage Workspaces\Workspace Details".
--    => search for "First Schema Provisioned" in the online help.
--
--    If you use a other version than APEX 2.2.x then you have to replace the
--    schema FLOWS_020200 with the schema your version is using (eg FLOWS_030000)
--
--  SUPPORTED APEX VERSIONS:
--
--    2.x, 3.x
--
--  AUTHORS:
--
--    PW: Patrick Wolf (http://www.inside-oracle-apex.com/)
--
--  SVN HEADER:
--
--    $Id: ApexLib_V_functions_patch.sql 140 2007-02-08 19:26:56Z patrick_wolf $
--
--******************************************************************************
--
CREATE OR REPLACE FUNCTION V
  ( p_item   IN VARCHAR2
  , p_flow   IN NUMBER   := NULL
  , p_scope  IN VARCHAR2 := 'SESSION_AND_USER'
  , p_escape IN VARCHAR2 := 'N'
  )
  RETURN VARCHAR2 DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See /2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    RETURN FLOWS_020200.V
             ( p_item   => p_item
             , p_flow   => p_flow
             , p_scope  => p_scope
             , p_escape => p_escape
             );
END V;
/
--
CREATE OR REPLACE FUNCTION NV
  ( p_item   IN VARCHAR2
  )
  RETURN NUMBER DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX NV function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See /2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    RETURN TO_NUMBER(FLOWS_020200.V(p_item));
END NV;
/
--
CREATE OR REPLACE FUNCTION DV
  ( p_item        IN VARCHAR2
  , p_format_mask IN VARCHAR2 := NULL
  )
  RETURN DATE DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See /2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    IF p_format_mask IS NOT NULL
    THEN
        RETURN TO_DATE(FLOWS_020200.V(p_item), p_format_mask);
    ELSE
        RETURN TO_DATE(FLOWS_020200.V(p_item));
    END IF;
END DV;
/

8 thoughts on “Drop in replacement for V and NV function

  1. We have 10.2.0.2 and db_link to 9.2.06.

    In view on 10g side there is call like:

    select * from ora92_table
    where id = v(‘APEX_ITEM_VALUE’)

    What is fancy that saving that query in Apex, creates loooong query …

    This is just a small add to 10g2 deterministic function which should solve the problem of that…looks like the problems still remains …

    Just my proved 2 c!

    P.S.
    BTW, how do you generate these “check images”? THX

  2. Hi Damir,

    I’m not so familiar with database links, especially if different version are involved. I’m not sure which optimizer (on which db) is responsible. I think the 9.2 one is asked at least for one part of the query (his tables), because only he can has the statistics and can decide what is the best explain plan.

    Be on the save side in that case and use

    SELECT * FROM ORA92_TABLE WHERE ID = (SELECT V(‘APEX_ITEM_VALUE’) FROM DUAL);

    That always works, with all DB versions!

    Which “check images” do you mean?

    Patrick

  3. Hi, Patrick
    I think that it is better to add format mask to DV function.
    Some thing like:
    CREATE OR REPLACE FUNCTION DV
    ( p_item IN VARCHAR2, p_fm in varchar2
    )
    RETURN DATE DETERMINISTIC
    IS
    BEGIN
    RETURN TO_DATE(FLOWS_020200.V(p_item), p_fm);
    END DV;
    /

    Lev

  4. Hi Lev,

    you are right, I have added an optional p_format_mask parameter.

    But anyway, it’s better to use the ApexLib function ApexLib_Item.DV, because it automatically knows which format mask has to be used for the conversion.

    Thanks for your suggestion
    Patrick

  5. Patrick,

    Sorry to comment on such an old post, but I just got burned by the nv() function, and noticed that your version here has the same (potential) issue. It’s basically the same issue noted by Lev on the dv() function–nv() doesn’t pay attention to format masks. So if a number has a grouping separator, nv() throws an invalid number error. I posted a solution to this using the apex dictionary to get the proper format mask on my blog.

    -David

  6. Hi David,

    actually the drop in replacement for V, NV and DV where just to fix the deterministic problem. In my ApexLib framework I have implemented ApexLib_Item.NV and ApexLib_Item.DV which are taking care of that.

    Regards
    Patrick

Comments are closed.