RE: Table with ~255+ columns

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 19 2020 - 18:56:59 CET