Create Projection Issues .

Naveen_Kumar

Query is taking too much time to update tmp_ table.

create table tmp_table as
select * from flex_table;

lter table tmp_table add column purchase_flag integer;

explain update tmp_table set
purchase_flag = case when ("e.f.p.ti" like '%Order%Confirmation%') then 1 else 0 end;
ccess Path:
 +-DML UPDATE [Cost: 0, Rows: 0]
 |  Target Projection: public.tmp_table_b1 (NO DELETE)
 |  Target Projection: public.tmp_table_b0 (NO DELETE)
 |  Target Prep:
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for <No Alias> [Cost: 31M, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 | |      Projection: public.tmp_table_b1
 | |      Materialize: tmp_table.acid, tmp_table."e.ec",................
 | |      Execute on: All Nodes
 +-Target Projection: public.tmp_table_b1 (DELETE ON CONTAINER)
 |  Target Prep:
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for tmp_table [Cost: 1K, Rows: 4M (NO STATISTICS)] (PATH ID: 1)
 | |      Projection: public.tmp_table_b1
 | |      Materialize: tmp_table.acid
 | |      Execute on: All Nodes
 +-Target Projection: public.tmp_table_b0 (DELETE ON CONTAINER)
 |  Target Prep:
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for tmp_table [Cost: 1K, Rows: 4M (NO STATISTICS)] (PATH ID: 1)
 | |      Projection: public.tmp_table_b0
 | |      Materialize: tmp_table.acid
 | |      Execute on: All Nodes
then I created two projections

CREATE PROJECTION tmp_table_proj0("e.f.p.ti"."prchase_flag") as select "e.f.p.ti"."prchase_flag" from tmp_table segmented by hash(""e.f.p.ti"."prchase_flag") all nodes offset 0;
 CREATE PROJECTION tmp_table_proj1("e.f.p.ti"."prchase_flag") as select "e.f.p.ti"."prchase_flag" from tmp_table segmented by hash(""e.f.p.ti"."prchase_flag") all nodes offset 1;


explain update tmp_table set
 purchase_flag = case when ("e.f.p.ti" like '%Order%Confirmation%') then 1 else 0 end;

 Access Path:
 +-DML UPDATE [Cost: 0, Rows: 0]
 | 
 |  Target Projection: public.tmp_table_proj1 (NO DELETE)
 |  Target Projection: public.tmp_table_proj0 (NO DELETE)
 |  Target Projection: public.tmp_table_b1 (NO DELETE)
 |  Target Projection: public.tmp_table_b0 (NO DELETE)
 |  Target Prep:
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for <No Alias> [Cost: 32M, Rows: 4M (NO STATISTICS)] (PATH ID: 1)
 | |      Projection: public.tmp_table_b1
 | |      Materialize: tmp_table.acid, tmp_table."e.ec", ............


i want to project it on only tmp_table_proj1,and tmp_table_proj0 how can i accomplish this?

it is taking approx 16 mins to update table.
tmp_table_b1 and tmp_table_b0 both are default projections.