Projection Deletes

Moderator: NorbertKrupa

Post Reply
Ezhil
Newbie
Newbie
Posts: 1
Joined: Mon Sep 26, 2016 6:40 am

Projection Deletes

Post by Ezhil » Mon Sep 26, 2016 7:01 am

Hello,

I need to understand how data from a projection gets deleted. Consider the below scenario with a table containing 3 columns a,b,c and there are two projections
a) Super Projection
b) User-Defined Projection with columns a and b.

If I delete from the table having the predicate as c, how will the data be removed from the user-defined projection since there is no c column ?

Ex : CREATE TABLE TEMP(A INT,B INT,C INT);

CREATE PROJECTION TEMP_SUPER
(
A,B,C ) AS SELECT A,B,C FROM TEMP;

CREATE PROJECTION TEMP_USER
(
A,B ) AS SELECT A,B FROM TEMP;

INSERT INTO TEMP(a,b,c) SELECT 1,2,3;
INSERT INTO TEMP(a,b,c) SELECT 4,5,6;

DELETE FROM TEMP WHERE C = 6;

How will data from User-defined projection TEMP_USER be removed ??

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Projection Deletes

Post by JimKnicely » Mon Sep 26, 2016 1:44 pm

Hi,

Heh heh. Well, the fact that Vertica can delete records in all anchor table projections even when the delete predicate column is not included in the projection definition is part of Vertica's "secret sauce". This functionality cannot be reveled here :)

But it is important to point out that when all columns required by the DELETE or UPDATE predicate are present in a projection, the projection is optimized for DELETEs and UPDATEs. DELETE and UPDATE operations on such projections are significantly faster than on non-optimized projections.

DELETE and UPDATE Optimization
https://my.vertica.com/docs/8.0.x/HTML/ ... rmance.htm
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”