Re: RAC partitioning question
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Feb 2012 11:40:37 -0000
Marcin,
I've had a look at the blog item, and don't think it's really making a
point that's restricted to partitioning.
Any time you have mechanisms that only ever add data at the right hand side
of an index, and then have bulk deletes scattered randomly through the
entire length of the index, the utilisation of the average leaf block is
bound to drop slowly over a number of delete cycles. This will happen
whether or not you're talking about partitioning.
On the plus side, though, if you have already licensed the partitioning
option, you could create the index as a hash-partitioned index, which would
make it much easier to do index maintenance when you thought an index had
got to a point where the older blocks had more free space than you liked,
because you can rebuild each partition online separately. (Unfortunately
you can't coalesce individual partitions of a hash-partitioned index -- or
maybe you can, but the coalesce does something completely different in the
context of hash partitioning).
I wrote a script a couple of years ago to draw a "picture" of what the
index usage looked like - it might be quite entertaining to repeat your
demo and draw the index after each delete cycle.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: "Marcin Przepiorowski" <pioro1@xxxxxxxxx>
To: <Jed_Walker@xxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 01, 2012 9:15 AM
Subject: Re: RAC partitioning question
On Tue, Jan 31, 2012 at 9:19 PM, Walker, Jed S
<Jed_Walker@xxxxxxxxxxxxxxxxx> wrote:
> Thanks again everyone for the ideas. I tried to push for changing the
> column but the application uses that instead of timestamp, well, I won't
> get into it, bad design is bad design eh?
Hi,
One more remark about global indexes and partition drop - yet it is
possible online but you will "pay" for it. When Oracle is busy with
dropping your partition and updating global index and application is
doing inserts your index will growing faster during that activity.
Next thing to remember is that Oracle is reading index block related
to every dropped row in partition so dropping partition is not a cost
less operation anymore.
After partition drop you should add index maintenance task to your
schedule - coalesce is fine for most of cases but if you are dropping
lot of rows you can end up with index rebuild as well. I create simple
test case here -
http://oracleprof.blogspot.com/2011/03/rolling-partition-and-global-index.html
. In real life I got a index which was 10 x bigger that is should
after 1 year of dropping daily partitions.
--
Marcin Przepiorowski
http://oracleprof.blogspot.com
--
--
//www.freelists.org/webpage/oracle-l
Other related posts:
