View and projection

Wayne_1

In Vertica 6.1, projection cannot be created directly based on a query which select data from a view. Also projection does not support the query with outer join directly. Could you explain how the projections will be created if the application use the queries with view or/and out join?  thanks.

Rahul_Choudhary

For each table in the database, HP Vertica requires a minimum of one projection, called a superprojection
To get your database up and running quickly, HP Vertica automatically creates a superprojection when you load or insert data into an existing table created using the CREATE TABLE or CREATE TEMPORARY TABLE statement.
Default superprojections do not exploit the full power of HP Vertica. Therefore, Vertica recommends loading a sample of your data and then running the Database Designer to create optimized projections. Database Designer creates new projections that optimize your database based on its data statistics and the queries you use. The Database Designer:
Analyzes your logical schema, sample data, and sample queries (optional)Creates a physical schema design (projections) in the form of a SQL script that can be deployed automatically or manuallyIn most cases, the designs created by the Database Designer provide excellent query performance within physical constraints. The Database Designer uses sophisticated strategies to provide excellent ad-hoc query performance while using disk space efficiently. If you prefer, you can design custom projections.
For more information about creating projections, see Designing a Physical Schema in the Administrator's Guide.
Kindly go through below documented link for more information on this:
https://my.vertica.com/docs/6.1.x/HTML/index.htm#12033.htm

kesten_broughto

What is the actual purpose of a view in vertica?  Is anything happening at the physical layer or is it just a way to alias a subquery?  Can they be used to improve performance by, for example, limiting a large table to a tiny fraction of its rows if that fraction is all that is needed for the most common queries?  Or would projections do that magically (by partitions?) if the query were fed to the Db Designer?

id10t

Hi!

Is anything happening at the physical layer

Nothing happening - its VIEW. If you want that will happen so use in LAPs (live aggregate projections).

VIEW

A named logical relation specified by an associated query that can be accessed similarly to a table in the FROM clause of a SQL statement. The results of the query are not stored but obtained on the fly when the SQL referencing the view is executed.

is it just a way to alias a subquery?

Just an alias.

Can they be used to improve performance by, for example, limiting a large table to a tiny fraction of its rows if that fraction is all that is needed for the most common queries?

Views - NO!
Take a look on LAPs. LAPs "improves" aggregate queries like SUM/MIN/MAX/AVG

Or would projections do that magically (by partitions?)

Correct - all magic happens in projections and done by projections. Main performance tuning for queries you will do with DBD.