The Vertica Database Forums

Hi All,

I am new to vertica and I want to implement below case scenario with Pre-Join projection. Please suggest how can i achieve this.

I have two tables like FactA(CustomerID (PK),Units) &FactB(CustomerID (FK),Sales) and the data is avail like below:
FactA FactB
CustomeID Units CustomerID Sales
------------ ------ -------------- -------
1 10 1 100
2 20 2 200
3 30

i want to create a pre-join projection which can return below result set:

CustomerID Units Sales
--------------- ------ -------
1 10 100
2 20 200
3 30 0 or null

Please suggest


Regards
Sree

We cannot create pre-join projections that contain LEFT joins; Only INNER joins are supported.

why do you want to go for a prejoin? Did you look at these options?

1. Consolidate both the facts, if you do not need to maintain them separately.
2. Create a view with left outer join. This is a dynamic approach and saves desk space.
3. Create a new table to hold left outer join output. This gives better performance but have to be maintained manually.

Hi Mrao,

Thank you for the update.

Fact A table has updates on almost daily basis and FactB table has weekly updates. FactB table takes around 4 hours to get updated.

As I wanted the consolidated output with good performance, I thought pre-join would be the good option. But it does not work to fetch left-join output.

Please suggest me the best work around to achieve this with good performance.

Best Regards
Sree

Hi!

[DELETED]

4 hrs looks really long time to me. I will first try to fix the fact tables and then will go to the next.

I will consider sKwa ideas. I may be able to suggest a solution if I have the following info.

1. Fact tables volumes
2. High level logic from source to Fact
3. Any partitions on the fact tables
4. what steps in the process are taking time
5. How many changes are there per run