Re: RAC partitioning question
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 31 Jan 2012 11:40:41 -0700
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@xxxxxxxxxxxxxxxxx
>> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> On Behalf Of Greg Rahn
>> Sent: Tuesday, January 31, 2012 10:25 AM
>> To: tim@xxxxxxxxx
>> Cc: oracle-l@xxxxxxxxxxxxx
>> 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@xxxxxxxxx> 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>
>>
>>
>> --
>> //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@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
