RE: Modifying column faster way

OR, copy create CTAS, as per Tim Gorman. Then create the new table EMPTY with the correct columns for the new tables you created with CTAS and the correct partitions defined. Then exchange the new tables back into the new empty table that has not been modified at all.  

IF you have a dominantly used index, you may also consider that ordering to match in the CTASes. There is a theoretical chance that ordering one index order will fubar another (and you can easily build a lab case to show that), but in real world data I have not seen any significant anti-correlation in a few decades of doing this. (You can check the cluster factors before and after for each index to see if any degrade at all and if any degrade compare any possible extra run time cost to the improvement on the dominantly used index.)  

IF you do have a dominant index and do this and you are on something that supports it, you may want to add attribute clustering to the table definition so new chunks of data tend to be in the order of the dominant index. IF you are on something that supports Oracle zonemaps, you may also find that useful.  

Tim, if I got any of the exchange stuff wrong, let us know,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karthikeyan Panchanathan
Sent: Thursday, May 25, 2023 5:17 PM
To: learnerdatabase99_at_gmail.com; Tim Gorman Cc: Oracle L
Subject: Re: Modifying column faster way  

Just to add more with Tim  

First need to add new column with required datatype in Source table( col1_new)  

Create exchange table with CTAS as defined by TIm  

Exchange each partition  

Finally drop original column then rename col1_new to your original column name  

HTH Karth  

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-l