Re: Indexing UNIONs

On Tue, Jul 16, 2002 at 15:42:23 -0700,
Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> 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.

I think you might be able to do this using (one sided) outer joins of the event
table to the Case and Trial Group tables. The join rules will need to work for
exactly one of the two tables. You probably will want to use case statements in
the select list to pick values from the right table.