Both approaches will duplicate data.
1st approach will need a separate table (causing extra space requirement),
But preferably be simple and good due to downtime flexibility.
2nd will involve interim MVs which also duplicates data, requires space
similar to 1st approach but have hassle of rename indexes/constraints with
rdbms_redefinition.
I would go for 1st one.
thanks
Nitin
On Tue, Mar 30, 2021 at 11:15 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, Its oracle version 11.2.0.4 database hosted on exadata X5
machine. We have a non partitioned table having size ~4TB holding
~10billion rowsand 4 different indexes on it including one primary key. We
want to convert it into a range partition table. We have flexibility to
afford downtime for this operation. So I want to understand what is
the best approach for achieving this? If ,
1) We should create a new blank table with range partitions. And then
insert all 10billion rows into it in parallel direct path mode and
then create local indexes and after that drop the original table ?
OR
2) follow dbms_redefinition considering the table is too big ?
Thanks
Pap
