Re: alter table split partition optimized in oracle 9i?

Hi Gene,

> I have also been running this kind of a split when an existing partition
> is
> split in two and one (or even both) partition
> is empty and this command does not seem to run too quickly (I am using
> oracle 10.2.0.1)

Is the partition empty and the HWM reset? There will be in some cases a serial scan of the partition with rownum < 2 - simply to verify the partition is really empty. You may check this quickly using 10046 trace on level 8. This can give a clue where the problem is also in other cases (reorg or allocation task maybe)

Regards,

Jaromir
----- Original Message -----
From: <genegurevich_at_discover.com>
To: "'oracle-l'" <oracle-l_at_freelists.org> Sent: Wednesday, December 26, 2007 10:24 PM Subject: alter table split partition optimized in oracle 9i?

> Hello Everybody:
>
> I have been reading a part in the Oracle 9i admin's guide stating that
> oracle is now optimizing the split partition and split subpartition
> commands if one of the new partitions ends up being empty (and some other
> conditions are satisfied -
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96521.pdf page
> 588). Someone from my team however
> had to go through a split like that which took about 30 hours.
>
> I have also been running this kind of a split when an existing partition
> is
> split in two and one (or even both) partition
> is empty and this command does not seem to run too quickly (I am using
> oracle 10.2.0.1)
>
> alter table cfr01.APR_DTL split partition APR_DTL_MAXVALUE
> AT ( 2009, 02 )
> INTO ( PARTITION YR_2009_MO_01, PARTITION APR_DTL_MAXVALUE
> );
>
> Table altered.
>
> Elapsed: 00:03:35.75
>
> What I do here is taking an empty partition and split it into two other
> empty partitions. I take all the default storage
> parameters so I think this command should qualify for an optimized split
> operation yet I don't think this is the case.
> I don't see why this should have taken 3.3 min to split an empty partition
> into two.
>
> Does anyone have any insight into that?
>
> thank you
>
> Gene Gurevich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l