I agree about the need for rebuilding. Your approach otherwise seems valid to me. That said, if disk space is not a problem, can we assume safely that rollback space is no problem either? In which case I would be quite tempted by taking a deep breath and try to do it as a single 'Create table as select'. Looks to me like your additional column might be computed with analytical functions.
>----- ------- Original Message ------- ----- >From: Mladen Gogala <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Sun, 15 Jun 2003 23:14:18 > >You should also rebuild the table afterwards >because after adding a new >column, you'll have plenty of chained rows which >are, as you are probably >aware, very bad thing indeed for application >performance. >Rebuild is done by using "alter table move". > > >On 2003.06.16 02:24, Mark Richard wrote: >> >> Hi List, >> >> I have been asked to add a column to a table and >populate it's contents. >> Conceptually this is very easy but I'm concerned >from a performance point >> of view. Let me explain: >> >> * The table currently has 160,000,000 rows in it, >taking up ~37 GB (~370 x >> 100 MB extents). >> * The rule for populating the column depends on >reading a variable number >> of records from the same table and assigning a >sequence number based on the >> contents of a date field for each group of >fields. An analogy would be a >> student attendance table - each student has a >record for each day they >> attended classes and I need to effectively number >these fields. My table >> holds the equivalent of 1,500,000 "students" >although the number of records >> per "student" can vary significantly from1to >perhaps several thousand. >> * The column, once populated should be defined as >NOT NULL >> >> I'm currently thinking of doing the following: >> 1) Adding column as a nullable column to >existing table >> 2) Creating a table which has a unique list of >"students" >> 3) Process perhaps 1000-10000 students at a time >to populate the new field >> (keeping rollback at around 1 million records per >iteration). There is a >> concatenated index on "student" and "date" which >I am hoping to (ab)use >> although I can't think of an easy way to do the >update without selecting to >> a temporary table. >> 4) Modify the column to not-null status >> >> I'm really looking for advice on whether this >approach will work, and if >> not what alternative approaches might work. >Since the update seems tricky >> I'm thinking that renaming the existing table and >creating a new table >> using select might be an easier approach. >Diskspace for a copy of the >> table shouldn't be an issue at all and I figure >this might effectively >> provide a rebuild of the table and it's indexes, >which probably isn't a bad >> thing. Also, I have a sneaking suspician that >adding the not null clause >> requires a full table scan to validate the data - >is this true? A full >> table scan of this table takes around 30-45 >minutes (although I will has >> exclusive access to the server during this change >and could probably go for >> a parallel scan to save a few minutes). >> >> All help is appreciated. >> >> Regards, >> Mark. >> >> >> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---- >> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >> Privileged/Confidential information may be >contained in this message. >> If you are not the addressee indicated >in this message >> (or responsible for delivery of the >message to such person), >> you may not copy or deliver this >message to anyone. >> In such case, you should destroy this message and >kindly notify the sender >> by reply e-mail or by telephone on (61 >3) 9612-6999. >> Please advise immediately if you or your >employer does not consent to >> Internet e-mail for messages of >this kind. >> Opinions, conclusions and other >information in this message >> that do not relate to the official >business of >> Transurban City Link Ltd > >> shall be understood as neither given nor >endorsed by it. >> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---- >> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >> >> >> -- >> Please see the official ORACLE-L FAQ: >http://www.orafaq.net >> -- >> Author: Mark Richard >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services -- 858-538-5051 >http://www.fatcity.com >> San Diego, California -- Mailing list and >web hosting services >> >> To REMOVE yourself from this mailing list, send >an E-Mail message >> to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >> the message BODY, include a line containing: >UNSUB ORACLE-L >> (or the name of mailing list you want to be >removed from). You may >> also send the HELP command for other information >(like subscribing). >> > >-- >Mladen Gogala >Oracle DBA >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.net >-- >Author: Mladen Gogala > INET: [EMAIL PROTECTED] > >Fat City Network Services -- 858-538-5051 >http://www.fatcity.com >San Diego, California -- Mailing list and >web hosting services >To REMOVE yourself from this mailing list, send an >E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >the message BODY, include a line containing: UNSUB >ORACLE-L >(or the name of mailing list you want to be removed >from). You may >also send the HELP command for other information >(like subscribing). >--------------------------------------------------- >------------------ >--------------------------------------------------- >------------------ Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
