SELECT a.id, b.description, some_funky_package_call(a.id, b.description) FROM tableA a, tableB b WHERE a.id = b.id AND some_funky_package_call(a.id, b.description) <> 0After I played around with it a bit, I found that the slowest part was using the function call in the WHERE clause. If the query included the package call in the WHERE clause, the query finished in over an hour. If the package call was not in the WHERE clause, the query finished in 5 minutes (but did not return the correct results).
No problem, we'll just use an inline view and filter later. So I came up with:
SELECT id, description, bal
FROM (
SELECT
a.id,
b.description,
some_funky_package_call(a.id, b.description) bal
FROM
tableA a,
tableB b
WHERE a.id = b.id
)
WHERE bal <> 0
Confident in my fondness for inline views, I ran the query fully expecting to get the results back in a few minutes. Except the query went on, and on, and on for a full 15 minutes before I killed it. I traced the session and found out the execution profile was not different than my original query.
Eventually, I came up with a solution using HAVING. My data is such that tableA.id is guaranteed to be unique, so I knew a.id, b.description would also be unique. I changed my query to:
SELECT id, description, sum(bal)
FROM (
SELECT
a.id,
b.description,
some_funky_package_call(a.id, b.description) bal
FROM
tableA a,
tableB b
WHERE a.id = b.id
)
GROUP BY id, description
HAVING sum(bal) <> 0
Since I know that only one row will be returned per a.id, I also knew that SUM() would be summing one row. The GROUP BY essentially was doing nothing, but the HAVING clause filtered after Oracle already figured out the result set.
Edit: See "Interesting Optimizer Result, Part II" for the resolution.
5 comments:
Have you check any oddity for PLSQL functions and their costing
in 2008 UKOUG I joined a presentation of Joze Senegacnik and he gave some information about the oddities
can be find something similar from Adrian Billington here
http://www.oracle-developer.net/display.php?id=426
I optimized many SQL with this form. The best way to do it, from a performance perspective, is the remove the call all together and do it in SQL only. But for this solution to work you need a fairly simple PL/SQL procedure.
"The GROUP BY essentially was doing nothing.... "
No offence but I hate that approach. Firstly the SQL becomes obscure/misleading and there's a tendency for someone to come along six months later and 'fix' it.
Secondly, there's the tendency for the optimizer to get smarter and say "Hey, I don't need to do this step".
That doesn't mean that I have a universally applicable alternative though. Sometimes you can pull out some of the PL/SQL into SQL. For example if the function is some isnumber style validation, you can augment
WHERE isnumber(a) = 'Y'
with
WHERE regexp_instr(a,'[a-zA-Z]') = 0 and isnumber(a) = 'Y'
So a bunch of easy failures are kicked out before the function is invoked.
I would try either:
Adding rownum to the inner query to force it to evaluate the inline view before the filter:
SELECT id, description, bal
FROM (
SELECT
a.id,
b.description,
some_funky_package_call(a.id, b.description) bal,
rownum rn
FROM
tableA a,
tableB b
WHERE a.id = b.id
)
WHERE bal <> 0
or using the MATERIALIZE hint with the WITH clause:
WITH a
as
(
SELECT /*+ materialize */
a.id,
b.description,
some_funky_package_call(a.id, b.description) bal
FROM
tableA a,
tableB b
WHERE a.id = b.id
)
select *
from a
where bal <> 0
Having a look at both predicates again, I'd guess that, because a.id is the same is b.id, the optimizer is re-writing some_funky_package_call(a.id, b.description) to
some_funky_package_call(b.id, b.description)
Then it is applying that to every row in 'b' before joining the results of that to 'a'. That would be valid if table 'b' was small, the function was low cost and the selectivity high.
Interested to hear more details
Post a Comment