Re: converting non partition to partition table

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_at_gmail.com> 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_at_gmail.com> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l