UNION ALL View

[Sathyaram Sannasi]

I'm testing a UNION ALL View (say UA_VIEW) of 12 tables - one table for
each year - 5 million records/table on an average.

Check constraints are defined on the base table -

EG. EFF_START_DATE BETWEEN ('2004-01-01' AND '2004-12-31') AND
EFF_END_DATE BETWEEN ('2004-01-01' and '2004-12-31')

and also on the view defn,

SELECT * from table_2004 where eff_start_date between '2004-01-01' and
'2004-12-31' and eff_end_date between '2004-01-01' and '2004-12-31'.

There is another table table2 with a column calendar_date, which
contains some details for each day of the last 12 years, one
record/day.

The following queries perform well, they scan through only one table :

SELECT 1 from UA_VIEW where '2004-04-01' between eff_start_date and
eff_end_date

SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date='2004-04-01'
and t2.calendar_date between eff_start_date and eff_end_date
But, the following queries scan through all the tables :
SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date in
('2004-04-01','2004-04-02') and t2.calendar_date between eff_start_date
and eff_end_date

SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date between
'2004-04-01' and '2004-04-02' and t2.calendar_date between
eff_start_date and eff_end_date

Is it by any chance, a documented 'limitation' of the using union all
view definitions ?

Please suggest on of how I can get the last two statements to do a
scan only on the relevant table(s) ?

Applying the where condition on the UA_VIEW is the last thing I want to
try as the query is being dynamically built using a third party query
tool ...

Thanks in advance

Sathyaram

[Serge Rielau]

Sathyaram Sannasi wrote:

I'm testing a UNION ALL View (say UA_VIEW) of 12 tables - one table for
each year - 5 million records/table on an average.

Check constraints are defined on the base table -

EG. EFF_START_DATE BETWEEN ('2004-01-01' AND '2004-12-31') AND
EFF_END_DATE BETWEEN ('2004-01-01' and '2004-12-31')

and also on the view defn,

SELECT * from table_2004 where eff_start_date between '2004-01-01' and
'2004-12-31' and eff_end_date between '2004-01-01' and '2004-12-31'. There is no need to duplicate the predicates in the view definition.
If you stick to the constraints only (and forget about labeling the
tables by year), you can do efficient rollin/out just by playing with
the constraints.
There is another table table2 with a column calendar_date, which
contains some details for each day of the last 12 years, one
record/day.

The following queries perform well, they scan through only one table :

SELECT 1 from UA_VIEW where '2004-04-01' between eff_start_date and
eff_end_date

SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date='2004-04-01'
and t2.calendar_date between eff_start_date and eff_end_date
But, the following queries scan through all the tables :
SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date in
('2004-04-01','2004-04-02') and t2.calendar_date between eff_start_date
and eff_end_date

SELECT 1 from UA_VIEW t1,table2 t2 where t2.calendar_date between
'2004-04-01' and '2004-04-02' and t2.calendar_date between
eff_start_date and eff_end_date So the base tables ahve three date columns: calendar_date, eff_start and
eff_end?
Is it by any chance, a documented 'limitation' of the using union all
view definitions ? The optimizer and query rewrite can only be described so good without
being both overwelming or giving away the secret sauce....

I don't think query rewrite resolved the IN list/BETWEEN combination
into an OR/BETWEEN combination.
Do you have a constraint that specifies that eff_start <= calendar_date
<= eff_and. I doubt it will help, but it may be worth a shot.
Please suggest on of how I can get the last two statements to do a
scan only on the relevant table(s) ?

Applying the where condition on the UA_VIEW is the last thing I want to
try as the query is being dynamically built using a third party query
tool ...

You need to get the constants "closer" to the epartitioning constraints..
What about partitioning on the calendar_date instead?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[AK]

>EG. EFF_START_DATE BETWEEN ('2004-01-01' AND '2004-12-31') AND

EFF_END_DATE BETWEEN ('2004-01-01' and '2004-12-31')

so no row can have EFF_START_DATE 5 minutes before New Year and
EFF_END_DATE 10 minutes after New year, correct?