Re: Optomizing left outer joins

Lorraine,

> Perhaps I should have said that not only is the column optional, the entire
> row is optional. I am trying to join a master table (contains insurance
claim
> information) to an error table. If the claim in the master table doesn't
have
> any errors, there won't be a row for it in the error table. When I select
> columns from both tables, I only get the errored claims. That's why I was
> trying to use the outer join.

Well, an outer join is appropriate in that case. However, a single outer
join shouldn't be killing your queries -- in the example I gave, I eliminated
19 outer joins to boost the client's performance.

I think the lack of indexes is your real albatross.

> I like your advice about telling the customer about the workaround. I'll
> definitely do that! The workaround is pretty awkward: two select statements
> joined by "union all," then some subselects to pick out the unmatched
records.
> It returns the correct data and it's faster than the left join, but it
takes
> a lot more code. Anyway, I guess I'm stuck with it.

You probably are. But make sure you tell the client that the lack of indexes
is killing performance; even if it doesn't get fixed, your keister is
covered.

--
-Josh Berkus
Aglio Database Solutions
San Francisco