How to automagically create the superprojection on Create Table ?

scoulibaly

When I issue a CREATE TABLE on vsql, the superprojection is not created until I insert a row. I need the superprojection to be created as soon as the table is created (some of my custom process might do SELECT/UPDATE on the table, making the query to fail). How can I force the CREATE TABLE to create the superprojection straightaway ?

scoulibaly

Doc says : Vertica automatically creates a superprojection for the table when - you use an INSERT INTO or COPY - you use CREATE TABLE AS SELECT to create a table - you use ENCODING encoding-type and ACCESSRANK integer in column definition - you use hash-segmentation-clause - you use range-segmentation-clause - you use ORDER BY in CREATE TABLE I think i'll go with the ORDER BY

[Deleted User]

Why do you need a superprojection? SELECT and UPDATE queries will not fail on tables with no superprojection. They will succeed. They just won't return or update any rows (as you would expect with an empty table). In general, if Vertica hasn't created a superprojection for you, the reason is that you don't (yet) need one. The docs are simply enumerating the cases where Vertica needs to create a superprojection for you in order to proceed. (In which case it will go ahead and do so automatically.) If you find an example where this is not the case, please post it. That said, if you know how you want to sort your table, specifying ORDER BY up front will tend to give you better performance.

Alex_mein_Nachn

We encountered already several cases there this behaviour is a problem. SWAP PARTITIONS doesnt work for tables without a super-projection. The workaround is to insert and delete a row. The second case: If we create a table and a prejoin-projection on it then we cant drop the prejoin-projection, because there would be no projections left for this table.