Data Partitioning is a way to scale your database structure to improve performance and manageability. \u00c2\u00a0Data Partitioning is a method of \u00c2\u00a0logically 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.
\nIBM has been pioneering data partitioning for years. \u00c2\u00a0DB2 on mainframe has been enjoying partitioning for many years and it rocks ! \u00c2\u00a0 DB2 UDB started with DPF \u00c2\u00a0(Distributed Partition Facility) on its DB2 EEE days. \u00c2\u00a0The 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.<\/p>\n
I had written an article <\/a>in 2009 that explained a bit about table partitoning, so I am not going to repeat it.\u00c2\u00a0 Today we will look into how\u00c2\u00a0table partitioning<\/a> and multi-dimensional cluster<\/a> can improve your performance. \u00c2\u00a0I did a three part exercise to compare the outcome. \u00c2\u00a0I am going to show you three different scenarios that illustrates the benefits of\u00c2\u00a0table partitioning and multi-dimensional custer.<\/p>\n 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. Scenario 1:<\/strong> The table has an index of YEAR \u00c2\u00a0and STATE .<\/p>\n Result : \u00c2\u00a0 When I ran the query, I got the following results : Result :<\/p>\n <\/span><\/div>\n \n From this you can clearly see how efficient the combination of partitioned and MDC is. \u00c2\u00a0Db2 is able to do partition elimination and access the data by block index. Data Partitioning is a way to scale your database structure to improve performance and manageability. \u00c2\u00a0Data Partitioning is a method of \u00c2\u00a0logically 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358],"tags":[439,430,943,433,436,434,435,437,98,438,432,431],"class_list":["post-825","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-block-index","tag-database-partition","tag-db2","tag-db2-database-partition","tag-dpf","tag-mdc","tag-multi-dimensional-cluster","tag-organize-by","tag-partition","tag-partition-elimination","tag-partitioning","tag-table-partition"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/825","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=825"}],"version-history":[{"count":13,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/825\/revisions"}],"predecessor-version":[{"id":1511,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/825\/revisions\/1511"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=825"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=825"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=825"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nThe sql we are going to run all the three scenarios is :
\nselect customer_id from tableA where year =’2010′ \u00c2\u00a0and state = ‘TX’ and \u00c2\u00a0order_date <> ship_date ;<\/p>\n
\nLets just use a table with just 5 columns with 14 millions rows :
\nCUSTOMER_ID \u00c2\u00a0INT
\nORDER_ID \u00c2\u00a0 \u00c2\u00a0 INT
\nORDER_DATE \u00c2\u00a0DATE
\nSHIP_DATE DATE
\nYEAR INT
\nSTATE \u00c2\u00a0 CHAR(2)<\/p>\n
\nEstimated Cost = 90865.046875<\/span><\/p>\n
\nScenario 2 :<\/strong>
\nLets use the same table with YEAR column partitioned by each year on the data, in my case it was 5 partition.
\n<\/span><\/div>\n
\n<\/span><\/div>\n
\nScenario 3 :<\/strong>
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.<\/p>\n
\nInorder to partition and create MDC, you need to know your data and how the applications access the data. \u00c2\u00a0One added benefit of MDC is that DB2 will also keep the table organized meaning, you will never have to reorg the table. \u00c2\u00a0Table Partitioning and MDC \u00c2\u00a0are only available in Enterprise Edition and up<\/a> .<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"