ABCDEFGHIJKL
1
CP - Column Partition
2
RP - Normal Row partitioningTable Structure
3
CP1CP2CP3CP4CP5CREATE TABLE sales_2
4
sales_datestore_idproduct_idtotal_revenuetotal_soldtop_salespersonnote(
5
RP11/1/12266055754789200000150000p1histore_id INTEGER NOT NULL,
6
1/1/1226605488699523000002290000p2helloproduct_id INTEGER NOT NULL,
7
1/1/1226613442777512001100p3hisales_date DATE FORMAT 'yyyy-mm-dd' NOT NULL,
8
1/1/1226615488699510000001000200p4nonetotal_revenue DECIMAL(13,2),
9
1/1/12352155754789287566287500p5na (
10
RP21/2/12266055754789200000150000p1hi total_sold INTEGER,
11
1/2/1226605488699523000002290000p2hello top_salesperson INTEGER,
12
1/2/1226613442777512001100p3hi note VARCHAR(256)
13
1/2/1226615488699510000001000200p4none )
14
1/2/12352155754789287566287500p5na)
15
NO PRIMARY INDEX, -- This clause is optional.
16
Q1PARTITION 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
Q2RANGE_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 RP1EACH 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