Re: Indexing UNIONs

Bruno,

> This sounds like a design issue. This makes it seem like the events
> should be broken out into their own table and the other two tables
> should get joined with the events table when needed.
>

OK, I guess I'll have to get into detail:

Table "cases" is the database's third largest table, with 100,000 records,
plus three dependant tables and 19 attributes (fields).

Table "trial groups" is a small table listing a few dozen "cases" which are
aggregated for settlement bargaining. Thus, each "trial group" relates to
one to many "cases". Beyond this relationship, trial groups has only 5
attributes and 2 dependant tables.

Table "events", the largest table in the database, contains event schedule
listing with 11 attributes and one dependant table as well as recursive
relationships between events. Each event record can be (and Must be)
related to either one Case or one Trial Group.

Thus, I need to relate (in views and queries) each Event to the Union of Cases
and Trial Groups. I just can't figure out how to do so without the database
discarding the indexes on Cases in the process and things slowing to a crawl.

--
-Josh Berkus
Aglio Database Solutions
San Francisco