Re: RAC partitioning question

In fact the two-column index may be significantly more space efficient.

Given the average 12 rows per second (1M rows per day, 86,400 seconds per day (except for one day later on this year)) it would be sensible to compress on the first column of the two column index, storing (on average) one copy of each date value per block instead of about 12. The benefit is harder to assess if the 1M rows is actually 9 batches of 100,000 each plus one row per second, of course.

You can't do any compression on the concatenated thing, and the 20 digit number imposed by the structure is pretty fixed in its storage, while you might choose to make the numeric bit of a two column key a cyclic sequence limited to (say) 20,000 - or some smaller, but very safe, limit on the number of rows arriving per second - keeping that part of the index to the smallest possible size.

One problem - if the values are imposed from the front end - how do you guarantee to avoid timing differences between client machines, and how do you avoid sequence clashes ? The key values really ought to be generated (with a "returning" clause) by the database.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Greg Rahn" <greg_at_structureddata.org> To: "Walker, Jed S" <Jed_Walker_at_cable.comcast.com> Cc: <tim_at_evdbt.com>; <oracle-l_at_freelists.org> Sent: Tuesday, January 31, 2012 6:16 PM Subject: Re: RAC partitioning question

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




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1901 / Virus Database: 2109/4777 - Release Date: 01/30/12


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