Benefits of Data Partitioning and MDC !
Data Partitioning is a way to scale your database structure to improve performance and manageability.  Data Partitioning is a method of  logically and physically segregating data within tables and indexes so that queries can access them individually or concurrently. Pretty much every commercially available RDBMS has data partitioning as one of its features and they come in different varieties too.
IBM has been pioneering data partitioning for years. Â DB2 on mainframe has been enjoying partitioning for many years and it rocks ! Â DB2 UDB started with DPF Â (Distributed Partition Facility) on its DB2 EEE days. Â The concept was to distribute the instance across multiple nodes (logical or physical) so that the database workload can take advantage of non-shared resources across the nodes.
I had written an article in 2009 that explained a bit about table partitoning, so I am not going to repeat it. Today we will look into how table partitioning and multi-dimensional cluster can improve your performance.  I did a three part exercise to compare the outcome.  I am going to show you three different scenarios that illustrates the benefits of table partitioning and multi-dimensional custer.
The first scenario is with a regular table, the second scenario is with a partitioned table and the third scenario is a partitioned table with MDC columns.
The sql we are going to run all the three scenarios is :
select customer_id from tableA where year =’2010′  and state = ‘TX’ and  order_date <> ship_date ;
Scenario 1:
Lets just use a table with just 5 columns with 14 millions rows :
CUSTOMER_ID Â INT
ORDER_ID Â Â INT
ORDER_DATE Â DATE
SHIP_DATE DATE
YEAR INT
STATE Â CHAR(2)
The table has an index of YEAR Â and STATE .
Result : Â When I ran the query, I got the following results :
Estimated Cost = 90865.046875
Scenario 2 :
Lets use the same table with YEAR column partitioned by each year on the data, in my case it was 5 partition.
Scenario 3 :
Lets use the same table with YEAR column partitioned by each year on the data, and have it organized by Year and STATE . This means the table has a MDC of YEAR and STATE and also partitioned by YEAR.
Result :
From this you can clearly see how efficient the combination of partitioned and MDC is. Â Db2 is able to do partition elimination and access the data by block index.
Inorder to partition and create MDC, you need to know your data and how the applications access the data. Â One added benefit of MDC is that DB2 will also keep the table organized meaning, you will never have to reorg the table. Â Table Partitioning and MDC Â are only available in Enterprise Edition and up .