RE: RAC partitioning question
Date: Mon, 6 Feb 2012 17:37:22 +0000
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;
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.
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
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.
- 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
> 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?
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-lReceived on Mon Feb 06 2012 - 11:37:22 CST