Re: Column drop issue

Thank you so much.

The blogs which Jonathan pointed to on the 255 columns issue in Oracle are simply awesome. Thank you. Will evaluate different possible options which suits best in our case here.

On Fri, 8 Apr 2022, 5:20 pm Mark W. Farnham, <mwf_at_rsiz.com> wrote:

> +42 on doing the extra work JL mentions below and brilliantly outlines “by
> hand” since this table has already been indicted as causing things to be
> slow. (The amount of careful human work avoided by using Oracle’s nearly
> magical tools built in is NOT being deprecated. This just seems like a case
> where you might want to do it as nearly perfectly as you can for the exact
> situation.)
>
>
>
> Now one more thing: If any of those mostly null columns are subjects of
> per row predicate logic, then you WANT to keep them in the first piece.
>
>
>
> Okay, another thing: One of the justifications of the enhancement request
> to Oracle to allow updateable view joins was approximately this: Tables
> exceeding the single piece column limit but where the columns used apart
> from insert are smaller in number **may** be at an advantage (barring a
> lot of gratuitous select * queries, which tend to be stupid and slow in
> wide columns anyway) to break the table into two (n, really) pieces by
> table now known as the view of the current table name. Then if your query
> only references columns in the popular part of the (now logical instead of
> physical) piece you will have dodged all the row piece physical under the
> covers work that makes extremely wide tables in Oracle possible. The price
> in space is duplication of the identical unique join key between the pieces
> of the view.
>
>
>
> Last thing: Since this table is already an issue and probably you are
> going to physically rebuild it as JL mentioned, consider strongly using
> attribute ordering (or any other means) to make the physical order of rows
> in the pieces match the order of your mostly popularly used index but
> including also the most popular order by columns after the columns of that
> index.
>
>
>
> In theory it is possible that this last bit could worsen the cluster
> factor and performance of other indexes, and it is possible to set up a
> table with an anti-correlated order for some other index as a lab
> experiment. I have never encountered an actual degradation of other access
> paths. Usually, you get one big winner, some smaller winners (in nature
> there tends to be positive correlation of important indexes, especially if
> time or a monotonically increasing id is involved, and the rest tend to be
> about the same.
>
>
>
> Good luck.
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Friday, April 08, 2022 5:00 AM
> *To:* Oracle L
> *Subject:* Re: Column drop issue
>
>
>
>
>
> Did you use the "checkpoint" syntax to commit after every few thousand
> rows - necessary for a job this big - as a drop without checkpoint might be
> behind the ORA-01555.
>
>
>
> The drop column command is very naively written - it generates one undo
> and one redo records per column per row in a block, the undo is likely to
> be about 100 bytes per column per row PLUS the content of the column, the
> redo is likely to be closer to 250 bytes + contents. So your 20 column drop
> is likely to produced at least 2KB of undo and 5KB of redo per row. But
> then if you've got chained rows there's going to be a huge overhead as
> Oracle updates the row and (MAY) re-arrange the chain, and the extra impact
> of undo and reod could be huge, and the randomness of where the chained
> column were and where they end up may be the cause of the ORA-01555.
>
>
>
> Since the table is locked during a "drop column" you don't lose anything
> by locking users out so that you can create a replacement table. I wouldn't
> use dbms_redefition, I'd either create an empty table, or do a "create as
> select" specifying every partition in advance because then I could choose
> to set pctfree = 0 for all the old (never going to change again)
> partitions. (And then I'd run one process per partition to do the copying
> rather than just running a parallel statement).
>
>
>
> There is a warning that goes with this - you need to test on REAL data
> (not an export/import copy, a clone copy) the effects of CTAS, Insert /*+
> append */ and a simple (logged) insert as the choice may make a huge
> difference on the final size and pattern of data in the table. Check this
> note I wrote for 11g/12c a few years back:
> https://jonathanlewis.wordpress.com/2017/05/19/255-columns-2/ (while
> you're at it you might want to browse the catalogue of 255-column articles:
> https://jonathanlewis.wordpress.com/2022/01/25/255-column-catalogue/ )
>
>
>
> Note - by the way - that when Oracle splits a row with more than 255
> columns it works from the END, so for 300 columns you'd have a starting
> piece of 45 column and an ending piece of 255 - so another reason for
> creating a new table is that you MIGHT get some benefit from moving the
> most popular columns to the start of row to minimise the calls for the
> second piece. This behaviour varies (as you will see in the blog) with the
> way that you populate the table.
>
>
>
> This splitting algorithm also means that (in theory, but perhaps not in
> practice) you might get WORSE performance from repositioning the mostly
> null columns because that might make the split appear earlier in the row.
> E.G. current row has 280 columns and column 280 has data in it, the split
> is 25 / 255; you move 20 null columns to the end of the row and the row is
> now (from Oracle's perspective) 260 columns, so the split is 5 / 255 and
> you're doing a chained row fetch more frequently.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
> On Fri, 8 Apr 2022 at 05:19, Pap <oracle.developer35_at_gmail.com> wrote:
>
> Hello Experts, This is a 19c database. We encountered a scenario in which
> an alter query got stuck and eventually failed after many hours with
> Ora-1555. It's a range partitioned table with ~600million rows and ~600GB
> in size and the team is trying to drop ~20 columns out of it using "Alter
> table drop(col1,col2,col3.. coln);" syntax. The wait event we are seeing is
> 'log buffer space' also 'cell multiblock physical read". At one point we
> checked, the "long ops" was showing a 'rowid range scan' and the estimated
> time it was showing was ~60hrs+. So wondering how to make this Alter
> succeed?
>
>
>
> Had a talk with respective team, we got to know this table is
> holding ~256+ rows and encountering row chaining effect in queries , and
> as this table is having ~20+ columns which holds mostly nulls , so we want
> to have those columns placed towards the last of the row, so that row
> chaining effect can be eliminated. For this reason, we first try to drop
> those ~20 columns and then add those again using 'alter table add' , so
> that they will be created towards the end of the row. Any better/faster
> approach through which we can achieve this?
>
>
>
> Regards
>
> Pap
>
>

--
http://www.freelists.org/webpage/oracle-l