Avoiding super projection

[fotographs]

My understanding is that a base table is required to load data and have projections defined on the base table for targeted performance.

We have a base table with a super projection and an optimized projection . The optimized projection takes up just 20% of the super projection size.

Is there a way we can avoid the super projection and simply work with the optimized projection (thats all we need)....the reason I ask this is the Terabyte limit on the community edition.

[JimKnicely]

Hi,

You can always query the non-super projection projection directly...

Code: Select all

dbadmin=> select projection_name, is_super_projection from projections where anchor_table_name = 'main';
  projection_name   | is_super_projection 
--------------------+---------------------
 main_b0            | t
 main_b1            | t
 main_proj_node0001 | f
 main_proj_node0002 | f
 main_proj_node0003 | f
 main_proj_node0004 | f
(6 rows)

dbadmin=> select * from main_b0;
 c1 | c2  |  c3  
----+-----+------
  1 | Jim | Jane
(1 row)

dbadmin=> select * from main_proj_node0001;
 c1 |  c3  
----+------
  1 | Jane
(1 row)

[fotographs]

Hi

I was looking more at purging the super projection to keep data usage under control. (I am aware that the actual projection can be queried directly)..

Here is what I need

purge super_projection (is this allowed)..

[JimKnicely]

Hi,

I think every table needs a super projection... unless you drop your ksafety to 0.

If you have an optimized projection are you saying that Vertica is not choosing to use it in your query explain plan?

[fotographs]

Vertica chooses the correct plan..I just need to purge the super projection data but retain the optimized projection...(ie. I have partitioned the data by date...can I drop just the partitions of the super-projection while keeping the partitions of the optimized projection ?

[JimKnicely]

Partitions are at the table level and not at the partition level. So no, you can not drop partitions from projections.

Why not just create a new table with the smaller subset of data from the bigger table and then drop the bigger table?

[fotographs]

I was hoping to avoid an extra load

Looks like I have to create a separate table and manage space accordingly

Thank you for your help.