I was trying to solve a performance problem in a query containing a left join on two global temporary tables.
Clearly if you run an Explain Plan on such tables, the cardinality is totally wrong, but theoretically you should be able to "simulate" the right cardinality with the undocumented hint CARDINALITY.
The second problem is that apparently such hint accepts only one table or I couldn't find any example of usage when you need to set the value for two tables.
So I came up with the following workaround using the WITH clause.
If the resulting plan is correct, then it's easily understood why a query with a cost of 7513724 takes ages to return the results.
WITH
zoo_01 as (
select /*+ CARDINALITY(gtt_rpt_zoo_01 38974) */ * from reports.gtt_rpt_zoo_01),
zoo_03 as (
select /*+ CARDINALITY(gtt_rpt_zoo_03 38967) */ * from reports.gtt_rpt_zoo_03)
SELECT ...
FROM zoo_01, zoo_03
WHERE ...
If there is an alternate method, I'd be glad to know...
No comments:
Post a Comment