Hi all,

I need help tunning the following query


Table scan on table Fact.sale_Current_2005
1.select key,sale_amt,dt,c4,c5,c6,c7 from Fact.sale_Current_view
where dt>= (select (min(Dt)) from Stage.sale_Current)


Ix used
2. select key,sale_amt,dt,c4,c5,c6,c7 from Fact.sale_Current_view
where dt>= '2006-07-23'


Fact.sale_Current_view is a union all view on about 6 fact tables (
select * from Fact.sale_Current_1998 union all select * from
Fact.sale_Current_2001............... 2005)


The query select (min(Dt)) from Stage.sale_Current results in
'2006-07-23'


The above query goes for a tablescan on table Fact.sale_Current_2005
from which it is selecting most of the data, it has an MDC on the Dt
column , statics on all tables are current. Data types of both dt's is
date.
The optimizer uses the index if a value is hardcoded? Can the query #1
be rewritten to make use of the ix?


Thanks,
Raj

Raj schrieb:

Hi all,
>
I need help tunning the following query
>
>
Table scan on table Fact.sale_Current_2005
1.select key,sale_amt,dt,c4,c5,c6,c7 from Fact.sale_Current_view
where dt>= (select (min(Dt)) from Stage.sale_Current)
>
>
Ix used
2. select key,sale_amt,dt,c4,c5,c6,c7 from Fact.sale_Current_view
where dt>= '2006-07-23'
>
>
Fact.sale_Current_view is a union all view on about 6 fact tables (
select * from Fact.sale_Current_1998 union all select * from
Fact.sale_Current_2001............... 2005)
>
>
The query select (min(Dt)) from Stage.sale_Current results in
'2006-07-23'
>
>
The above query goes for a tablescan on table Fact.sale_Current_2005
from which it is selecting most of the data, it has an MDC on the Dt
column , statics on all tables are current. Data types of both dt's is
date.
The optimizer uses the index if a value is hardcoded? Can the query #1
be rewritten to make use of the ix?
>
>
Thanks,
Raj

If the value is hardcoded the optimizer knows ( especially where
distribution stats are available ) if its worth going to the index or
not. If the value is buried in functions the optimizer makes guesses
which aren't always good. Look at the plan, especially the filter
factor on the fact table to see the percentage of rows the optimizer
thinks qualify. I'm not sure I understand what you mean by "most of the
data" - are there other tables involved?

Thanks for the reply...
Fact.sale_Current_view is a view on
Fact.sale_Current_view_2001
union all
Fact.sale_Current_view_2002
,...2003,
....2004
,...2005,

>From the plan, the filter factor for the predicate is (hardcoded):

0.0625

The query selects 2 months data from the view which has 5 years of
data..

I have tested the query by replacing the view with sale_current_2005
and it uses the index. With the view it it trying to use the predicate
after union all and is going for a
tablescan

The filter factor for the predicate (join after union all, tablescan):
0.67

Thanks,
Raj

If the value is hardcoded the optimizer knows ( especially where
distribution stats are available ) if its worth going to the index or
not. If the value is buried in functions the optimizer makes guesses
which aren't always good. Look at the plan, especially the filter
factor on the fact table to see the percentage of rows the optimizer
thinks qualify. I'm not sure I understand what you mean by "most of the
data" - are there other tables involved?

Raj schrieb:

Thanks for the reply...
Fact.sale_Current_view is a view on
Fact.sale_Current_view_2001
union all
Fact.sale_Current_view_2002
,...2003,
...2004
,...2005,
>

0.0625
>
The query selects 2 months data from the view which has 5 years of
data..
>
I have tested the query by replacing the view with sale_current_2005
and it uses the index. With the view it it trying to use the predicate
after union all and is going for a
tablescan
>
The filter factor for the predicate (join after union all, tablescan):
0.67
>
Thanks,
Raj
>

If I understand correctly, the UAV hardcoded is ok, the Table only with
min() is ok, but the UAV with min() fails ( you have got distribution
statistics on all tables, haven't you? ). I don't know enough to say
why the estimates with UAV are so wrong, maybe you could post the
plans?

ibadba@hotmail.com wrote:

Raj schrieb:
>

How about define Fact.sale_Current_view as following?

CREATE VIEW Fact.sale_Current_view as
select * from Fact.sale_Current_1998 WHERE dt BETWEEN '1998-01-01' AND
'1998-12-31'
union all
select * from Fact.sale_Current_1999 WHERE dt BETWEEN '1999-01-01' AND
'1999-12-31'
...............
union all
select * from Fact.sale_Current_2005 WHERE dt BETWEEN '2005-01-01' AND
'2005-12-31'