RE: Modifying column faster way
Date: Thu, 25 May 2023 18:04:25 -0400
Message-ID: <17d001d98f54$e023aec0$a06b0c40$_at_rsiz.com>
Tim, if I got any of the exchange stuff wrong, let us know,
First need to add new column with required datatype in Source table( col1_new)
Create exchange table with CTAS as defined by TIm
Finally drop original column then rename col1_new to your original column name
Sent from Outlook for iOS <https://aka.ms/o0ukef>
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
behalf of yudhi s <learnerdatabase99_at_gmail.com>
Sent: Thursday, May 25, 2023 4:29:45 PM
To: Tim Gorman <tim.evdbt_at_gmail.com>
Cc: Oracle L <oracle-l_at_freelists.org>
Subject: Re: Modifying column faster way
Thank you Tim. I am still struggling to understand your point. My understanding is, Partition exchange will need same structure I. E column type and lengths between both the objects, so how partition exchange can be utilized for modifying the column data type here. Can you explain bit more?
On Fri, 26 May, 2023, 1:47 am Tim Gorman, <tim.evdbt_at_gmail.com> wrote:
Recreate each range partition using a highly parallel CREATE TABLE ... AS SELECT (CTAS) command with the change to the column in the SELECT list, and then EXCHANGE the resulting table with the source range partition. This will be lightning fast compared to any other method, and this technique has worked well since Oracle8.0 when range partitioning was first introduced.
On 5/25/2023 3:51 PM, 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 26 2023 - 00:04:25 CEST