RE: Table with ~255+ columns

+42 regarding attribute clustering.  

It has been used with exceptional results by a customer I cannot mention externally both for a two-stage feed from multiple sources to the long term integrated reporting repository, for "rebuild it once when all the excitement is over" time based “not allowed to update anymore” partitions, AND for cases when none of the attribute clustering columns are allowed to be changed from the insert birth of the row. That last case is frequently true in sensible designs, and the benefits may erupt immediately, even while the excitement is in progress.  

Thanks for calling that out directly. It is exactly as you wrote “a real gem.”  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, November 19, 2020 1:12 PM To: Oracle L
Subject: Re: Table with ~255+ columns    

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_at_rsiz.com> 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_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 - 19:34:10 CET