Mark,
A real gem (12.2+ I think) that I love with partitions and "rebuild it once
when all the excitement is over" is the option for attribute clustering.
Not to mention basic compression. It gives you great scope of optimising
history for the most important queries.
Regards
Jonathan Lewis
On Thu, 19 Nov 2020 at 17:57, Mark W. Farnham <mwf@xxxxxxxx> wrote:
And…
IF your rows are born missing some column values, but your applications
fill them in afterwards, but the cycle is complete when your mentioned
partition week is complete,
THEN (and please don’t leave out the IF), this could be a big opportunity
for rebuilding the partition once each time it becomes “stale” with respect
to lengthening rows.). Notice that filling in a null date lengthens the
row, but updating an actual date to another date does not change the row
length, and likewise nullifying an actual date does not grow the row.
IF this is the case you **may** benefit from the rebuild (if there is an
advantage to physically ordering rows within the partition), or move the
partition and setting the pctfree of the target as 0. 10% is nothing to
sneeze at IF you’re sure you’re done lengthening rows. Likewise *
*sometimes** physically ordering.
Good luck. These are second order effects compared to what JL and Sayan
have already mentioned. Nevertheless they are usually well worth while IF
you only have to do them once per chunk (time in your case) of data.
Please remember all my IFs. Rebuild treadmills are to be avoided without
certainty of the benefit (rare). Rebuilds of once or twice or some other
very small n for which you know why you are rebuilding can be useful and if
reasonable in size can be cheaper just to see if they help if the
exhaustive research is more expensive than the rebuild.
mwf
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Sayan Malakshinov
*Sent:* Wednesday, November 18, 2020 7:40 PM
*To:* Jonathan Lewis
*Cc:* Lok P; Oracle L
*Subject:* Re: Table with ~255+ columns
Hi Lok,
As I see you are on 11.2.0.4 now. So first of all, I would suggest you to
upgrade to at least oracle 12.2, since intra-block row chaining doesn't
work for updates before 12.2:
http://orasql.org/2017/02/12/intra-block-row-chaining/
http://orasql.org/2017/06/17/intra-block-row-chaining-optimization-in-12-2/
http://orasql.org/wp-content/uploads/2017/04/Intra-block-row-chainingRuOUG.pdf
The last example in the presentation above shows how just 1 row has been
split into 101 blocks.
So any updates of null values in the end of rows you ran before split row
into another block.
--
Best regards,
Sayan Malakshinov
Oracle ACE Associate
Oracle DB Developer Choice Award winner
http://orasql.org
