Are searches partition aware in db2 11

Anders Domeij

Are searches partition aware in db2 11

First I apologize if I'm asking a question t which there is an obvous answer (that I haven't found or understood)

 

If I partition a table in my database by, for example, month on a (indexed) column like Sale_Date and keep data in the table for 4 years, does a 'select * from table where Sale_Date Between xxxx-Jan and xxxx-Feb' automagically figure out that the search need only be done in 2 partitions or does it use the full index (48 partitions for 4 yrs)?

i.e Will I automagically get faster searches on a partitioned table provided I use the correct (partition) column in my search?

Thanks in advance

Anders 

Phil Grainger

Are searches partition aware in db2 11
(in response to Anders Domeij)
Hi Anders

Quick answer is “yes” to all parts of your question

But beware of the difference between dynamic SQL (where Db2 KNOWS the value of your betweens) and static SQL with host variables (where Db2 doesn’t know, so may well end up looking in every partition)

Also, this is a question better asked of the main db2-l list (you asked on the idugemea list)

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Anders Domeij [mailto:[login to unmask email]
Sent: 21 May 2018 12:13
To: [login to unmask email]
Subject: [IDUGEMEA] - Are searches partition aware in db2 11


First I apologize if I'm asking a question t which there is an obvous answer (that I haven't found or understood)



If I partition a table in my database by, for example, month on a (indexed) column like Sale_Date and keep data in the table for 4 years, does a 'select * from table where Sale_Date Between xxxx-Jan and xxxx-Feb' automagically figure out that the search need only be done in 2 partitions or does it use the full index (48 partitions for 4 yrs)?

i.e Will I automagically get faster searches on a partitioned table provided I use the correct (partition) column in my search?

Thanks in advance

Anders

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)

Anders Domeij

RE: Are searches partition aware in db2 11
(in response to Phil Grainger)

Phil --

Thanks for the quick reply.

JDBC is always dynamic SQL -- correct?

Rgds

Anders