Does flex tables support projections ?
hello,
Does flex tables has projections, if yes how to apply projections to flex tables.
Thanks in advance
0
hello,
Does flex tables has projections, if yes how to apply projections to flex tables.
Thanks in advance
Comments
Yes, FLEX tables support projections. But unless you materialize columns, I do not think they are of much use (other than the default SUPER projection).
Example:
dbadmin=> create flex table proj_test(); CREATE TABLE dbadmin=> insert into proj_test select 1 c1, 2 c2; OUTPUT -------- 1 (1 row) dbadmin=> select c1, c2 from proj_test; c1 | c2 ----+---- 1 | 2 (1 row) dbadmin=> create projection proj_test_pr as select c1, c2 from proj_test order by c2 segmented by hash(c1) all nodes; WARNING 5727: Sort clause contains a LongVarbinary attribute, proj_test.__raw__ - data loads may be slowed significantly HINT: Consider using a non-long type column for sorting or adding at least one such column to the table WARNING 5724: Segmentation clause contains a LongVarbinary attribute, proj_test.__raw__ - data loads may be slowed significantly HINT: Consider using a non-long type column for segmentation or adding at least one such column to the table WARNING 4468: Projection <public.proj_test_pr> is not available for query processing. Execute the select start_refresh() function to copy data into this projection. The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh CREATE PROJECTION dbadmin=> select start_refresh(); start_refresh ---------------------------------------- Starting refresh background process. (1 row) dbadmin=> select * from proj_test_pr; c1 | c2 ----+---- 1 | 2 (1 row) dbadmin=> select projection_name, segment_expression from projections where anchor_table_name = 'proj_test'; projection_name | segment_expression -----------------+------------------------------------------------- proj_test_super | hash(proj_test.__identity__) proj_test_pr | hash(public.MapLookup(proj_test.__raw__, 'c1')) (2 rows) dbadmin=> explain select c1, c2 from proj_test order by c2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select c1, c2 from proj_test order by c2; Access Path: +-SORT [Cost: 18, Rows: 1 (NO STATISTICS)] (PATH ID: 1) | Order: public.MapLookup(proj_test.__raw__, 'c2') ASC | +---> STORAGE ACCESS for proj_test [Cost: 11, Rows: 1 (NO STATISTICS)] (PATH ID: 2) | | Projection: public.proj_test_super | | Materialize: proj_test.__raw__ ------------------------------Note above that the default SUPER projection is used for the query (even though I order by C2).
Next I will materialize the columns C1 and C2 and create another projection:
dbadmin=> alter table proj_test add column c1_materialized int default c1::int; ALTER TABLE dbadmin=> alter table proj_test add column c2_materialized int default c2::int; ALTER TABLE dbadmin=> create projection proj_test_pr2 as select c1_materialized, c2_materialized from proj_test order by c2_materialized segmented by hash(c1_materialized) all nodes; WARNING 4468: Projection <public.proj_test_pr2> is not available for query processing. Execute the select start_refresh() function to copy data into this projection. The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh CREATE PROJECTION dbadmin=> select start_refresh(); start_refresh ---------------------------------------- Starting refresh background process. (1 row) dbadmin=> select projection_name, segment_expression from projections where anchor_table_name = 'proj_test'; projection_name | segment_expression -----------------+------------------------------------------------- proj_test_super | hash(proj_test.__identity__) proj_test_pr | hash(public.MapLookup(proj_test.__raw__, 'c1')) proj_test_pr2 | hash(proj_test.c1_materialized) (3 rows) dbadmin=> explain select c1_materialized, c2_materialized from proj_test order by c2_materialized; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select c1_materialized, c2_materialized from proj_test order by c2_materialized; Access Path: +-STORAGE ACCESS for proj_test [Cost: 10, Rows: 1 (NO STATISTICS)] (PATH ID: 2) | Projection: public.proj_test_pr2 | Materialize: proj_test.c2_materialized, proj_test.c1_materialized ------------------------------Now, the expected projection is being used correctly.