Re: Table with ~255+ columns

Thank you.

Yes I am yet to read all the points and test/analyze my table data pattern as suggested by Jonathan and Sayan in the blogs which they pointing to. But one thing I just notice the table has 277 columns not 270, hope that will not change the math much. I will analyze the data asap and revert. Thank you.

On Thu, 19 Nov 2020, 11:27 pm 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