oracle普通表转分区表之后的唯一索引创建问题

sql_tigerliu

jihuyao 发表于 2022-3-18 01:22
Why not partition on orderid for both performance and maintenance purpose?  If orderid is from seque ...

这个方法确实能解决local index全局唯一的问题, 但是如果sequence不设置成ordered=y, sequence与插入时间就对应不上了.  一般还是以日期做分区字段最为常见.

jihuyao

Even though sequence is used one can only truncate old partitions after making sure all rows in it have date meeting purging cutoff date.  And also it is necessary to keep ONE low boundary partition available for possible business operations in future.  This implies a maintenance window for purging is needed.  If have to perform delete, lock each partition and then transfer minor rows to temp table and then truncate and then exchange back (all in maintenance hours).  If only minor rows need to be purged then just delete.  As long as undo tablespace can be well managed redo should not be problem on such set data process for minor rows delete.

ZALBB

可以尝试下对SUBTIME字段，使用TIMESTAMP类型，精确到微妙，

sqysl

jihuyao 发表于 2022-3-18 01:22
Why not partition on orderid for both performance and maintenance purpose?  If orderid is from seque ...

这种方法如果orderid有序还好，否则，如果随机产生，维护清理数据就比较麻烦。

sql_tigerliu

jihuyao 发表于 2022-3-29 02:57
Even though sequence is used one can only truncate old partitions after making sure all rows in it h ...

如果使用了scale extend sequence 也是不行的, 这种sequence 是为了解决高并发插入时index contention问题; 后面说的delete , 在高版本有简单的方法, 就是使用alter table move partition  including rows.