RE: Column drop issue

+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