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
[sourcecode language=”sql”]
— 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
[/sourcecode]
PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist