2010-04-21

Reducing the number of function calls

Using a slow function call in your query? Maybe you are calling it unnecessarily.



SQL> create or replace type ns_typ is table of number;
2 /

Type created.

SQL> create or replace function rn(n number) return ns_typ is
2 ret ns_typ;
3 begin
4 dbms_lock.sleep(1);
5 select level bulk collect into ret from dual connect by level <= n;
6 return ret;
7 end;
8 /

Function created.

SQL>
SQL> create table ta as select level n, mod(level,3)+1 m from dual connect by level <= 10;

Table created.

SQL> select * from ta;

N M
---------- ----------
1 2
2 3
3 1
4 2
5 3
6 1
7 2
8 3
9 1
10 2

10 rows selected.

SQL> set timi on

SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

N M
---------- ----------
1 2
2 3

Elapsed: 00:00:10.01


The query is calling rn function for each ten rows of ta table. Each call takes one second as the function is using dbms_lock. There are only three distinct values that the function is needed to be called.



SQL> with aa as (
2 select *
3 from ta a
4 ), bb as (
5 select distinct m
6 from aa
7 ), cc as (
8 select /*+materialize*/ b.m, dd.column_value n
9 from bb b, table(rn(b.m)) dd)
10 select *
11 from aa
12 where (n,m) in (select n,m from cc)
13 ;

N M
---------- ----------
1 2
2 3

Elapsed: 00:00:03.03


Alternatively you might consider using result cache for the function.


SQL> create or replace function rn(n number) return ns_typ result_cache is
2 ret ns_typ;
3 begin
4 dbms_lock.sleep(1);
5 select level bulk collect into ret from dual connect by level <= n;
6 return ret;
7 end;
8 /

Function created.

Elapsed: 00:00:00.04
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

N M
---------- ----------
1 2
2 3

Elapsed: 00:00:03.01
SQL>
SQL> select * from ta a where a.n in (select * from table(rn(a.m)));

N M
---------- ----------
1 2
2 3

Elapsed: 00:00:00.00


Cleanup

SQL> drop table ta purge;
SQL> drop function rn;
SQL> drop type ns_typ;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.