Re: performance strategies for many joins

From: Brian E Dick <bdick_at_cox.net>
Date: Tue, 04 Feb 2003 19:35:37 GMT
Message-ID: <dCU%9.100674$GX4.4388090@news2.east.cox.net>

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E3B0635.A8F41038_at_exesolutions.com...
> In tests run with both 8i and 9i a straight inner join had a cost at least
2X
> greater than the second SQL statement.
>
> Given my intro I would expect everyone to make the correct choice. But
shown to
> them blind ... I would expect everyone to write the simple inner join.
(well
> almost everyone).

I considered this a "here's the answer, explain why" type of problem.

Did your test results vary depending of the absolute and relative size of the tables? Were there cases where the inner join out-performed the SELECT...NOT IN (...) version? What role does the DISTINCT play in this un-intuitive problem?

Even given the performance advantage of the more complex solution, I would be wary of the maintenance cost of such a solution.

Later,
BEDick Received on Tue Feb 04 2003 - 13:35:37 CST