Does flex tables support projections ?

Comments

• Jim_Knicely - Select Field - Administrator

  November 2017

  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.

  0