Does flex tables support projections ?

ashu123

hello,
Does flex tables has projections, if yes how to apply projections to flex tables.
Thanks in advance

Jim_Knicely

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.