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

    Unanswered: VIEW question - functions

    If you had a view like
    Code:
    create or replace view v1 as
    select PK,
           DECODE(...) as f1,
           DECODE(...) as f2
    from table, table ..
    and you ran a query like:
    Code:
    select PK from v1
    would Oracle actually process the DECODE(), even though you didn't request the results?

    -Chuck

  2. #2
    Join Date
    Sep 2004
    Posts
    60
    As you queried View & not the base table, it would process DECODE.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    No it may or may not process the decode depending on the execution plan it chooses. Dont forget Oracle can merge views into your main query and can thus decide not do the decode if you dont select it.

    Alan

  4. #4
    Join Date
    Sep 2004
    Posts
    60
    Thanks for correction Alan !!

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    How can you tell? I've not seen any reference to the contents of the SELECT clause in the execution plan.

    I am asking because we have some views built for a specific purpose, but they could also be useful for reporting. In some cases they have 5 or more functions, some are more complex functions than a simple decode (although those functions don't query tables).

    -Chuck

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I guess this works for me, even though it's not the exact query structure that we are using on site:

    Code:
    SQL> create or replace view test_v
      2  as
      3  select 1 as foo, (select count(*) from all_tables) as bar
      4  from dual;
    
    View created.
    
    SQL> set autotrace on
    SQL> select foo from test_v;
    
           FOO
    ----------
             1
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
       1    0   FAST DUAL (Cost=2 Card=1)
    
    SQL> select bar from test_v;
    
           BAR
    ----------
          3557
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     FILTER
       3    2       HASH JOIN (Cost=430 Card=47 Bytes=6815)
       4    3         HASH JOIN (Cost=405 Card=47 Bytes=6627)
       5    4           HASH JOIN (OUTER) (Cost=398 Card=47 Bytes=6439)
       6    5             NESTED LOOPS (OUTER) (Cost=390 Card=47 Bytes=6251)
       7    6               NESTED LOOPS (OUTER) (Cost=343 Card=47 Bytes=5734)
       8    7                 NESTED LOOPS (OUTER) (Cost=299 Card=47 Bytes=5358)
       9    8                   NESTED LOOPS (Cost=299 Card=47 Bytes=5123)
      10    9                     MERGE JOIN (CARTESIAN) (Cost=252 Card=705 Bytes=55695)
    .....
    -cf

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This experiment shows that only the relevant functions are being called (in this particular case):
    Code:
    SQL> create or replace function fun1(x in number) return number is
      2  begin
      3    dbms_output.put_line('fun1 called');
      4    return x;
      5  end;
      6  /
    
    Function created.
    
    SQL> create or replace function fun2(x in number) return number is
      2  begin
      3    dbms_output.put_line('fun2 called');
      4    return x;
      5  end;
      6  /
    
    Function created.
    
    SQL> create or replace view v1 as select empno, fun1(empno) x1, fun2(empno) x2 from emp;
    
    View created.
    
    SQL> set serverout on size 1000000
    SQL> select empno, x1 from v1;
    
         EMPNO         X1
    ---------- ----------
          7839       7839
          7698       7698
          7782       7782
          7566       7566
          7788       7788
          7902       7902
          7369       7369
          7499       7499
          7521       7521
          7654       7654
          7844       7844
          7876       7876
          7900       7900
          7934       7934
    
    14 rows selected.
    
    SQL> exec null;
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    fun1 called
    
    PL/SQL procedure successfully completed.
    
    SQL> select empno, x2 from v1;
    
         EMPNO         X2
    ---------- ----------
          7839       7839
          7698       7698
          7782       7782
          7566       7566
          7788       7788
          7902       7902
          7369       7369
          7499       7499
          7521       7521
          7654       7654
          7844       7844
          7876       7876
          7900       7900
          7934       7934
    
    14 rows selected.
    
    SQL> exec null;
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    fun2 called
    
    PL/SQL procedure successfully completed.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How can you tell? Thats the difficult bit as you rightly point out the execution plan doesnt tell you about the select clause. What you can say is that if your view is merged into your main query Oracle wont process any columns which arent used. However if you have say a group by clause in your view or something which prevents merging then it will process every column.

    The way I proved it was to create a view which had a column which did a to_number() on a char field i.e. it would fail if it executed the to_number. If I didnt select the to_number column it worked fine, if I selected the to_number column it failed.

    I then added a group by clause which would prevent view merging and then even if I didnt select the to_number column it always failed.

    So you will have to look at the execution plans on your selct statements, if it looks like its merging the view into the query your functions probably wont get executed unless they are selected.

    Alan

Posting Permissions

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