>> > The big win that is associated with table partitioning is using
>> > constraint exclusion to avoid unnecessary partitions scans.
>
> there is no reason for not using the 'CASE condition' for constraint exclusion.
There is if you can't rely on the rows to be in the right partition.
Allowing DDL commands to change the partitioning layout without moving
the rows is going cause a lot of problems for you, I think.
I'm also not sure how skillful the constraint exclusion logic is at
proving theorems when CASE statements are involved. It might be good
to test that before going too far with this approach.
...Robert