Suppose we have a table with 10 rows:

SQL> select id from t10; ID ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected.

And we have the query:

select * from ( select xf(t10.id) a from t10 ) where a*a >= 25

At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.

Lets test it:

SQL> create or replace function xf(p int) return int as 2 begin 3 dbms_output.put_line('F fired!'); 4 return p; 5 end; 6 / Function created. SQL> set serverout on; SQL> select * 2 from ( 3 select xf(t10.id) a 4 from t10 5 ) 6 where a*a >= 25 7 / A ---------- 5 6 7 8 9 10 6 rows selected. F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 F fired! F fired! F fired! F fired! F fired! -- 15 F fired! F fired! F fired! F fired! F fired! -- 20 F fired! F fired! F fired! F fired! F fired! -- 25 F fired!

As you see, there are more than 10 executions, so lets see the execution plan:

SQL> @xplan +projection PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- Plan hash value: 2919944937 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T10"."ID"[NUMBER,22]

Now you see that inner view was merged, and the function was executed 20 times in the filter and 6 times on the fetch after filtering(6 rows – 6 calls).

I see that often in such cases “no_merge” hint is suggested, but let’s test it:

SQL> select * 2 from ( 3 select/*+ no_merge */ xf(t10.id) a 4 from t10 5 ) 6 where a*a >= 25 7 / A ---------- 5 6 7 8 9 10 6 rows selected. F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired! F fired!

As you can see, the number of function calls wasn’t changed.

And if we look into the plan, we understood why:

SQL> @xplan +projection PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- Plan hash value: 2027387203 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 | |* 2 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"[NUMBER,22] 2 - "T10"."ID"[NUMBER,22] 20 rows selected.

Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.

But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:

SQL> begin 2 dbms_sqltune.import_sql_profile( 3 sql_text => 'select * from (select xf(t10.id) a from t10) where a*a >= 25' 4 ,profile => sys.sqlprof_attr( 5 q'[NO_MERGE(@SEL$2)]' 6 ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]' 7 ) 8 ,category => 'DEFAULT' 9 ,name => 'TEST_PROFILE' 10 ,force_match => true 11 ,replace => true 12 ); 13 end; 14 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.16 SQL> set serverout on SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25; A ---------- 5 6 7 8 9 10 6 rows selected. F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 F fired! -- extra execution because of sql*plus Elapsed: 00:00:00.17 -- there are no extra calls when we fetches by 100 rows in pl/sql: SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop; F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 PL/SQL procedure successfully completed. Elapsed: 00:00:00.22

With changing arraysize -- with fetching by 1 row: SQL> declare 2 cursor c is select * from (select xf(t10.id) a from t10) where a*a >= 25; 3 n number; 4 begin 5 open c; 6 loop 7 fetch c into n; 8 exit when c%notfound; 9 end loop; 10 end; 11 / F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 F fired! F fired! F fired! F fired! F fired! -- 15 F fired! F fired! F fired! F fired! F fired! -- 20 F fired! F fired! -- 22 PL/SQL procedure successfully completed. -- with arraysize = 3 SQL> set arraysi 3 SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25; A ---------- 5 6 7 8 9 10 6 rows selected. F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 F fired! F fired! F fired! F fired! -- 14 Elapsed: 00:00:00.45 SQL> set arraysi 2 SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25; A ---------- 5 6 7 8 9 10 6 rows selected. F fired! F fired! F fired! F fired! F fired! -- 5 F fired! F fired! F fired! F fired! F fired! -- 10 F fired! F fired! F fired! F fired! F fired! F fired! -- 16 Elapsed: 00:00:00.72 [collapse]

PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist