Re: Adding column and updating a huge partitioned table.

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Thu, 8 Jan 2015 20:08:38 -0500
Message-Id: <97E16A16-2EDF-420A-BF4B-3253DC1C23DB_at_gmail.com>

My first thought was dbms_redefinition as well but two things came to mind as to why I didn’t recommend it to Michael ( not that it is not feasible ).

1. it requires the duplication of the entire table which takes a bit more space but if you have it this would not be a concern.
2. Although I’ve successfully done this on some much larger tables with quite a bit of activity I’m not expert. I wasn’t for sure how you would populate the new column. Would you define a function based index on the new column ( provided the function is deterministic as earlier mentioned ) and maintain that for updates until following the finish?

Definitely a nice utility that I’ve been able to leverage on more than one occasion.

Thanks,
Kenny

> On Jan 8, 2015, at 4:15 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
>
> 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 <mailto:mwf_at_rsiz.com>> wrote:
> apparently bounced short of the list, trying again.
>
>
>
> From: Mark W. Farnham [mailto:mwf_at_rsiz.com <mailto:mwf_at_rsiz.com>]
> Sent: Thursday, January 08, 2015 12:30 PM
> To: 'timur.akhmadeev_at_gmail.com <mailto:timur.akhmadeev_at_gmail.com>'; 'napacunningham_at_gmail.com <mailto: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> [mailto:oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Timur Akhmadeev
> Sent: Thursday, January 08, 2015 11:20 AM
> To: napacunningham_at_gmail.com <mailto: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 <mailto: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 Fri Jan 09 2015 - 02:08:38 CET