Reading very first question to this thread, I failed to see why Isaac didn’t use CASE instead of that PL/SQL function (btw, Tom pointed this out too).

Anyway, sometimes CASE and other built-in SQL function just do not help us to satisfy our demands -- then what we have to do is to "use a bit of PL/SQL".

As far as I know, we can not predict in all possible cases how many times this or that function in SQL query will be invoked (or can we???). But if we know that NDV (number of distinct values) for our CODE column is low compared to cardinality of our result set -- then we can make use of scalar subquery caching, I believe.

Consider:

SQL> create or replace function SomeFunc (code in varchar2) return varchar2 is
  2    l_ClientId number :=
  3      nvl(to_number(sys_context('USERENV','CLIENT_IDENTIFIER')), 0);
  4  begin
  5    dbms_session.set_identifier(to_char(l_ClientID + 1));
  6    -- For simplicity, just return that "code" parameter
  7    return code;
  8  end;
  9  /

Function created.

SQL> create table SomeTab as
  2  select 'AskTom' as Code
  3    from all_objects
  4   where rownum <= 1000;

Table created.

SQL> set autotrace traceonly stat
SQL>
SQL> select SomeFunc(code) from SomeTab;

1000 rows selected.


Statistics
----------------------------------------------------------
       1022  recursive calls
          0  db block gets
       3083  consistent gets
          2  physical reads
          0  redo size
      13424  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> set autotrace off
SQL>
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') Cnt
  2    from dual;

CNT
------------------------------------------------------------
1000

That’s what we might expect: function was called for each row though CODE column contained the same data for all rows.
Now, let’s go on with scalar subquery:

SQL> exec dbms_session.set_identifier(null)

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly stat
SQL>
SQL> select (select SomeFunc(code) from dual) from SomeTab;

1000 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         77  consistent gets
          0  physical reads
          0  redo size
      13440  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> set autotrace off
SQL>
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') Cnt
  2    from dual;

CNT
------------------------------------------------------------
1

That’s what we need, right? Look at CNT and also at number of "recursive calls" -- they both went down.

Cheers,
Andrew. 

CREATE GLOBAL TEMPORARY TABLE Simulate_Sys_Context (
   Attribute  VARCHAR2(30)  NOT NULL,
   Value      INTEGER
)
ON COMMIT DELETE ROWS;

CREATE TABLE x (
   id   INTEGER NOT NULL,
   id2  INTEGER NOT NULL
);

BEGIN
   FOR i IN 1..1000 LOOP
      INSERT INTO x VALUES (1, MOD(i, 100));
   END LOOP;

   COMMIT;
END;
/

-- ----------------------------------------------------------------------
-- Function: returns the bind value.
-- ----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION Get_Bind_Value (
   p_Attribute  IN  VARCHAR2
)
RETURN NUMBER
AS
   CURSOR c IS
      SELECT Value
      FROM   Simulate_Sys_Context
      WHERE  Attribute = p_Attribute;

BEGIN
   FOR x IN c LOOP
      RETURN x.Value;
   END LOOP;
END Get_Bind_Value;
/

-- ----------------------------------------------------------------------
DECLARE
   TYPE REFCURSOR IS REF CURSOR;
   l_Cursor       REFCURSOR;
   l_SQL          LONG;
   l_cnt          PLS_INTEGER;

BEGIN
   INSERT INTO Simulate_Sys_Context VALUES ('id2', 2);

   l_SQL :=
     'SELECT COUNT(*) cnt FROM x WHERE id2 = Get_Bind_Value(''id2'')';

   OPEN l_Cursor FOR l_SQL;
   FETCH l_Cursor INTO l_cnt;
   CLOSE l_Cursor;

   dbms_output.put_line(l_cnt);
END;
/

COMMIT;

********************************************************************************
From the trace file
********************************************************************************
SELECT COUNT(*) cnt 
FROM
 x WHERE id2 = Get_Bind_Value('id2')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.06       0.09          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.06       0.09          0          8          0           1


SELECT VALUE 
FROM
 SIMULATE_SYS_CONTEXT WHERE ATTRIBUTE = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute   1000      0.01       0.00          0          0          0           0
Fetch     1000      0.01       0.00          0       3000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2000      0.03       0.00          0       3000          0        1000


********************************************************************************
After making Get_Bind_Value function deterministic, it only gets called once.
********************************************************************************

SELECT COUNT(*) cnt 
FROM
 x WHERE id2 = Get_Bind_Value('id2')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

SELECT VALUE 
FROM
 SIMULATE_SYS_CONTEXT WHERE ATTRIBUTE = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           1

FUNCTION f_knowledge_time RETURN TIMESTAMP DETERMINISTIC IS





BEGIN

 RETURN SYS_CONTEXT ('APP_USR_CTX', 'KNOWLEDGE_TIME');

END f_knowledge_time;

