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

Dennis, Stephane, Mladen, Raj and Peter,

Thankyou all for your comments. I was also a little concerned about chaining and that's why I figured that renaming the existing table and creating a new one might be a sensible approach. It simplifies the query (SELECT's offer so much more flexiblity than UPDATES), avoids chaining, provides a backout path (not likely to be used, but nice to have) and even effectively throws in an index rebuild.

Exclusive will be a Friday night probably. The system is connected to a tollway and cars never stop driving on the road but the roadside can backlog messages which provides our opportunity for outages. Having said that, it can be difficult to recover from a backlog so there is pressure to make every outage as short as possible.

Unfortunately there wouldn't be a lot of benefit in partitioning. Being an OLTP system most lookups are a single record and virtually always via index - everything is just fine until they ask to add a column and populate it historically! I imagine partitioning could simplifying our archiving / purging process but I haven't had a chance to look properly at that yet.

I will look at the approach of renaming and replacing the table in detail - You have all confirmed my suspicion that this is the way to go.

Regards,

      Mark

                                                                                       
                                               
                      DENNIS WILLIAMS                                                  
                                               
                      <[EMAIL PROTECTED]        To:       Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>                  
                      UCH.COM>                 cc:                                     
                                               
                      Sent by:                 Subject:  RE: Re: Opinions of 
high-volume DDL and data population                      
                      [EMAIL PROTECTED]                                                
                                               
                      .com                                                             
                                               
                                                                                       
                                               
                                                                                       
                                               
                      16/06/2003 23:55                                                 
                                               
                      Please respond to                                                
                                               
                      ORACLE-L                                                         
                                               
                                                                                       
                                               
                                                                                       
                                               

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).



<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   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).