Re: Modifying column faster way

On 5/25/23 15:51, yudhi s wrote:
> Hello Listers,   We have a big table ~3.5TB in size holding
> ~15billion+ rows , 65 columns and its range partitioned(~200+
> partition) on a date column and hash subpartition(32 hash
> subpartitions for each partition) on a separate ID column. The
> requirement is to modify a column data type from number(10) to
> Varchar2(10) for one of the columns in this table. This column will be
> nullable. It's a 19C(19.15) oracle Exadata production database.
>
> We don't have an environment to try with similar volume , but as it
> looks to me Oracle will try to update each and every value of that
> column while modifying the data type, so that will take a long time
> considering the size of the table. So I wanted to understand from
> experts , is there any possible way here to modify the column data
> type faster?
>
> Regards
> Yudhi

The problem with updating such a monster is that you have to write rows to redo files and undo segments. That will cause archive log storm, waiting on the log switch and may even consume the entire UNDO space, depending on how you do it.  Since your database version is >= 19.6, you can use fast update, as described here:

https://dbwhisperer.wordpress.com/2023/03/24/fast-ingest-in-oracle-19c/

That will be faster because it doesn't write data to either redo files or undo segments. However, be aware that it is not possible to roll the transaction back if you decide to use this method. That is why I pointed you to my blog instead of Andy Rivenes's blog which you can find here:

https://blogs.oracle.com/in-memory/post/fast-ingest-updates

I am not sure about the speed-up you will get with this technique but at least you will not see an archive log storm and you will not run out of UNDO tablespace.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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