CREATE OR REPLACE VIEW V_A_RD_DATA_ENTRY
AS
  SELECT 1 RN,
    V.RD_TYPE_CODE,
    V.RD_TYPE,
    V.RD_CODE,
    V.VERSION_VALID_FROM,
    V.CRE_USER,
    V.CRE_DATIM,
    V.IS_DELETING_VERSION,
    V.DELETES_VERSION_ID,
    V.CODE_1,
    V.CODE_2,
    VERSION_VALID_UNTIL AS version_valid_till,
    CAST ( MULTISET
    (SELECT RD_TYPE_CODE,
      VERSION_ID,
      LANGUAGE_CODE,
      SHORT_DESCRIPTION,
      LONG_DESCRIPTION
    FROM RD_DATA_ENTRY_T
    WHERE RD_DATA_ENTRY_T.RD_TYPE_CODE = V.RD_TYPE_CODE
    AND RD_DATA_ENTRY_T.VERSION_ID     = V.VERSION_ID
    ) AS RD_DATA_ENTRY_T_TAB_TYPE) RD_DATA_ENTRY_T
  FROM RD_DATA_ENTRY_V v
  JOIN RD_DATA_ENTRY_DT dt
  ON dt.RD_TYPE_CODE = v.RD_TYPE_CODE
  AND f_knowledge_time BETWEEN dt.CRE_DATIM AND dt.TERM_DATIM
  WHERE 1 = 1
  AND f_knowledge_time BETWEEN v.CRE_DATIM AND v.TERM_DATIM;

Suggested Approach
--------------------
CREATE OR REPLACE VIEW V_A_RD_DATA_ENTRY
AS
  SELECT 1 RN,
    V.RD_TYPE_CODE,
    V.RD_TYPE,
    V.RD_CODE,
    V.VERSION_VALID_FROM,
    V.CRE_USER,
    V.CRE_DATIM,
    V.IS_DELETING_VERSION,
    V.DELETES_VERSION_ID,
    V.CODE_1,
    V.CODE_2,
    VERSION_VALID_UNTIL AS version_valid_till,
    CAST ( MULTISET
    (SELECT RD_TYPE_CODE,
      VERSION_ID,
      LANGUAGE_CODE,
      SHORT_DESCRIPTION,
      LONG_DESCRIPTION
    FROM RD_DATA_ENTRY_T
    WHERE RD_DATA_ENTRY_T.RD_TYPE_CODE = V.RD_TYPE_CODE
    AND RD_DATA_ENTRY_T.VERSION_ID     = V.VERSION_ID
    ) AS RD_DATA_ENTRY_T_TAB_TYPE) RD_DATA_ENTRY_T
  FROM RD_DATA_ENTRY_V v
  JOIN RD_DATA_ENTRY_DT dt
  ON dt.RD_TYPE_CODE = v.RD_TYPE_CODE
AND SYS_CONTEXT ('APP_USR_CTX', 'KNOWLEDGE_TIME') BETWEEN dt.CRE_DATIM AND dt.TERM_DATIM
  WHERE 1 = 1
  AND SYS_CONTEXT ('APP_USR_CTX', 'KNOWLEDGE_TIME') BETWEEN v.CRE_DATIM AND v.TERM_DATIM;

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 12566 | 1067K| 48502 (8)| 00:09:43 |
| 1 | LOAD AS SELECT | X109 | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 12566 | 1067K| 48474 (8)| 00:09:42 |
|* 4 | TABLE ACCESS FULL | FI_NON_SPLITTING_CL_V | 12566 | 687K| 23303 (16)| 00:04:40 |
|* 5 | INDEX UNIQUE SCAN | KM_FI_INST_PKX | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| KM_FI_INSTRUMENT | 1 | 31 | 2 (0)| 00:00:01 |

begin
  dbms_output.enable();
end;
/

create or replace function f_det(arg varchar2) return varchar2 deterministic as
begin
dbms_output.put_line('called f_det with arg ' || arg);
return arg;
end;
/

select f_det('x') two_explicit_f_det from dual
union all
select f_det('x') two_explicit_f_det from dual
;

select f_det('x') one_explicit_f_det from (
select 1 from dual
union all
select 1 from dual)
;

create or replace function f_not_det(arg varchar2) return varchar2 as
begin
dbms_output.put_line('called f_not_det with arg ' || arg);
return arg;
end;
/

select f_not_det('x') two_explicit_f_not_det from dual
union all
select f_not_det('x') two_explicit_f_not_det from dual
;

select f_not_det('x') one_explicit_f_not_det from (
select 1 from dual
union all
select 1 from dual)
;

anonymer Block abgeschlossen
FUNCTION F_DET kompiliert
TWO_EXPLICIT_F_DET
------------------
x
x

ONE_EXPLICIT_F_DET
------------------
x
x

FUNCTION F_NOT_DET kompiliert
TWO_EXPLICIT_F_NOT_DET
----------------------
x
x

ONE_EXPLICIT_F_NOT_DET
----------------------
x
x

(two rows for each query)


dbms_output:
called f_det with arg x
called f_det with arg x

called f_det with arg x <-- only one function call here

called f_not_det with arg x
called f_not_det with arg x

called f_not_det with arg x
called f_not_det with arg x