Preserving Optimized Projections Across Drop Table Statements

Hi

Before dropping/reloading tables you can run export_objects for the tables involved which can help you preserve the DDL of the same object/table which it has earlier.

You can use below command to perform the same:

vsql=> select export_objects('/<path>/<file_name>',<schema_name.table_name>');

For example:

dbadmin=> \dt
List of tables
Schema | Name | Kind | Owner | Comment
--------+-------+-------+---------+---------
public | test | table | dbadmin |
public | test2 | table | dbadmin |

dbadmin=> select export_objects('/home/dbadmin/test.sql','public.test');
export_objects
-------------------------------------
Catalog data exported successfully

(1 row)

dbadmin=>

[dbadmin@~]$ cat test.sql

CREATE TABLE public.test
(
node_name varchar(128),
node_id int,
node_state varchar(128),
node_address varchar(80),
node_address_family varchar(10),
export_address varchar(8192),
export_address_family varchar(10),
catalog_path varchar(8192),
node_type varchar(9),
is_ephemeral boolean,
standing_in_for varchar(128),
node_down_since timestamptz
);

CREATE PROJECTION public.test /*+createtype(A)*/
(
node_name,
node_id,
node_state,
node_address,
node_address_family,
export_address,
export_address_family,
catalog_path,
node_type,
is_ephemeral,
standing_in_for,
node_down_since
)
AS
SELECT test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
FROM public.test
ORDER BY test.node_id
SEGMENTED BY hash(test.node_id, test.is_ephemeral, test.node_down_since, test.node_type, test.node_address_family, test.export_address_family, test.node_address, test.node_name, test.node_state, test.standing_in_for, test.export_address, test.catalog_path) ALL NODES KSAFE 1;

CREATE PROJECTION public.abc
(
node_name,
node_id,
node_state,
node_address,
node_address_family,
export_address,
export_address_family,
catalog_path,
node_type,
is_ephemeral,
standing_in_for,
node_down_since
)
AS
SELECT test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
FROM public.test
ORDER BY test.node_name,
test.node_id,
test.node_state,
test.node_address,
test.node_address_family,
test.export_address,
test.export_address_family,
test.catalog_path,
test.node_type,
test.is_ephemeral,
test.standing_in_for,
test.node_down_since
UNSEGMENTED NODE v_test_node0001;

SELECT MARK_DESIGN_KSAFE(1);

Hope this helps!!

Regards

Rahul Choudhary

This post have very detailed description on how to use Database Designer to build projections and explains how to get SQL scripts at the end.

http://www.dbjungle.com/using-database-designer-to-build-projections-with-optimal-sort-order-in-hp-vertica/

Hope this helps