Re: Modifying column faster way

Thank you so much. Got the point.

On Fri, 26 May, 2023, 6:27 am Tim Gorman, <tim.evdbt_at_gmail.com> wrote:

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