I wrote an article explaining the new 11g (and enhanced 12c for not null columns) DDL optimization feature http://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html That I wished it could be extended to deterministic functions create table t_ddl as select rownum n1 , trunc ((rownum-1)/3) n2 , trunc(dbms_random.value(rownum, rownum*10)) n3 , dbms_random.string('U', 10) c1 from dual connect by level <= 1e4; SQL> alter table t_ddl add C_DDL number default 42 not null; Table altered. create or replace function f1_ddl return number deterministic is begin return 42; end f1_ddl; / SQL> alter table t_ddl add C_DDL_2 number default f1_ddl() not null; alter table t_ddl add C_DDL_2 number default f1_ddl() not null * ERROR at line 1: ORA-04044: procedure, function, package, or type is not allowed here Best regards Mohamed Houri www.hourim.wordpress.com 2015-01-09 2:08 GMT+01:00 Kenny Payton <k3nnyp@xxxxxxxxx>: > 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> wrote: > >> apparently bounced short of the list, trying again. >> >> >> >> *From:* Mark W. Farnham [mailto:mwf@xxxxxxxx] >> *Sent:* Thursday, January 08, 2015 12:30 PM >> *To:* 'timur.akhmadeev@xxxxxxxxx'; '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 <oracle-l-bounce@xxxxxxxxxxxxx>] *On >> Behalf Of *Timur Akhmadeev >> *Sent:* Thursday, January 08, 2015 11:20 AM >> *To:* 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> 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.' > > > -- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Member of Oraworld-team <http://www.oraworld-team.com/> Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri>