Column modification happening in serial
From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 6 May 2022 16:24:13 +0530
Message-ID: <CAEjw_fgxTc2x=7-GpZjVKr62rL19L2DjYUj6DFppuG-Wr-QVWQ_at_mail.gmail.com>
This is version 19c of oracle 19.11 to be specific. We have table with ~25 billion rows and is ~2.4T in size and is range partitioned with 500 partitions. We have a column we want to convert it to not null + validate and post that these four column will comprise the primary key. But making this columns NOT NULL is running forever. And the bad part is we are not seeing this happening in parallel even the table is having degree parallel-32 and also we see the wait events its showing 'cell multiblock physical read' allover but not "cell smartscan". So this alter query is running in serial fashion but not running in parallel.
Date: Fri, 6 May 2022 16:24:13 +0530
Message-ID: <CAEjw_fgxTc2x=7-GpZjVKr62rL19L2DjYUj6DFppuG-Wr-QVWQ_at_mail.gmail.com>
This is version 19c of oracle 19.11 to be specific. We have table with ~25 billion rows and is ~2.4T in size and is range partitioned with 500 partitions. We have a column we want to convert it to not null + validate and post that these four column will comprise the primary key. But making this columns NOT NULL is running forever. And the bad part is we are not seeing this happening in parallel even the table is having degree parallel-32 and also we see the wait events its showing 'cell multiblock physical read' allover but not "cell smartscan". So this alter query is running in serial fashion but not running in parallel.
I tried to see few blogs as below but seeing below two are contradicting , the first doc stating ALTER ATBLE MODIFY can run in parallel by just stating the parallel degree for table but its somehow now working as i am testing. And the second blog showing 'the ddl statement that can be parallelized' but 'alter table modify' statement is not there. So wanted to understand what is the fastest way to achieve this?
alter table TAB1 modify(COL1 not null,COL2 not null,COL3 not null,COL4 not null ) parallel 32;
Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null) (Doc ID 2264469.1)
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 06 2022 - 12:54:13 CEST