Re: Modifying column faster way
Date: Thu, 25 May 2023 16:17:34 -0400
Message-ID: <1694fd42-0739-14d8-a2ae-ab4b31d799ac_at_gmail.com>
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 Thu May 25 2023 - 22:17:34 CEST