Using of row_number for STAT collecting

Database
Enthusiast

Using of row_number for STAT collecting

Hi, everybody!

Can you please help me to investigate the following question...

There are one fact table t1 and several dimension tables p1, p2, p3. Fact table has a field "date1" (contains data "on date"), dimension tables have actual periods like start_date ... end_date

I need to make a simple query joining fact table (for a period) with all dimension ones taking into consideration periods of dates, smth like:

select ...

from t1

inner join p1 on ...

inner join p2 on ...

inner join p3 on ...

where

t1.date1 between date '2011-01-01' and date '2011-03-31'

and t1.date1 between p1.start_date and p1.end_date

and t1.date1 between p2.start_date and p2.end_date

and t1.date1 between p3.start_date and p3.end_date

So, when I run such a query it takes a lot of time to execute, when I look at the explain plan I can see the optimizer uses product join to join fact and dimension tables. If I set a long period of date, like a year (such t1.date1 bewteen date '2011-01-01' and date '2011-21-31') the query fails with an error "No more spool space ... ".

The trick is that when I rewrote a query simple adding row_number to dimension tables, it worked fine!

Smth like this:

select ...

from t1

inner join (select p1.*,row_number () over (order by p1.start_date) from p1) p1 on ...

inner join (select p2.*,row_number () over (order by p2.start_date) from p2) p2 on ...

inner join (select p3.*,row_number () over (order by p3.start_date) from p3) p3 on ...

where

t1.date1 between date '2011-01-01' and date '2011-03-31'

and t1.date1 between p1.start_date and p1.end_date

and t1.date1 between p2.start_date and p2.end_date

and t1.date1 between p3.start_date and p3.end_date

And in the query plan I see merge join rather than product join. And I also see a STAT FUNCTION STEP in the begining of the plan...

Do you know what the thing is here? I have poor stat? And when adding a row_number the optimizer forces a dynamic STAT Collection?

Thank you in advance.

Andrey.