database performance - Oracle 11gR2 - View Function Columns Evaluation -

i seem have odd issue regarding oracle view has functions defined columns , when functions evaluated.

let's have following view , function definition:

create or replace view test_view_one select column_one,        a_package.function_that_returns_a_value(column_one) function_column   a_table;  create or replace package body a_package      function function_that_returns_a_value(p_key  varchar2) return varchar2        cursor a_cur           select value             table_b            key = p_key;       p_temp   varchar2(30);     begin     -- code here write temp table. function call autonomous.       open a_cur;       fetch a_cur p_temp;       close a_cur;        return p_temp;     end function_that_returns_a_value; end a_package; 

in general, expect if function_column included in query every row brought query, function run. seems true in circumstances not others.

for example, let's have following:

select pageouter,*  from(with page_query (select *                             test_view_one                          order column_one) select page_query.*, rownum innerrownum page_query rownum <= 25) pageouter pageouter.innerrownum >= 1 

in scenario, inner query (the 1 querying test_view_one) brings around 90,000 records. if define function inserting temporary table can tell function ran 25 times, once each row brought back. expect.

however, if add significant clause on inner query, e.g.

select pageouter,*    from(with page_query (select *                               test_view_one                             exists (select 'x' some_table ...)                             , not exists (select 'x' some_other_table ...)                             , exists (select 'x' another_table ...)                            order column_one)   select page_query.*, rownum innerrownum   page_query   rownum <= 25) pageouter pageouter.innerrownum >= 1 

then number of rows being brought inner query 60,000 , if query temporary table, can tell function has run 60,000 times. unsurprisingly, pretty destroys performance of query.

the queries above run part of paging implementation why ever bring 25 rows , why ever need functions run 25 rows.

i should add, if change clause (i.e. remove of conditions) query goes behaving self, running functions 25 rows brought back.

does have idea when functions in views evaluated? or anyway in determining causes or way of identifying when functions evaluated (i've checked explain plan , there's nothing in there seems give away). if knew find solution problem there seems little documentation other "they'll run each row brought back" not case in scenarios.

i appreciate it's difficult work out what's going on without working schema if need anymore info please feel free ask.

many thanks

---

additional info requested.

below actual explain plan out of production environment. table names don't match above query (in fact there's considerably more tables involved they're joined not exists statements within clause.) demise table, equivalent of a_table in above query.

it's worth noting stats gathered before ran explain plan make accurate possible.

my understanding of view row functions evaluated, occurs after rows have been filtered down. understanding flawed!

so bad plan, 1 calls function 60,000 times...

execution plan ----------------------------------------------------------  ------------------------------------------------------------------------------------------- | id  | operation                              | name        | rows  | bytes | cost (%cpu)| ------------------------------------------------------------------------------------------- |   0 | select statement                       |             |     5 | 10230 |   984   (1)| |   1 |  fast dual                             |             |     1 |       |     2   (0)| |   2 |  fast dual                             |             |     1 |       |     2   (0)| |*  3 |  view                                  |             |     5 | 10230 |   984   (1)| |*  4 |   count stopkey                        |             |       |       |            | |   5 |    view                                |             |     5 | 10165 |   984   (1)| |*  6 |     sort order stopkey              |             |     5 |   340 |   984   (1)| |   7 |      count                             |             |       |       |            | |*  8 |       filter                           |             |       |       |            | |*  9 |        hash join right outer           |             |  5666 |   376k|   767   (1)| |* 10 |         index range scan               | userdatai1  |     1 |    12 |     2   (0)| |* 11 |         hash join right anti           |             |  5666 |   309k|   765   (1)| |* 12 |          index fast full scan          | tnntmvini1  |     1 |    17 |    35   (0)| |* 13 |          hash join right anti          |             |  6204 |   236k|   729   (1)| |* 14 |           index range scan             | codesgeni3  |     1 |    10 |     2   (0)| |* 15 |           index full scan              | demisei4    |  6514 |   184k|   727   (1)| |  16 |            nested loops                |             |     1 |    25 |     3   (0)| |  17 |             nested loops               |             |     1 |    25 |     3   (0)| |* 18 |              index range scan          | property_gc |     1 |    15 |     2   (0)| |* 19 |              index unique scan         | codesgeni1  |     1 |       |     0   (0)| |* 20 |             table access index rowid| codesgen    |     1 |    10 |     1   (0)| |  21 |        table access full               | qcdual      |     1 |       |     3   (0)| |* 22 |        index range scan                | dmseleasi4  |     1 |    21 |     2   (0)| |* 23 |        index range scan                | tnntmvini1  |     1 |    17 |     1   (0)| |  24 |        table access full               | qcdual      |     1 |       |     3   (0)| ------------------------------------------------------------------------------------------- 

this good plan. calls function 25 times has of not exists statements removed clause.

