Re: RAC partitioning question

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 31 Jan 2012 11:40:41 -0700
Message-ID: <4F2835A9.1080901_at_evdbt.com>

Agreed, and grateful for the generous application of common sense, Greg.

The only thing I'd add is to get rid of the NUMBER column representing a date/time and use a DATE or TIMESTAMP datatype. Can't say how many times the road to hell has been paved with unnecessary datatype conversions. No upside and plenty of downside.

On 1/31/2012 11:16 AM, Greg Rahn wrote:
> 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
>>
>>
>>
>

-- 
Tim Gorman
consultant ->  Evergreen Database Technologies, Inc.
postal     =>  PO Box 352151, Westminster CO 80035
website    =>  http://www.EvDBT.com/
email      =>  Tim_at_EvDBT.com
mobile     =>  +1-303-885-4526
fax        =>  +1-303-484-3608
Lost Data? =>  http://www.ora600.be/ for info about DUDE...

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

Received on Tue Jan 31 2012 - 12:40:41 CST