473,850 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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(sal es_date)=1);
alter table sales_02 add constraint date_02
check(month(sal es_date)=2);
alter table sales_03 add constraint date_03
check(month(sal es_date)=3);
alter table sales_04 add constraint date_04
check(month(sal es_date)=4);
alter table sales_05 add constraint date_05
check(month(sal es_date)=5);
alter table sales_06 add constraint date_06
check(month(sal es_date)=6);
alter table sales_07 add constraint date_07
check(month(sal es_date)=7);
alter table sales_08 add constraint date_08
check(month(sal es_date)=8);
alter table sales_09 add constraint date_09
check(month(sal es_date)=9);
alter table sales_10 add constraint date_10
check(month(sal es_date)=10);
alter table sales_11 add constraint date_11
check(month(sal es_date)=11);
alter table sales_12 add constraint date_12
check(month(sal es_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
5 3853
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5458
by: Matias Silva | last post by:
Hi Everyone, I wrote a for loop to build several select statements that are combined with a UNION. When I execute one of the queries separately, it works, but when I execute the query with a UNION it returns: ERROR 2013 (HY000): Lost connection to MySQL server during query I am able to run additional queries after I get the error, so the connection is not lost. I have tried running the query with the union on MySQL Query Browser and...
7
2060
by: jane | last post by:
HI, I was ask to do a query to get the member active condition I had table to show the member number and active status in three month. ACC A1 A2 A3 ---- ----------- ----------- ----------- c001 1 1 1
1
2387
by: Joachim Klassen | last post by:
Hi all, I'm investigating partitioned tables using a UNION ALL VIEW and found the following (see ddl below): If I create a check constraint like "check (month(tdate) = 1)" DB2 won't do branch elimination for a query like "SELECT * FROM Q1 WHERE TDATE = '01.01.2004'". Explains shows that all 3 tables are accessed. But with a a constraint "check (tdate between '01.01.2004' and '31.01.2004')" DB2 will do branch elimination - means only...
8
5812
by: lyn.duong | last post by:
Hi, I have a large table (about 50G) which stores data for over 7 years. I decided to split this table up into a yearly basis and in order to allow minimum changes to the applications which access this table, I created a union all view over the 7 yearly tables. What I have noticed is that queries against the union all view is considerably slower than queries against the original table. When I ran db2batch, I noticed cpu usage was higher...
5
1928
by: Alicia | last post by:
Hello everyone based on the data, I created a union query which produces this. SELECT ,,, 0 As ClosedCount FROM UNION SELECT ,, 0 AS OpenedCount, FROM ORDER BY , ;
1
2016
by: PATGMorris | last post by:
I've got a Union Query that pulls data from two different tables for chemistry and micro testing. The tables containing very similar data but for reasons not necessary here, cannot be put into one table (differing types of results mainly) However, both have date entry fields for varying time stations from an initial, 2 weeks, 1 month, 2 months through to 24 months. I've written a Union Query that pulls the dates into a sort of ...
3
1560
by: dkintheuk | last post by:
Hi everyone, I have a table with a set of accounts in that contains one row for each account for each time it has been audited. (this may not include every account we deal with) Each month I want to select, from a separate and full list of accounts that i import to the database, a random selection of new accounts to be audited this month. Hence, a row for each one needs to be added to the original database and the most recent entry...
3
2227
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are 2 tables with a standard LEFT JOIN. One field of the query is calculated, looking for a NULL in one table, and then using a field from the second table in that case. One query looks like this: PARAMETERS Text ( 255 ); SELECT...
2
4954
kcdoell
by: kcdoell | last post by:
Hello: I am trying to create a union query but do not have a lot of experience. Basically I have the below tables: The Tables: Table Name = tblPrior CreditRegIDFK; Number; Foreign Key for the Credit Region ID
0
9895
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
0
9741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11011
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10666
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10351
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7899
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5735
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5929
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4546
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.