CBO wrong cardinality in very simple SQL
637538Jul 30 2008 — edited Aug 3 2008Hi All,
I'm working on datawarehouse environment.
we have a lot of queries that get the period time from another table.
for example
select *
from fact_table
where ...
and date between (select add_month(date_column,-2) from period_table)
and (select date_column from period_table);
or
select *
from fact_table , period_table
where ...
and date between add_month(date_column,-2) and date_column
so this needs to give me 3 month period.
the query needs to retrieve about 50M rows.
the CBO give me wrong cardinality about how many rows it will retrieve from the fact table.
it give me about 2M in the join and 220K in the sub select after histograms and fresh statistics.
this cause a bad explain plan sometimes.
i think it's something natural in datawarehouse env's.
how can i help the optimizer here ?
(i don't want to use the cardinality hint here)
10x
Zvika