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 ??
Projection Deletes
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Projection Deletes
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
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.