Re: Modifying column faster way

Mark and Karthikeyan,

Thanks for the support!

For any bulk UPDATE or DELETE operation, please always try to figure out how to convert it into a bulk INSERT operation instead.  INSERTs are always faster than UPDATEs and DELETEs.

This presentation (HERE
<http://evdbt.com/download/presentation-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database/>) and white paper (HERE
<http://evdbt.com/download/paper-scaling-to-infinity-partitioning-data-warehouses-on-oracle-database-2/>) is entitled for data warehousing, but the techniques described also work for the task here.

Also, this video HERE
<https://www.youtube.com/watch?v=pvbTAgq_BBY>(entitled "/The Fastest UPDATE is an INSERT/") was recorded at Oak Table World 2012, which was co-resident with another little event called Oracle Open World 2012 in San Francisco.  The production was designed by a friend of Kyle Hailey.

Hope this helps!

Thanks!

-Tim

On 5/25/2023 6:04 PM, Mark W. Farnham wrote:
>
> 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