Multiple bulk deletes

mkarun2

I have a table say tmp which 3 columns
tmp:
s1,
s2,
s3
I have a master table

table1:
c1,
c2,
c3,
s1,
s2

I want to delete rows from table1 that has data in tmp. ie delete from table1 join tmp on s1,s2,s3

What is the best way to do?

Currently I have multiple delete statements
delete from table1 where s1 in (select s1 from tmp)
delete from table1 where s2 in (select s2 from tmp)
delete from table1 where s3 in (select s3 from tmp)

I understand that delete optimization happens for the above query but is there a better way to do?

Thanks
Mohan

Jim_Knicely

I would run DBD with those DELETE statements so it can create an optimized design.

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AnalyzingData/Optimizations/OptimizingDELETEsAndUPDATEsForPerformance.htm

marcothesane

Can't you create 3 single-column temp tables, and then try:

    DELETE FROM table1 WHERE s1 IN (SELECT s FROM tmp1)
    DELETE FROM table1 WHERE s2 IN (SELECT s FROM tmp2)
    DELETE FROM table1 WHERE s3 IN (SELECT s FROM tmp3)

?
The temp tables would be ordered by their single column, and you'd have to perform a physical design on delete optimization only on the main table ....