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.