Re: Designing database tables for performance?
Date: 12 Feb 2007 13:54:25 +1100
Message-ID: <87vei8axwe.fsf_at_virgo.contecint.com.au>
"Timasmith" <timasmith_at_hotmail.com> writes:
> It seems to me very difficult to optimize this, obviously we leave it
If performance is critical (and space and software maintenance aren't)
you will need to flatten the item hierarchy into something like an
item_equivalence table:
item_equivalence (item_id1 number, item_id2 number)
and add logic to generate and maintain this table. Then, given an item
id, you can write a join to the item_equivalence instead of using "in".
You should be able to use triggers to auto-maintain the
item_equivalence table. Messy but, AFAIK, doable.
> to the database and cross our fingers that the optimizer will pick the
> right query in the right situation but I cant help thinking there must
> be *something* one can do to help out. The fact the items are a
> hierarchy doesnt help - difficult to avoid the 'in' statement without
> knowing how many levels there are in the hierarchy.
m. Received on Mon Feb 12 2007 - 03:54:25 CET