Hi, we are using version 11.2.0.4 of oracle exadata. We are in process of changing an existing table design to cater future business requirement and also to sustain future growth. We are mainly looking into suitable partitioning scheme based on our future usage/maintenance. This table is OLTP kind which already contains ~8 different indexes. Currently table shows Avg row length as ~156bytes. And the table is populated with ~100-150million rows daily and will persists ~6 months of data.
In that new design the table will be range partitioned on a truncated date column and subpartitioned by hash on a number column. But we just found that the column which we had decided to keep as partition key, is having UPDATE query running on that from one third party process. And ~10% of rows i.e 10million to 15million rows for that partition key column are getting updated daily based on certain criteria. And this means we have to set row movement enabled for that table if we will go with this same partition key. And also it means all those ~8 indexes will also be UPDATED and behind the scene Oracle will perform DELETE from old partition+ Insert into new partition those ~10% rows. So wanted to understand from experts , If going ahead with this UPDATE partition key approach going to be troublesome for us in future wrt performance/maintenance etc and we should avoid this design? Or its okay considering 10% rows UPDATE/movement from one to other partition? or we should follow some other possible way to cater this UPDATE?