表分区示意图-Greenplum 数据库基础培训V1.2

表分区示意图 * Instructor Notes Here is an example of the partitioning design we will be creating in our labs. The rank table contains data from the past 5 years. For each year you have the top 1000 ranked baby names for girls and the top 1000 ranked baby names for boys. Let’s say we have analyzed our query workload and we have determined that queries our users issue seem to select names and rank based mostly on sex and/or on year. Based on that analysis, we have determined that partitioning the rank table by sex (boy/girl) and then further by year will offer us the biggest performance gain on our query workload. For example a query that is only interested in boy names only has to scan the boy partition. And queries only interested in the year 2005 need only scan a small subset of the data in the boy or girl partitions. Also, since our data warehouse only has the past 5 years of data, when we get the data for 2006 we can easily drop the 2001 partitions and add ones for 2006 and just load that child partition directly with the 2006 data. This is much more efficient than doing a delete operation to remove 2001 data.