[Info-Ingres] Automatic partitioninng and unique keyed tables

[martin.bowes at ctsu.ox.ac.uk]

> >Why is it that I cannot put an automatic partition on a  unique keyed
> >table?
> 
> Ooh!  Ooh!  I know!  :)

Steady there. The last time I saw someone that excited it was my 
Missus looking at Roy's new car. And I suspect Osama Bin Redhead 
has further schemes in that area...Thanks Roy.

> >
> >As I understand it, the automatic partition is just there to scatter
> >the data randomly amongst the partitions. So to retrieve data it must
> >go and look in each partition. Surely uniqueness would actually help
> >not hinder that task?
> 
> That is precisely why one can't use AUTOMATIC and unique together: DMF
> would have to probe all partitions for each insert to verify the key
> uniqueness.  It's actually harder than probing each partition for the
> key when doing a lookup or k-join;  QEF would have to drive it
> somehow, doing X-locking probes to verify uniqueness, and then one
> real insert into the proper partition (whichever that is).

Bugger! I just knew it was something simple.
> 
> The restriction doesn't just apply to AUTOMATIC.  The rule is that the
> partitioning has to be the same as the keying (or a subset of it, in
> the case of RANGE or LIST);  this allows us to ensure that rows
> potentially containing that key must necessarily be in some one known
> partition, avoiding the need to probe upon insert.

Sweet!

I suspect then that in this case as uniqueness has to be preserved in 
the table that a hash partition is probably going to be the easiest option 
to go with.

Marty
--
Random Farscape Quote #6:
Ka'Dargo - You have no idea where we are.
Pilot    - Of course I do... We are someplace else, I'll get back to you 
when more details become apparent.