Thank you. Yes all the indexes including Primary key will be local only. So
if I get it correct , with both the options, storage space consumption
will be twice the size of the object(table+indexes) because we will be
maintaining two copies till the operation completes (even in case of
dbms_redefinition also as it will have to maintain interim table all the
way till completion). On the other hand the benefit of option-1 (i.e.
having downtime affordable in our case) willbe that it will be faster as we
can rebuild indexes from 4 different sessions utilizing max parallel
threads or max capacity of the system.
Thanks and Regards
Pap
On Wed, Mar 31, 2021 at 12:27 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
You don't say whether the indexes are local, global, or globally
partitioned, and that's always worth knowing.
If you choose option 1 then you can create any local indexes unusable then
alter each partition of each index usable in separate sessions - which
means you can recreate the indexes at the maximum rate your hardware can
handle.
Regards
Jonathan Lewis
On Tue, 30 Mar 2021 at 19:15, 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
