RE: converting non partition to partition table

Please consider time in your partitioning.  

First, if at the time of original insert there is a known at least approximate date of deletion requirement, you probably want your highest level of partitioning to be a date range. Common life cycle time frames are 7-9 years and 25 years (EPA projects, for example, require that you keep everything bit of information for 25 years after project completion, but even then you might want to disconnect partitions older than 7-9 years to an archive.) IF you have a life cycle that is mostly known at the time of original insertion, then the time saved by reducing “delete” to a dictionary operation usually is more important than everything else.  

Second, even if each row may be logically eternal once it is inserted, there may be an elapsed time after initial insert when the likelihood of a given row being updated is rare enough that making tablespaces containing partitions range partitioned by date can be usefully made read only. (Requiring making the containing tablespace RW temporarily if some row does need to be repaired, but potentially dramatically reducing a lot of maintenance operations.)  

Third, even if each row eternally has some attributes in flux, time periods of interest related to the original insert date may be a fact of the queries you service.  

Fourth, if your time based lifecycle is short, it *may* be useful to leave your existing table in one chunk “less than” range value exchanged into your new partition structure that is nicely diced up in the future by date range and in a while you just unhook the stuff that exists today as a monolith. Then all your moves are just dictionary operations apart from the new local index builds.  

Please also consider your query set, indexes frequently used in queries, and whether physically ordering this data that you have to move at least once is worth ordering so that at least one index is a huge winner on clustering factor. Apart from artificial data sets to illustrate anti-correlation of block clustering for two attributes, I have never seen a case where making one index path “perfect” by physically ordering (quite easily by attribute clustering in recent releases) the rows to match your most important index access path is likely worthwhile. Notice that I did not advocate re-ordering existing data just to make it “nice.” That would imply test measurements indicated the effort was worthwhile. But if you have to move it anyway, even a small win on subsequent access is probably worthwhile. IF you are using compression and this order tends to place only one or two values of highly repeated attributes in order in the same block, the win can be massive (pun intended.)  

Physical ordering is never a Codd requirement in the relational world. Requiring physical ordering for correctness is in fact a violation if you want your RDBMS to be Codd compliant. But operationally it can be a large factor in performance. And just as no one can tell you not to assign a value to null in a given column, no one can tell you that you cannot physically order your data to your advantage.  

Just please do not create a recurrent treadmill of maintenance of low value.  

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Wednesday, March 31, 2021 12:43 AM To: Jonathan Lewis
Cc: Oracle L
Subject: 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