Working in a DSS environment, we often need to truncate table partitions 
regarding a WHERE condition and have to:

- query the dictionnary to identify thoses partitions,

- build SQL statements,

- truncate all partitions covered by the WHERE condition

- eventually delete the rest ...

- perform additionnal maintenance tasks

Wouldn't it be possible to make it possible directly in SQL as a TRUNCATE TABLE 
WHERE syntax ?

I have done something similar using a procedure in Oracle:

- identifying partitions using oracle rowids by a SELECT using the same WHERE 

- for each partition check if data NOT in WHERE condition

- then DELETE WHERE or TRUNCATE PARTITION depending of data distribution.

Maybe there are some other constrainst like locking, FK disabling/enabling, 
indexes rebuild ...

Would be pleased to ear your feedback regarding this.



Reply via email to