Pre-Join Projection

A subset of query specific projections that are used to store the results of a join between a single large (fact/anchor) table in the logical schema with one or more dimension tables. As with other query-specific projections, HP Vertica calculates and stores a new result set each time data is inserted or loaded into these tables. This provides a significant performance advantage over joining tables when queries are run.

The result set of a pre-join projection is typically sorted for a specific query or commonalities in a class of queries based on the query predicate. This provides optimal query performance.

Pre-join projections can have only inner joins between tables on their primary and foreign key columns. Outer joins are not allowed in pre-join projections

Before creating the pre-join projection, we need following steps.

1. Create table with primary keys and add foreign key constraints
2. Create anchor tables
3. Create pre-join projection

Here is the example.


<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: &quot;andale mono&quot; , &quot;lucida console&quot; , &quot;monaco&quot; , &quot;fixed&quot; , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code style="color: black; word-wrap: normal;">
ALTER TABLE sales  DROP CONSTRAINT IF EXISTS FK_Sales_product;

DROP TABLE IF EXISTS product cascade;
Drop table if exists sales cascade;

create table product(
product_id int NOT NULL ,
description  varchar(100) ,
date_added datetime);

ALTER TABLE product ADD PRIMARY KEY (product_id) ;


Drop table if exists sales;
CREATE TABLE sales (
sales_id integer ,
code varchar(20),
quantity smallint,
amount money,
product_id int NOT NULL,
sales_date datetime);

ALTER TABLE sales ADD PRIMARY KEY (sales_id) ;


ALTER TABLE sales    ADD CONSTRAINT fk_sales_product FOREIGN KEY(product_id)     REFERENCES product(product_id);

INSERT INTO sales
select 1,'RC012',1,50,1001,now();


INSERT INTO product
select 1001,'Wagon',now();


--SELECT ANALYZE_CONSTRAINTS('sales');


CREATE PROJECTION sales_anc (
        sales_id,
        code,
        quantity,
        amount,
        product_id,
        sales_date)
   AS SELECT * FROM public.sales UNSEGMENTED ALL NODES;

 CREATE PROJECTION product_anc (
       product_id,
       description,
       date_added)
   AS SELECT * FROM public.product UNSEGMENTED ALL NODES;
 
   select refresh('sales');
   select refresh('product');
 
   select * from projections where anchor_table_name='sales';
 
 
 
   CREATE PROJECTION Sales_product (
       sales_id,
       code,
       amount,
       description,
       sales_date
       )
   AS SELECT sales_id,code,amount,description,sales_date
   FROM public.sales s join product p ON s.product_id=p.product_id
   ORDER BY s.sales_date;
 
 
   explain SELECT count(*)
   FROM public.sales s join product p ON s.product_id=p.product_id
   ORDER BY s.sales_date;
 
 
</code> </pre>