RE: RAC partitioning question

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Mon, 6 Feb 2012 17:37:22 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C562A64D6_at_COPDCEXMB08.cable.comcast.com>

Ugh, they just told me that they actually do use the PK throughout the code I'd rather not mess with that, but. I think going back to Tim's idea might be the best way to go (similar to what Jonathan suggested too).

Here is my table;
desc rdvrgw_chic.scheduled_recording;

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------- ------
 SCHEDULED_RECORDING_ID                    NOT NULL NUMBER     PK
 STB_SETTING_ID                            NOT NULL NUMBER
 VIRTUAL_CHANNEL_NUMBER                             NUMBER
 ...
 START_TIME_IN_MILLIS                               NUMBER     unix time

 ...

select max(scheduled_recording_id), max(start_time_in_millis) from rdvrgw_chic.scheduled_recording SQL> / MAX(SCHEDULED_RECORDING_ID) MAX(START_TIME_IN_MILLIS)

--------------------------- ---------------------------
                  420556165               1329886140000

So, recreate sequence with a max of 9999999999 and have it cycle (theoretically I could probably use much less precision). Then make PK ID = (start_time_in_millis*10000000000)+sequence.nextval

This should produce a unique value for PKID and allow me to partition based on the PK column and have no global indexes.

Thoughts?

Jed

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, February 01, 2012 4:41 AM To: oracle-l_at_freelists.org
Subject: 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<mailto: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




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

Received on Mon Feb 06 2012 - 11:37:22 CST