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:

Post a Comment

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio