Materializing flex tables

After creating flex tables, you can change the table structure to promote virtual columns to materialized (real) columns. If your table is already a hybrid table, you can change existing real columns and promote other important virtual columns. This section describes some key aspects of promoting columns, adding columns, specifying constraints, and declaring default values. It also presents some differences when loading flex or hybrid tables, compared with columnar tables.

Materializing virtual columns by promoting them to real columns can significantly improve query performance. Vertica recommends that you materialize important virtual columns before running large and complex queries. Promoted columns cause a small decrease in load performance.

Adding columns

You can add a real column to a flex table using ALTER TABLE ADD COLUMN. By default, data already in the table remains in the __raw__ column and only new data is added to the real column:

=> CREATE FLEX TABLE mtns();

=> COPY mtns FROM STDIN PARSER FJSONPARSER();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name": "Everest"}
>> {"name": "Mt St Helens"}
>> {"name": "Denali"}
>> {"name": "Kilimanjaro"}
>> {"name": "Mt Washington"}
>> \.

=> ALTER TABLE mtns ADD COLUMN name VARCHAR;

=> INSERT INTO mtns(name) VALUES('Fuji');
 OUTPUT
--------
      1
(1 row)

--- Querying the real column shows only the inserted value:
SELECT name FROM mtns;
 name
------





 Fuji
(6 rows)

To instead add the real column and import values from the __raw__ column, use the DEFAULT option with the MAPLOOKUP function to find the value in the flex data:

=> ALTER TABLE mtns ADD COLUMN name VARCHAR
DEFAULT (MapLookup(mtns.__raw__, 'name'))::VARCHAR;

--- column now has all values:
=> SELECT name FROM mtns;
     name
---------------
 Fuji
 Everest
 Mt St Helens
 Denali
 Kilimanjaro
 Mt Washington
(6 rows)

Changing the __raw__ column size

You can change the default size of the __raw__ column for flex tables you plan to create, the current size of an existing flex table, or both.

To change the default size for the __raw__ column in new flex tables, set the FlexTableRawSize configuration parameter:

=> ALTER DATABASE DEFAULT SET FlexTableRawSize = 120000;

Changing the configuration parameter affects all flex tables you create after making this change.

To change the size of the _raw_ column in an existing flex table, use the ALTER TABLE statement to change the definition of the __raw__ column:

=> ALTER TABLE tester ALTER COLUMN __raw__ SET DATA TYPE LONG VARBINARY(120000);
ALTER TABLE

Changing flex table real columns

You can make the following changes to the flex table real columns (__raw__ and __identity__), but not to any virtual columns:

Dropping flex table columns

There are two considerations about dropping columns:

• You cannot drop the last column in your flex table's sort order.

• If you have not created a flex table with any real columns, or materialized any columns, you cannot drop the __identity__ column.

Updating flex table views

Adding new columns to a flex table that has an associated view does not update the view's result set, even if the view uses a wildcard (*) to represent all table columns. To incorporate new columns, you must re-create the view.