Re: RAC partitioning question

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_at_gmail.com> To: <Jed_Walker_at_cable.comcast.com> Cc: <oracle-l_at_freelists.org> 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_at_cable.comcast.com> 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
--

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