How delete mechanism works on projections without the delete key on base table?

[Fred]

Hello,

I am wondering how does Vertica identify the records to be deleted from a projection if the projection does not have the column based on which the delete is performed on the underlying base table. Does anyone have any idea or does online documentation provide any explanation(if I missed it) as to how it works internally?

Thanks in advance for the insight.

Regards, Fred

[NorbertKrupa]

Not sure if I understand. What do you mean by underlying base table?

There's stitching between the columns (not documented anywhere). A change to any column in a table would result in the entire row being marked for deletion on all projections for the table.

[Fred]

By base table, I meant simply the table on which the projections are built. Let me give an example. Please ignore any inconsistencies in the code in terms of syntax or performance point of view:

If my table structure is say something like this:

create table tbl
(
col1 number,
col2 number
)
segmented by hash(col1) all nodes ksafe 1;

and I have a custom projection something like:

create projection custom_proj
as
select col1
from tbl;

Now I perform a delete on the table like this:

delete from tbl where col2=<some value>.

Since the projection custom_proj does not have the col2, how does vertica know/identify the record to be deleted from the projection?

Thanks for your time.

-Fred.

[NorbertKrupa]

Since the projection custom_proj does not have the col2, how does vertica know/identify the record to be deleted from the projection?