RE: RAC partitioning question
- From: "Walker, Jed S" <Jed_Walker@xxxxxxxxxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>
- 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;
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jonathan Lewis
Sent: Wednesday, February 01, 2012 4:41 AM
To: oracle-l@xxxxxxxxxxxxx
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@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<mailto: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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
