I'm using Oracle 11g. I have a query that takes 6 seconds to execute
select *
from tab1
where exists (select 1
from tab2
where tab2.col1 = tab1.col1
and tab2.col2 = pkg1.fn1('STRING'));
The highlighted function returns a constant value = 1. (The function has 1 query which when executed standalone, returns result in < 1 second.
If I hardcode 1 instead of calling the function, query returns same results in < 1 second.
I do not want to use RESULT_CACHE in function, etc. I want to rewrite the query so that it is faster.
Approach 1: No time difference The query within the function is brought out and used as a subquery
and tab2.col2 = pkg1.fn1('STRING')
changed to
and tab2.col2 = (query within the function is written here)
Approach 2: Time increased The query within the function is brought out and used as an inner join with the overall query within the exists clause.
Approach 3: No change
with factoring as (select pkg1.fn1('STRING') as return_val from dual)
select *
from tab1
where exists (select 1
from tab2, factoring
where tab2.col1 = tab1.col1
and tab2.col2 = factoring.return_val);
Approach 4: No change
with factoring as (select pkg1.fn1('STRING') as return_val from dual)
select *
from tab1, factoring
where exists (select 1
from tab2
where tab2.col1 = tab1.col1
and tab2.col2 = factoring.return_val);
I want to understand below
1. why approach 3 and 4 didn't work
2. how to optimize this query?
