RE: Re: Opinions of high-volume DDL and data population

Mark - Exclusive access? Man, do you live right. Of course this probably means a weekend. One advantage of the CTAS is the opportunity to turn off logging. Also, leaving the existing table in-situ would be a nice fallback, a great point with a 37-gig. table. If you are licensed for it, this table is a great candidate for partitioning. The points about chaining are excellent. If you decide to add the column to the current table, there is a way to identify chained (technically migrated) rows and fix them by just moving those rows.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] -----Original Message-----
Sent: Monday, June 16, 2003 4:45 AM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  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).