Re: Adding column and updating a huge partitioned table.
- From: Kenny Payton <k3nnyp@xxxxxxxxx>
- To: andrew.kerber@xxxxxxxxx
- Date: Thu, 8 Jan 2015 20:08:38 -0500
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@xxxxxxxxx> 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@xxxxxxxx
> <mailto:mwf@xxxxxxxx>> wrote:
> apparently bounced short of the list, trying again.
>
>
>
> From: Mark W. Farnham [mailto:mwf@xxxxxxxx <mailto:mwf@xxxxxxxx>]
> Sent: Thursday, January 08, 2015 12:30 PM
> To: 'timur.akhmadeev@xxxxxxxxx <mailto:timur.akhmadeev@xxxxxxxxx>';
> 'napacunningham@xxxxxxxxx <mailto:napacunningham@xxxxxxxxx>'
> Cc: 'oracle-l@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@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>]
> On Behalf Of Timur Akhmadeev
> Sent: Thursday, January 08, 2015 11:20 AM
> To: napacunningham@xxxxxxxxx <mailto:napacunningham@xxxxxxxxx>
> Cc: oracle-l@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@xxxxxxxxx
> <mailto:napacunningham@xxxxxxxxx>> 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.'
Other related posts:
