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