Hello,
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 conditions
- 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.
Regards
PAscal
