Re: RAC partitioning question

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 31 Jan 2012 10:16:49 -0800
Message-ID: <CAGXkmiu6H0R-ZZHt4_rV551pi-pstQLj-C+Nz7-BrZy5HZDESg_at_mail.gmail.com>

The restriction on unique partitioned indexes is such:

• Unique global partitioned indexes must always be prefixed with the partitioning columns.
• Unique local indexes must have the partitioning key of the table as a subset of the unique key definition.

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_oltp.htm#VLDBG1369

My point is that there really is no difference between 1 column (timestamp || sequence) or 2 columns (timestamp, sequence) for a PK.

On Tue, Jan 31, 2012 at 9:53 AM, Walker, Jed S <Jed_Walker_at_cable.comcast.com
> wrote:

> Greg,
>
> I was just thinking about that and made some notes. My only thought (would
> need to test) is whether I could still have a unique index on just the old
> PK column without it having to be global. (I'll update on that)
>
> For example:
> Pk_id number
> Start_time number
> Pk on (start_time, pk_id)
> Unique index on (pk_id)
>
>
> Also, on Tim's point, I guess I could also make the old PK smaller since
> it could wrap now that is appended to the start_time_in_millis columns. The
> chance of rolling through, say 10000, sequence values within a second is
> almost nill.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Greg Rahn
> Sent: Tuesday, January 31, 2012 10:25 AM
> To: tim_at_evdbt.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: RAC partitioning question
>
> Any reason not to just add the date/timestamp column into the PK and make
> it two columns? This would result in allowing the index to be local (the
> date col is the partition key col) without any modification to the current
> table definition.
> On Tue, Jan 31, 2012 at 8:42 AM, Tim Gorman <tim_at_evdbt.com> wrote:
>
> > Jed,
> > Why not get rid of the sequence-generated PK column and instead make
> > another NUMBER column the PK, itself generated both from the needed
> > timestamp appended to a sequence generated data value to ensure it's
> > uniqueness? If you have that, then you can RANGE partition on that
> > NUMBER value according to your data manipulation requirements and also
> > have a LOCAL partitioned index so that you have no GLOBAL index issues.
> >
> >
> --
> Regards,
> Greg Rahn | blog <http://bit.ly/u9N0i8> | twitter <
> http://bit.ly/v733dJ> | linkedin <http://linkd.in/gregrahn>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,
Greg Rahn  |  blog <http://bit.ly/u9N0i8>  |  twitter <http://bit.ly/v733dJ>  |
 linkedin <http://linkd.in/gregrahn>


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

Received on Tue Jan 31 2012 - 12:16:49 CST