Deferred predicate evaluation

This is a case i was working on a few days ago.I was asked to tune a specific query on our production server, so here is a quick test case to model the problem (i only kept the interesting part of the original query which was much more longer)

Test case : (ORACLE 12.1.0.2.6)

DROP TABLE test_a1;
DROP TABLE test_a2;

CREATE TABLE test_a1
AS
SELECT level a,
level AS b
FROM DUAL where level > 3000
CONNECT BY level <6000;

CREATE TABLE test_a2
AS
SELECT  level AS a,
level AS b
FROM DUAL where LEVEL > 100
CONNECT BY  level < 3100;

exec dbms_stats.gather_table_stats(null,'TEST_A1');
exec dbms_stats.gather_table_stats(null,'TEST_A2');

create or replace  function cpu_intensive_function(a number) return number  deterministic   as
n NUMBER := 0;
BEGIN
dbms_application_info.set_client_info(userenv('client_info')+1 );
FOR f IN 1..100000
LOOP
n := MOD (n,999999) + SQRT (f);
END LOOP;
return n+a;
END;

&nbsp;

Query :

SELECT t1.a a,t1.b b,t2.a a2,t2.b b2
FROM test_a1 t1, test_a2 t2
WHERE     t1.a = t2.a  and cpu_intensive_function(t1.b) = 1;

At first this may seem a good execution plan.We have two tables with 2999 rows each,we use the filter function “cpu_intensive_function”on column “b” to minimize throughway and eliminate the maximum number of rows as soon as possible.

But at runtime the execution of the  deterministic function “cpu_intensive_function” 2999 times (for each row of table TEST_A1 as it was a unique value so the caching is not effective) ,which is cpu intensive , took a significant amount of time.

Although we are eliminating rows as soon as possible,this step is very costly so why not try to differ it knowing that the join between this two tables on column “a” will return only 99 rows (In this case this function will be executed  only 99 times minimizing significantly the execution time).

The question is how to do it ?

To be able to differ the execution of the function i have rewritten the query this way :

SELECT h.a,h.b,h.a2,h.b2
FROM (SELECT /*+ NO_MERGE */
t1.a a,t1.b b,t2.a a2,t2.b b2, ROWNUM
FROM test_a1 t1, test_a2 t2
WHERE t1.a = t2.a) h
WHERE  cpu_intensive_function (h.b)  = 1;

I added the ROWNUM pseudo-column to prevent  simple filter push-down from happening as it’s a no brainer transformation.(It’s also possible to use  /*+ opt_param(‘_optimizer_filter_pushdown’,’false’) */ but i like the first alternative.For more info)

This query was mush faster, the execution time dropped from 5Min to 10Sec !

That’s it 😀