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