We have a PPI table (3.2 Billion rows, 12 months data, partitioned by month). Lat night, a Prod job attempted to drop the oldest partition (approx 164 million rows), and the statement took nearly 7 hours to complete. Sadly, the developer had omitted the 'WITH DELETE' clause from the drop. I would have expected this to generate an error, as I didn't think you could drop populated partitions, but it didn't. However, after 7 hours, the Drop finally finished and it had indeed dropped the partition (ie the table definition no longer shows the dropped month as part of its partition range), but the data which was previously housed in the dropped partition is STILL there on the table. D'oh. I'd have assumed that the 7 hour run was due partly to Transient journalling being done on the 164 million rows - but they weren't actually deleted ! So what's going on ? Oh also, I've checked, and the job wasn't being blocked, although towards the end it WAS blocking quite a few other jobs, apparently because of a Row Hash Write lock on DBC.DBCAssociation. Cheers
Hi John, there's a NO RANGE partition and all rows have been moved to that partition. In that case the delete from the old partition and the merge into the no range partition where transient journaled. Btw, there wouldn't be any difference if you added the WITH DELETE, because this is only for rows which don't fit in any partition.
If you got a no range ten you have to "delete from tab where...." before you modify the PI. Check the manuals about that.