Tuesday, March 26, 2024

Setting the CARDINALITY (undocumented) hint on two tables

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:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles