By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,835 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

question: db2 LUW V8 UNION ALL with table function month() have bad query performance

P: n/a
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance

Env:
db2 LUW V8 + FP14

Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big
table,
we try separate this big table into twelve tables and create a view
to join these 12 tables with UNION ALL .
after query the view, we get bad query performance than query single
big table.

follow our scenario :

create table sales_01(
sales_date date not null,
prod_id integer,
city_id integer,
channel_id integer,
revenue decimal(20,2) ) ;

create table sales_02 like sales_01 ;
create table sales_03 like sales_01 ;
create table sales like sales_01 ;
create table sales_05 like sales_01 ;
create table sales_06 like sales_01 ;
create table sales_07 like sales_01 ;
create table sales_08 like sales_01 ;
create table sales_09 like sales_01 ;
create table sales_10 like sales_01 ;
create table sales_11 like sales_01 ;
create table sales_12 like sales_01 ;

alter table sales_01 add constraint date_01
check(month(sales_date)=1);
alter table sales_02 add constraint date_02
check(month(sales_date)=2);
alter table sales_03 add constraint date_03
check(month(sales_date)=3);
alter table sales_04 add constraint date_04
check(month(sales_date)=4);
alter table sales_05 add constraint date_05
check(month(sales_date)=5);
alter table sales_06 add constraint date_06
check(month(sales_date)=6);
alter table sales_07 add constraint date_07
check(month(sales_date)=7);
alter table sales_08 add constraint date_08
check(month(sales_date)=8);
alter table sales_09 add constraint date_09
check(month(sales_date)=9);
alter table sales_10 add constraint date_10
check(month(sales_date)=10);
alter table sales_11 add constraint date_11
check(month(sales_date)=11);
alter table sales_12 add constraint date_12
check(month(sales_date)=12);

create view all_sales as
(
select * from sales_01
union all
select * from sales_02
union all
select * from sales
union all
select * from sales_05
union all
select * from sales_06
union all
select * from sales_07
union all
select * from sales_08
union all
select * from sales_09
union all
select * from sales_10
union all
select * from sales_11
union all
select * from sales_12
) ;
test select sql
select * from all_sales_04 where sales_date '2007-10-15' ;

explain command:
db2expln -d sample -f sel.sql -t -g -z ;

explain output :

SQL Statement:

select *
from all_sales_04
where sales_date '2007-10-15'
Section Code Page = 850

Estimated Cost = 142.257706
Estimated Cardinality = 267.666626

(
| Access Table Name = ALANCHEN.SALES_04_11 ID = 2,14
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_10 ID = 2,13
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_09 ID = 2,12
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_08 ID = 2,11
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_07 ID = 2,10
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_06 ID = 2,9
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_05 ID = 2,8
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_04 ID = 2,7
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_02 ID = 2,5
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_01 ID = 2,4
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
UNION
| Access Table Name = ALANCHEN.SALES_04_12 ID = 2,15
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
)
Return Data to Application
| #Columns = 5

End of section
our currently solution is re-create view similar follow :
create view all_sales_05 as
(
select * from sales_01
where sales_date between '2007-01-01' and '2007-01-31' or
sales_date between '2006-01-01' and '2006-01-31' or
sales_date between '2005-01-01' and '2005-01-31'
union all
select * from sales_02
where sales_date between '2007-02-01' and '2007-02-28'
union all
select * from sales_03
where sales_date between '2007-03-01' and '2007-03-31'
union all
select * from sales_04
where sales_date between '2007-04-01' and '2007-04-30'
union all
select * from sales_05
where sales_date between '2007-05-01' and '2007-05-31'
union all
select * from sales_06
where sales_date between '2007-06-01' and '2007-06-30'
union all
select * from sales_07
where sales_date between '2007-07-01' and '2007-07-31'
union all
select * from sales_08
where sales_date between '2007-08-01' and '2007-08-31'
union all
select * from sales_09
where sales_date between '2007-09-01' and '2007-09-30'
union all
select * from sales_10
where sales_date between '2007-10-01' and '2007-10-31'
union all
select * from sales_11
where sales_date between '2007-11-01' and '2007-11-30'
union all
select * from sales_12
where sales_date between '2007-12-01' and '2007-12-31'
) ;

new exlpain output:
select *
from all_sales_05
where sales_date '2007-10-15'
Section Code Page = 850

Estimated Cost = 38.844070
Estimated Cardinality = 21.900002

(
| Access Table Name = ALANCHEN.SALES_04_11 ID = 2,14
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 3
UNION
| Access Table Name = ALANCHEN.SALES_04_10 ID = 2,13
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 3
UNION
| Access Table Name = ALANCHEN.SALES_04_12 ID = 2,15
| | #Columns = 5
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 3
)
Return Data to Application
| #Columns = 5

End of section
but our data is continue growing and this view is need to maintain
every year for DBA,
did anyone know other simple solution can maintain this kind data ?
Thanks

May 29 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
You need to change your check constraints:
CHECK (salesdate BETWEEN '2007-10-01' AND '2007-10-31')
etc...
By doing round robin there is no way a query with >= or <= can prune any
branches because there is a new July next year too.

Uisng the changed check constraints you can also roll out data much nicer:
1. Empty the partition
2. alter table drop old 2007 constraint add 2008 constraint.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 29 '07 #2

P: n/a
On 5月30日, 上午3時55分, Serge Rielau <srie...@ca.ibm..comwrote:
You need to change your check constraints:
CHECK (salesdate BETWEEN '2007-10-01' AND '2007-10-31')
etc...
By doing round robin there is no way a query with >= or <= can prune any
branches because there is a new July next year too.

Uisng the changed check constraints you can also roll out data much nicer:
1. Empty the partition
2. alter table drop old 2007 constraint add 2008 constraint.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi Serge ,

Thanks your advice.

May 30 '07 #3

P: n/a
Serge Rielau wrote:
You need to change your check constraints:
CHECK (salesdate BETWEEN '2007-10-01' AND '2007-10-31')
etc...
By doing round robin there is no way a query with >= or <= can prune any
branches because there is a new July next year too.
Just curious, would adding a generated column as month(salesdate), and
an index on that column help?

/Lennart

[...]
May 30 '07 #4

P: n/a
Lennart wrote:
Serge Rielau wrote:
>You need to change your check constraints:
CHECK (salesdate BETWEEN '2007-10-01' AND '2007-10-31')
etc...
By doing round robin there is no way a query with >= or <= can prune
any branches because there is a new July next year too.

Just curious, would adding a generated column as month(salesdate), and
an index on that column help?
No. No pruning is no pruning. It's a semantic problem.
If the queries were of the form WHERE MONTH(DATE) = 5 it would help.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 30 '07 #5

P: n/a
Serge Rielau wrote:
Lennart wrote:
>Serge Rielau wrote:
>>You need to change your check constraints:
CHECK (salesdate BETWEEN '2007-10-01' AND '2007-10-31')
etc...
By doing round robin there is no way a query with >= or <= can prune
any branches because there is a new July next year too.

Just curious, would adding a generated column as month(salesdate), and
an index on that column help?
No. No pruning is no pruning. It's a semantic problem.
If the queries were of the form WHERE MONTH(DATE) = 5 it would help.
Thanks, after rereading wugons questions I realize what a stupid
question I asked. In my mind he where asking a question like the one you
suggest at the end, in reality something completely different.
/Lennart
May 31 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.