RE: RAC partitioning question

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Tue, 31 Jan 2012 21:19:28 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C56262C10_at_COPDCEXMB08.cable.comcast.com>



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?

I like the idea to make the two column index and compress the start_time_in_millis; however,

This is a child table and I asked the developer if they really use the sequence based primary key column. He checked the code and said he can find nowhere where it is actually referenced, sheesh. That is good though, as it means we can probably remove the primary key (one less index) and maybe the column, and then I can partition on the other time column thus having all local indexes. That would be so nice.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Tuesday, January 31, 2012 11:41 AM To: oracle-l_at_freelists.org
Subject: Re: RAC partitioning question

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#VL
> DBG1369
>
> 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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 31 2012 - 15:19:28 CST

Original text of this message