Hi,
I have Materialized view queries in Oracle which uses complex left outer joins and some union operators. When I want to convert them in Vertica projections, it fails with error messages.
Can anyone please shed lights on how do we get around this problems? Thanks in Advance.
Regards,
Jay
Left Outer Join & Union Operators
Moderator: NorbertKrupa
Re: Left Outer Join & Union Operators
Hello jaypn007
Welcome to Vertica Forums.
First of all you need to understand there is no difference between Oracle's Materialized views and projections in Vertica
So Projections are nothing but materialized views in Vertica.
So, In order to migrate materialized view in vertica. you need to create a table with all columns you need in your projections and projection will be created for this table automatically.
In your scenario, you can create a pre-join projection directly using the CREATE PROJECTION statement,
but it has some limitations
1. It can only have inner joins
2. No where clause allowed in select statement
Sample prejoin projection
You need to migrate your materialized view into tables in Vertica
It will be better, if you can paste some sample code, so that we can give you clear idea on how to convert.
Hope this helps
Welcome to Vertica Forums.
First of all you need to understand there is no difference between Oracle's Materialized views and projections in Vertica
So Projections are nothing but materialized views in Vertica.
So, In order to migrate materialized view in vertica. you need to create a table with all columns you need in your projections and projection will be created for this table automatically.
In your scenario, you can create a pre-join projection directly using the CREATE PROJECTION statement,
but it has some limitations
1. It can only have inner joins
2. No where clause allowed in select statement
Sample prejoin projection
Code: Select all
CREATE PROJECTION prejoin_p (fk, pk, x) AS SELECT * FROM fact, dim WHERE pk=fk ORDER BY x;
It will be better, if you can paste some sample code, so that we can give you clear idea on how to convert.
Hope this helps