Re: alter table split partition optimized in oracle 9i?
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 27 Dec 2007 13:27:22 +0100
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@xxxxxxxxxxxx>
To: "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
