Re: Table with ~255+ columns

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 1 Dec 2020 17:40:41 -0500
Message-ID: <76bb9eb3-8b6d-f42b-0d9e-4dd976b8543b_at_gmail.com>



Hi Jonathan,

I have known about that for some time, but I missed your article. However, I was told that enabling row movement is a requirement for moving chained rows back to the original block. You didn't enable row movement in your example. Is there any difference, can you comment on that?

Regards

On 11/30/20 5:05 PM, Jonathan Lewis wrote:
>
> Mark,
>
> You missed a memo - Oracle can MIGRATE a row back to its original
> location on an update:
>
https://jonathanlewis.wordpress.com/2014/02/10/row-migration/
> <https://jonathanlewis.wordpress.com/2014/02/10/row-migration/>
> The note is a bit old, so I really ought to check it for newer
> versions, I guess.
>
> Oracle MIGHT also do something sensible with rows that are chained
> because of column lengths when the number of columns is no more than
> 255, but anything might happen if there are more than 255 non-null
> columns in the row.
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 30 Nov 2020 at 20:57, Mark W. Farnham <mwf_at_rsiz.com
> <mailto:mwf_at_rsiz.com>> wrote:
>
> I’m not aware of Oracle doing anything like repatriating migrated
> rows or row pieces due to just an update in any version of Oracle.
>
> I tend to think that would require the instantiation of a deity to
> code correctly and I can think of dozens of optimizations that
> would be of greater utility to the user base. Moving segments was
> provided to do that functionality in a bulletproof and relatively
> simple way, so I doubt we will ever see it.
>
> Please let me know if I missed a memo.
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Jonathan Lewis
> *Sent:* Monday, November 30, 2020 6:03 AM
> *To:* Lok P
> *Cc:* Oracle L
> *Subject:* Re: Table with ~255+ columns
>
> The answer to that question may be dependent on the version of
> Oracle and on how the row got to the state it was in before the
> update to null of the 276th column.
>
> The first point to consider, though, is that the row HAS to be in
> at least two pieces if *any *column after the 255th is non-null,
> so updating just the 276th isn't likely to have any significant
> effect.  Given that you're on 11.2.0.4 I doubt if Oracle will do
> anything nice all you do is set that column to null - in fact it
> may simply find another way of doing something nasty, but you'd
> have to do some experiments to find out what that could be.
>
> Regards
>
> Jonathan Lewis
>
> On Sun, 29 Nov 2020 at 19:55, Lok P <loknath.73_at_gmail.com
> <mailto:loknath.73_at_gmail.com>> wrote:
>
> One thing i saw, few of the stats gather statements on this
> table are logging heavy statistics "table fetch continued row"
> on this table, And another thing i notice, as per the NULL/NOT
> NULL data pattern i published from one of the sample
> partition, it shows out of total 277 columns , we have 276th
> position column populated with NOT NULL value for almost all
> the rows, so its confirm that we must be having the rows split
> into two parts as we have 276th column inserted as NOT
> NULL. And , as we have the row size restricted to ~avg
> ~277bytes, so my thought was , at max it will intra row
> chaining considering we are not updating the columns in this
> table. But then I observed in old partitions that the 276th
> position column is UPDATED to NULL value for all of those
> rows, so in that case will Oracle try to readjust them into a
> single piece again, or it will be in the same two rowpiece only?
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 01 2020 - 23:40:41 CET

Original text of this message