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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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
