Will an inline function execute if you don't select it from the view?-CodePudding

Say you have a View that selects a couple properties from a table. One of them is an inline function. After the view is created, will it execute the inline function if I don't select that property from the view?

CREATE OR REPLACE VIEW my_view AS
SELECT colA, colB, my_custom_function(colA) colC
FROM my_table;

Will the below query still execute the aforementioned my_custom_function?

SELECT colA, colB FROM my_view;

CodePudding user response：

Will the below query still execute the aforementioned my_custom_function?

No.

You can test it yourself:

CREATE FUNCTION my_custom_function(
  value NUMBER
) RETURN NUMBER
IS
BEGIN
  DBMS_SESSION.SLEEP(5);
  RETURN value;
END;
/

Then:

CREATE TABLE my_table (colA, colB) AS
SELECT 1, 2 FROM DUAL;

CREATE OR REPLACE VIEW my_view AS
SELECT colA, colB, my_custom_function(colA) colC
FROM my_table;

Then:

SELECT colA, colB FROM my_view;

If it does call the function then the query will take 5 seconds and if it doesn't call the function then it will return quickly.

The query returns quickly so the function is not called.

You can compare it to:

SELECT * FROM my_view;

Which takes 5 seconds to return the row.

db<>fiddle here

CodePudding user response：

Example based on Scott's sample schema.

Function sums salaries per department which is passed as a parameter. As you can see (line #7), it selects from the EMP table:

SQL> create or replace function my_custom_function(par_deptno in number)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select sum(sal) into retval
  7    from emp
  8    where deptno = par_deptno;
  9
 10    return retval;
 11  end;
 12  /

Function created.

View which selects data from the DEPT table and contains call to previously created function:

SQL> create or replace view my_view as
  2    select dname, loc, my_custom_function (deptno) sum_sal
  3    from dept;

View created.

OK; let's see what explain plan says for select statement that fetches only the first two columns from the view (i.e. which doesn't call the function):

SQL> explain plan for select dname, loc from my_view;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3383998547

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DEPT |
----------------------------------

8 rows selected.

SQL>

It selects rows from the dept table, but doesn't affect emp table at all. Therefore,

Will the below query still execute the aforementioned my_custom_function?

I'd say not.