Re: Arrays and foreign keys

On Fri, 11 Aug 2000, Chris Bitmead wrote:

> Stephan Szabo wrote:
> 
> > Remember, his structure was the array referenced the integer, not the
> > other way around.  So, if you say, delete one of the integers from the
> > referenced table you need to find any array element that referenced that
> > integer in all rows of the referencing table, that's the slow part.
> 
> Ah yes. I guess that's a problem crying out for a new indexing solution.

Yeah, and it would probably need some associated cost estimation stuff,
since you'd need to know something about the element value rarity
instead of the array value rarity if you wanted to make intelligent guesses
as to whether the index scan is better than the sequential scan.

You could kind of store the information in a secondary relation, but that
seems like a major point of locking contention, plus it'd either end up
being the reverse index (element->array of oids) or the normalized,
element->oid rows at which point are you better off than if you
normalized the original relation.

Does any version of SQL have meaningful arrays, and do they actually
specify any behavior for this?  Or for that matter, what about other
dbs.  What do they do with these cases...