dynamic extent allocation with an update on a partitioned table

From: Michael Norbert <michaelnorbert_at_hotmail.com>
Date: 6 Nov 2002 08:03:14 -0800
Message-ID: <c2880966.0211060803.bedfc6f@posting.google.com>

Hello List

I have a partitioned table(partitioned by week) since 2000. It has 294 columns, the last 10 columns added recently are null(date). Each partition is about ~600 Meg in size. Partitions from 2000 to April of this year had the 10 most recently added columns as null. Partitions after this date have the data.
I have to update the columns for this table. The update is based on a concat of a few number,char columns in the table. I am running into massive extent allocations for the updated partitions. They go from 600 Meg to 3 Gig for each partition. I know if I export and reimport the partition it will go back to ~600 Meg.
Here is the last few columns of my create table statement that are being updated and here is my update statement. I'm just updating partition by partition as letting it all go parallel was too hard to administor once I started having problems.

Thanks
Mike
michaelnorbert_at_hotmail.com

    FLAGSTUD CHAR (1),

   282    TS_OIMF   DATE,
   283    TS_OIPL   DATE,
   284    TS_OISW   DATE,
   285    TS_OITR   DATE,
   286    TS_OIVM   DATE,
   287    TS_OIWF   DATE,
   288    TS_OOMF   DATE,
   289    TS_OOPL   DATE,
   290    TS_OOSW   DATE,
   291    TS_OOTR   DATE,
   292    TS_OOVM   DATE,
   293    TS_OOWF   DATE,
   294    TS_SODB   DATE,

   295 DATE_APP DATE    FOR c_record IN (SELECT distinct datoload

                    from voice.voice_order
                    where datoload < to_date('01-APR-02')
                    and datoload >= to_date('13-FEB-00')
                    order by datoload) LOOP

   update VOICE.VOICE_ORDER set TS_OIMF = to_date(concat(concat(to_char(DATEOIMF, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOIMF / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOIMF is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OIPL = to_date(concat(concat(to_char(DATEOIPL, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOIPL / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOIPL is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OISW = to_date(concat(concat(to_char(DATEOISW, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOISW / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOISW is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OITR = to_date(concat(concat(to_char(DATEOITR, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOITR / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOITR is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OIVM = to_date(concat(concat(to_char(DATEOIVM, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOIVM / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOIVM is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OIWF = to_date(concat(concat(to_char(DATEOIWF, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOIWF / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOIWF is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOMF = to_date(concat(concat(to_char(DATEOOMF, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOMF / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOMF is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOPL = to_date(concat(concat(to_char(DATEOOPL, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOPL / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOPL is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOSW = to_date(concat(concat(to_char(DATEOOSW, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOSW / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOSW is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOTR = to_date(concat(concat(to_char(DATEOOTR, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOTR / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOTR is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOVM = to_date(concat(concat(to_char(DATEOOVM, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOVM / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOVM is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_OOWF = to_date(concat(concat(to_char(DATEOOWF, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMEOOWF / 1, 0))), 'yyyy-mm-dd:sssss') where (DATEOOWF is not NULL) and (datoload = c_record.datoload);

   commit;
   update VOICE.VOICE_ORDER set TS_SODB = to_date(concat(concat(to_char(DATESODB, 'yyyy-mm-dd'), ':'), to_char(trunc(TIMESODB / 1, 0))), 'yyyy-mm-dd:sssss') where (DATESODB is not NULL) and (datoload = c_record.datoload);

   commit;
   END LOOP;
   commit;
END;
/ Received on Wed Nov 06 2002 - 10:03:14 CST