Re: FW: Adding column and updating a huge partitioned table.

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 8 Jan 2015 15:15:51 -0600
Message-ID: <CAJvnOJZ-ZS4TLKqyK+r3p0=qvHhQ8rhs3Q0WeUVQqQO1rRHV=w_at_mail.gmail.com>

I think you can do this with pretty minimum pain using dbms_redefinition. It is a very nice utility, basically you create the interim table, start the redefinition, copy dependent objects, add indexes, and finish. You would probably need to use the sync option a few times, but the only lock is a very short dictionary lock.

On Thu, Jan 8, 2015 at 11:41 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> apparently bounced short of the list, trying again.
>
>
>
> *From:* Mark W. Farnham [mailto:mwf_at_rsiz.com]
> *Sent:* Thursday, January 08, 2015 12:30 PM
> *To:* 'timur.akhmadeev_at_gmail.com'; 'napacunningham_at_gmail.com'
> *Cc:* 'oracle-l_at_freelists org'
> *Subject:* RE: Adding column and updating a huge partitioned table.
>
>
>
> +42 on the virtual column idea IF Timur’s IF requirements are true.
>
>
>
> Otherwise, I would suggest that a new certain other use characteristics *
> *might** suggest that the least amount of work between now and when the
> sun explodes* might be to create this column on the existing data as a new
> table (possibly an IOT consisting of a relevant unique key matching your
> partition scheme plus the function value (ie. the new column), also
> indexed) and yoked by a union all view when needed.
>
>
>
> As you proceed eventually migrating to a “Scaling to Inifinity” scheme
> (best described, as far as I am concerned by Tim Gorman), then new time
> based partitions could swap in the existence of the new function based
> column and eliminate union all view.
>
>
>
> Notes:
>
> 1) don’t ignore my “otherwise”, if Timur’s way will work for you it is
> probably your best solution and yes you can index a virtual column ending
> up with it being tagged a function based index with similar performance
> characteristics and rules of whether the optimizer chooses it as a function
> based index as if the complexity of the virtual column was the complexity
> of the function based index on regular columns. (Based on a few relatively
> simple tests and measurements from my paper about managing transactions
> with disappearing indexes, but no tests violated that theory and it makes
> sense to me.)
>
> 2) “sun explodes” – ripped from a Baron Schwartz tweet via Cary Millsap,
> which should become common parlance: “If a query will not complete before
> the sun explodes, it can be described as thermodynamically infeasible.” (Of
> course hardware and software changes might make a formerly infeasible query
> feasible before the sun does explode. I think I’ve seen some queries where
> we’d need a new and smaller chronon or a faster speed of light.)
>
> 3) I intentionally implied you’ll be moving toward a “scaling to infinity”
> scheme. I’ll take odds on that if we meet at Collaborate in Las Vegas.
> #C15LV
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>] *On
> Behalf Of *Timur Akhmadeev
> *Sent:* Thursday, January 08, 2015 11:20 AM
> *To:* napacunningham_at_gmail.com
> *Cc:* oracle-l_at_freelists org
> *Subject:* Re: Adding column and updating a huge partitioned table.
>
>
>
> If you don't need to update this column after initial calculation, and the
> function is deterministic (preferably) you may try adding it as a virtual
> column.
>
> On Thursday, January 8, 2015, Michael Cunningham <napacunningham_at_gmail.com>
> wrote:
>
> It sounds crazy to me, but I thought I'd give it a try.
>
>
>
> I have a table that is over 1.2 TB with 64 hash partitions.
>
>
>
> If you have a great method for this I'd love to hear it. Oracle 12.1.0.1
> enterprise on Linux 6.5 with SSD storage.
>
>
>
> I need to add a column and populate the column with data from a function.
> It's a data warehouse so I can prevent DML during this time (one partition
> at a time), but cannot prevent during DML during the update of all 64
> partitions at once.
>
>
>
> I also need an index on this new column.
>
>
>
> I sure would appreciate all the input I can get on this topic because I
> feel like I'm missing something and there must be a better way of doing
> this.
>
>
>
> During a test it took be 6 hours to update the data in a single partition
> in a test environment. That comes out to around 700 hours for all 64
> partitions. Please help.
>
>
>
> --
>
> Michael Cunningham
>
>
>
> --
> Regards
> Timur Akhmadeev
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 08 2015 - 22:15:51 CET