execution plan ----------------------------------------------------------  ---------------------------------------------------------------------------------------- | id  | operation                            | name       | rows  | bytes | cost (%cpu)| ---------------------------------------------------------------------------------------- |   0 | select statement                     |            |    25 | 54200 |   144   (0)| |   1 |  fast dual                           |            |     1 |       |     2   (0)| |   2 |  fast dual                           |            |     1 |       |     2   (0)| |*  3 |  view                                |            |    25 | 54200 |   144   (0)| |*  4 |   count stopkey                      |            |       |       |            | |   5 |    view                              |            |    26 | 56030 |   144   (0)| |   6 |     count                            |            |       |       |            | |*  7 |      filter                          |            |       |       |            | |   8 |       nested loops anti              |            |    30 |  3210 |   144   (0)| |   9 |        nested loops outer            |            |    30 |  2580 |   114   (0)| |  10 |         nested loops anti            |            |    30 |  2220 |    84   (0)| |  11 |          nested loops anti           |            |    32 |  1824 |    52   (0)| |  12 |           table access index rowid| demise     |   130k|  5979k|    18   (0)| |  13 |            index full scan           | demisei4   |    34 |       |     3   (0)| |* 14 |           index range scan           | codesgeni3 |     1 |    10 |     1   (0)| |* 15 |          index range scan            | tnntmvini1 |     1 |    17 |     1   (0)| |* 16 |         index range scan             | userdatai1 |     1 |    12 |     1   (0)| |* 17 |        index range scan              | dmseleasi4 |     1 |    21 |     1   (0)| |  18 |       table access full              | qcdual     |     1 |       |     3   (0)| ---------------------------------------------------------------------------------------- 

i appreciate second plan doing less doesn't explain why functions aren't being evaluated... @ least not can work out.

the pagination rownum may performed in 2 ways:

a) full scan row source optimized sorting (limited top n rows) or

b) index access of row source no sort @ all

here simplified example of case a

 select *     (select a.*,      rownum rnum         ( select * test_view_one order id      )    rownum <= 25    )  rnum >= 1 

the corresponding execution plan looks follows (note presend part of column projection - explain why):

 -----------------------------------------------------------------------------------------  | id  | operation                | name | rows  | bytes |tempspc| cost (%cpu)| time     |  -----------------------------------------------------------------------------------------  |   0 | select statement         |      |    25 |   975 |       |  1034   (1)| 00:00:01 |  |*  1 |  view                    |      |    25 |   975 |       |  1034   (1)| 00:00:01 |  |*  2 |   count stopkey          |      |       |       |       |            |          |  |   3 |    view                  |      | 90000 |  2285k|       |  1034   (1)| 00:00:01 |  |*  4 |     sort order stopkey|      | 90000 |   439k|  1072k|  1034   (1)| 00:00:01 |  |   5 |      table access full   | test | 90000 |   439k|       |   756   (1)| 00:00:01 |  -----------------------------------------------------------------------------------------    column projection information (identified operation id):  -----------------------------------------------------------  ...      3 - "a"."id"[number,22], "a"."function_column"[number,22]     4 - (#keys=1) "id"[number,22], "my_package"."my_function"("id")[22]     5 - "id"[number,22]      

within execution table accessed full scan, i.e. records red. optimization in sort operation: sort order stopkey means not rows sorted, top 25 kept , sortet.

here execution plan case b

 --------------------------------------------------------------------------------  | id  | operation           | name     | rows  | bytes | cost (%cpu)| time     |  --------------------------------------------------------------------------------  |   0 | select statement    |          |    25 |   975 |     2   (0)| 00:00:01 |  |*  1 |  view               |          |    25 |   975 |     2   (0)| 00:00:01 |  |*  2 |   count stopkey     |          |       |       |            |          |  |   3 |    view             |          |    26 |   676 |     2   (0)| 00:00:01 |  |*  4 |     index range scan| test_idx |    26 |   130 |     2   (0)| 00:00:01 |  -------------------------------------------------------------------------------- 

here accessed required 25 rows , therefore function can't called more n times.

now important consideration, in case a, function can, need not called each row. how see it?

the answer in column projection in explain plan.

    4 - (#keys=1) "id"[number,22], "my_package"."my_function"("id")[22] 

the relevant line 4 show, function called in sort operation , therefor each line. (sort gets rows).

conclusion

my test on 11.2 shows in case (full scan sort order stopkey) view function called once per each row. guess workaround select id, limit result , join original view function value.

final notes

i tested in 12.1 , see below shift in column projection. function calculated first in view (line 3), i.e. both cases works fine.

 column projection information (identified operation id):  -----------------------------------------------------------  ...     3 - "a"."id"[number,22], "a"."function_column"[number,22]     4 - (#keys=1) "id"[number,22]     5 - "id"[number,22]         

and of course in 12c new feature of offset - fetch next used.

good luck!