Re: View with functions (Oracle 7.3)

A copy of this was sent to "Ruud de Gunst" <rdgunst_at_worldonline.nl> (if that email address didn't require changing) On Wed, 29 Sep 1999 12:53:02 +0200, you wrote:

>Hi DBA's,
>
>If I have a view definition (in Oracle 7.3) like the following:
>
>create or replace view vw_dummy
>as
>select
> function1(col1) c1
> ,function2(col2) c2
> ,function3(col3) c3
>from
> dummy
>;
>
>and the following query is executed:
>
>select
> c1
>from
> vw_dummy
>;
>
>will Oracle process all the columns in the view, and thus execute all
>functions for all columns, or just function1?
>
>From what I know about how Oracle handles queries on a view, I would say it
>will process the entire view, not just function1.
>
>Am I right?
>

no, it'll just call f1. consider this example:

create or replace function f1(x in number) return number as begin return 1; end; /
create or replace function f2(x in number) return number as begin return 1; end; /
create or replace function f3(x in number) return number as begin return 1; end; /

create or replace view v
as
select f1(1) f1, f2(2) f2, f3(3) f3 from dual /

alter session set sql_trace=true;
select f1 from v
/

The tkprof of the trace file shows me:

select f1
from
 v

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          2          1          2           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          2          1          2           1

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 10 (TKYTE)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (FULL) OF 'DUAL'

********************************************************************************

begin :r:="F1"(:a1);end;

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           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

only f1 was called -- f2 and f3 were not...

>Thanks,
>Ruud de Gunst
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 29 1999 - 15:42:33 CDT