A | B | C | D | E | F | G | H | I | J | K | L | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | CP - Column Partition | |||||||||||
2 | RP - Normal Row partitioning | Table Structure | ||||||||||
3 | CP1 | CP2 | CP3 | CP4 | CP5 | CREATE TABLE sales_2 | ||||||
4 | sales_date | store_id | product_id | total_revenue | total_sold | top_salesperson | note | ( | ||||
5 | RP1 | 1/1/12 | 2660 | 55754789 | 200000 | 150000 | p1 | hi | store_id INTEGER NOT NULL, | |||
6 | 1/1/12 | 2660 | 54886995 | 2300000 | 2290000 | p2 | hello | product_id INTEGER NOT NULL, | ||||
7 | 1/1/12 | 2661 | 34427775 | 1200 | 1100 | p3 | hi | sales_date DATE FORMAT 'yyyy-mm-dd' NOT NULL, | ||||
8 | 1/1/12 | 2661 | 54886995 | 1000000 | 1000200 | p4 | none | total_revenue DECIMAL(13,2), | ||||
9 | 1/1/12 | 3521 | 55754789 | 287566 | 287500 | p5 | na | ( | ||||
10 | RP2 | 1/2/12 | 2660 | 55754789 | 200000 | 150000 | p1 | hi | total_sold INTEGER, | |||
11 | 1/2/12 | 2660 | 54886995 | 2300000 | 2290000 | p2 | hello | top_salesperson INTEGER, | ||||
12 | 1/2/12 | 2661 | 34427775 | 1200 | 1100 | p3 | hi | note VARCHAR(256) | ||||
13 | 1/2/12 | 2661 | 54886995 | 1000000 | 1000200 | p4 | none | ) | ||||
14 | 1/2/12 | 3521 | 55754789 | 287566 | 287500 | p5 | na | ) | ||||
15 | NO PRIMARY INDEX, -- This clause is optional. | |||||||||||
16 | Q1 | PARTITION BY (COLUMN, -- Defines 4 single-column partitions and | ||||||||||
17 | SELECT sales_date, sum(total_revenue) from sales_2 where sales_date = date '2012-01-01' group by 1,2; | -- 1 multicolumn partition plus 2 | ||||||||||
18 | --- Touches only CP1, CP4 for only RP1 | -- additional column partitions for internal | ||||||||||
19 | -- use. | |||||||||||
20 | Q2 | RANGE_N(salesdate BETWEEN DATE '2007-01-01' | ||||||||||
21 | SELECT sales_date, store_id, sum(total_revenue) from sales_2 where sales_date = date '2012-01-01' group by 1,2; | AND DATE '2013-12-31' | ||||||||||
22 | --- Touches CP1, CP2, CP4 for only RP1 | EACH INTERVAL '1' MONTH) | ||||||||||
23 | ADD 36); | |||||||||||
24 | Q3 | |||||||||||
25 | SELECT store_id, sum(total_revenue) from sales_2 group by 1; | |||||||||||
26 | -- Touches CP2, CP4 for both RP1, RP2 | |||||||||||
27 | ||||||||||||
28 | Q4 | |||||||||||
29 | SELECT sales_date, note, sum(total_revenue) from sales_2 where store_id = 2660 group by 1,2; | |||||||||||
30 | -- Touches CP1, CP2 (for store_id where condition), full CP5, CP4 for both RP1, RP2 |