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@xxxxxxxx <mailto:mwf@xxxxxxxx>> 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@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *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@xxxxxxxxx
<mailto:loknath.73@xxxxxxxxx>> 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?
