본문 바로가기
TechNical/Oracle

LEFT OUTER JOIN 성능. 꼭 필요할 떄만 쓰자~

by 강멍멍이 2010. 3. 17.
반응형

LEFT OUTER JOIN을 동일한 테이블을 조건만 다르게 해서 여러게 붙여 놓은 쿼리가 있었다.
속도가 겁니 느려서 분산된 LEFT OUTER JOIN 을 하나로 빼고 해당 테이블에서 다른 조건들은
SELECT 문에서 DECODE나 CASE문으로 빼니까 성능이 좋아진 경우가 있었다.
플렌을 떴을 경우 별 차이는 없었지만 실제 실행 속도는 차이가 많았다.
이에 따라 LEFT OUTER JOIN 의 성능이 어떤가 하고 찾아보니 아래와 같은 TIP을 발견 했다.

If you have a query that uses a LEFT OUTER JOIN, check it carefully to be sure that is the type of join you
really want to use. As you may know, a LEFT OUTER JOIN is used to create a result set that includes all of the
rows from the left table specified in the clause, not just the ones in which the joined columns match. In
addition, when a row in the left table has no matching rows in the right table, the result set row contains NULL
values for all the selected columns coming from the right table. If this is what you want, then use this type of
join.

The problem is that in the real world, a LEFT OUTER JOIN is rarely needed, and many developers use them by
mistake. While you may end up with the data you want, you may also end up with more than the data you want,
which contributes to unnecessary overhead and poor performance. Because of this, always closely examine
why you are using a LEFT OUTER JOIN in your queries, and only use them if they are exactly what you need.
Otherwise, use a JOIN that is more appropriate to your needs. [6.5, 7.0, 2000] Updated 8-21-2006


http://www.sql-server-performance.com/tips/tuning_joins_p3.aspx

여기서 땜쳐 왔음 ㅋ
유용한 정보를 상당히 간략하게 정리해 놨네요. 좋은 정보가 많을 듯 !!

반응형

댓글