i am worried about the resegmentation during Group BY
say
-- to create super projection use ENCODING AUTO
Code: Select all
create table foo (a int ENCODING AUTO, b int, c int)
Code: Select all
CREATE PROJECTION foo_pj
(a,b,c) AS
select a,b,c from foo
SEGMENTED BY HASH(a,b,c)
ALL NODES OFFSET 0;
Code: Select all
CREATE PROJECTION foo_pj1
(b,c) AS
select b,c from foo
SEGMENTED BY HASH(b,c)
ALL NODES OFFSET 0;
Code: Select all
select projection_basename,segment_expression,is_super_projection from projections where anchor_table_name='foo'
foo_pj hash(foo.a, foo.b, foo.c) true
foo_pj1 hash(foo.b, foo.c, foo.a) true
Now, when i execute following
Code: Select all
explain SELECT F.b,F.a FROM foo AS F, foo WHERE foo.b = F.a GROUP BY 1,2;
+-GROUPBY PIPELINED [Cost: 4, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Group By: F.a, F.b
| +---> JOIN HASH [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 2)
but for
Code: Select all
explain SELECT F.b,F.c FROM foo AS F, foo WHERE F.b = F.c GROUP BY 1,2;\
+-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Group By: F.b, F.c
| +---> JOIN (CROSS JOIN) [Cost: 4, Rows: 1 (NO STATISTICS)] (PATH ID: 2)
| | +-- Outer -> STORAGE ACCESS for foo [Cost: 1, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
This means the second projection (foo_pj1) created has no effect on the SEGMENTATION?
Or my understanding of this is wrong please clarify.
Karthik S G
P.S posted same also in forum.vertica.com