두 열의 좌측 외측 접합 성능 문제

두 열의 좌측 외측 접합 성능 문제

저는 다음과 같은 형태의 SQL 쿼리를 사용하고 있습니다.

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
AND table1.period = table2.period

그리고 돌아오는 데 최소 4분이 걸리기 때문에 너무 느리거나 뭔가 교착상태에 빠져 있습니다.이렇게 변경하면 다음과 같습니다.

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table1.period = table2.period

그러면 잘 작동합니다(albeit 올바른 열의 수를 반환하지 않음).속도를 높일 방법이 없을까요?

업데이트: 후자 쿼리의 마지막 두 줄을 바꾸면 동일한 동작을 수행합니다.

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.period = table2.period
WHERE table1.person_uid = table2.person_uid

업데이트 2: 이것들은 제가 실제로 참여하고 있는 뷰들입니다.유감스럽게도 이들은 제가 통제할 수 없는 데이터베이스에 있기 때문에 색인을 변경할 수 없습니다.나는 이것이 색인 문제라는 것에 동의하는 경향이 있습니다.제가 모르는 이 쿼리를 조정할 수 있는 마법 같은 방법이 있을 경우를 대비하여 답변을 수락하기 전에 잠시 기다리겠습니다.그렇지 않으면, 저는 현재의 답 중 하나를 받아들이고 제가 하고 싶은 일을 할 다른 방법을 찾으려고 노력할 것입니다.지금까지 도와주신 모든 분들께 감사드립니다.

문 2와 문 3이 첫 번째 문과 다르다는 것을 명심하세요.

어떻게? 음, 당신은 왼쪽 외부 조인을 하고 있는데 WHERE 조항은 (ON 조항처럼) 그것을 고려하지 않고 있습니다.최소한 다음을 시도합니다.

SELECT col1, col2
FROM table1, table2
WHERE table1.person_uid = table2.person_uid (+)
AND table1.period = table2.period (+)

동일한 성능 문제가 발생하는지 확인할 수 있습니다.

이 표들에는 어떤 색인들이 있습니까?이 관계는 외국의 키 제약에 의해 정의됩니까?

person_uid 및 period(두 표 모두)에 대한 합성 지수가 필요할 수 있습니다.

마지막 두 개가 첫 번째 질문과 같은 질문이 아닌 이유를 이해해야 할 것 같습니다.왼쪽 조인을 수행한 다음 조인의 오른쪽에 있는 테이블의 필드를 참조하는 where 절을 추가하면 조인을 내부 조인으로 효과적으로 변경한 것입니다.이것에는 한 가지 예외가 있는데 그것은 만약 당신이 다음과 같은 것을 언급한다면 입니다.

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table2.person_uid is null

이 경우 두 번째 표에 기록이 없는 기록을 요청합니다.그러나 이 특수한 경우를 제외하고 where 절의 표 2의 필드를 참조하면 왼쪽 조인을 안쪽 조인으로 변경하는 것입니다.

쿼리가 빠르지 않으면 인덱싱을 살펴보겠습니다.

당신이 제공한 정보를 바탕으로 누구든지 당신에게 말하는 것은 무엇이든 추측입니다.

쿼리에 대한 실행 계획을 살펴봅니다.계획이 늦어지는 이유가 보이지 않으면 여기에 계획을 게시합니다.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

에 대한 ?person_uid그리고.period두 테이블 모두요?

그렇지 않은 경우 추가한 후 다시 시도합니다.

실행 계획을 살펴보고 쿼리가 실제로 어떤 작업을 수행하고 있는지 확인합니다.

또한: 필드의 데이터 유형은 무엇입니까?그것들은 두 테이블 모두 같습니까?암시적인 캐스팅은 정말로 일을 늦출 수 있습니다.

이 표들에 가입하려는 열에 색인이 있습니까?Oracle의 무료 SQL Developer 제품을 설치하고 이 제품을 사용하여 해당 쿼리에 대한 "설명"을 수행하고 두 테이블에 대한 순차적인 검색을 수행하는지 확인합니다.

왼쪽 조인에서 테이블 1을 검색하여 (person_uid, period)의 각 고유 조합에 대해 검색한 다음 테이블 2에서 해당하는 모든 레코드를 검색합니다.table2에 적절한 인덱스가 없는 경우, 이는 해당 table 전체를 스캔하는 것을 수반할 수 있습니다.

실행 계획을 보지 않고 가장 좋은 추측은 첫 번째 쿼리(정확한 것으로 보이는 유일한 쿼리)가 테이블 1 뿐만 아니라 테이블 2를 스캔해야 하는 것입니다.

인덱스를 변경할 수 없다고 하니 쿼리를 변경해야 합니다.내가 아는 한, 현실적인 대안은 단 한가지...

SELECT
   col1, col2
FROM
   table2
FULL OUTER JOIN
   table1
      ON table1.person_uid = table2.person_uid
      AND table1.period = table2.period
WHERE
   table1.person_uid IS NOT NULL

여기서 희망하는 것은 표 2를 (person_uid, period)의 각 고유한 조합에 대해 스캔하되 표 1의 인덱스를 사용하는 것입니다.(Table1을 스캔하고 table2의 인덱스를 사용하는 것과는 반대로, 당신의 쿼리에서 기대했던 것입니다.)

그러나 table1에 적절한 인덱스가 없는 경우 성능이 전혀 개선되지 않을 가능성이 매우 높습니다.

민주당원들.

업데이트 중 하나에서 OP는 실제로 테이블이 아닌 뷰를 쿼리하고 있다고 말합니다.이 경우, 특히 뷰가 복잡하여 필요한 정보가 포함되지 않은 다른 많은 테이블에 가입하거나 뷰를 호출하는 뷰인 경우 필요한 테이블을 직접 쿼리하여 성능을 높일 수 있습니다.

ANSI join 구문은 JOIN 조건과 FILTER 술어를 매우 명확하게 구분합니다. 이는 외부 join을 쓸 때 매우 중요합니다.emp/dept 표를 사용하여 다음 두 개의 외부 접합부의 결과를 살펴봅니다.

Q1

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
and loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
RESEARCH               20                       DALLAS
SALES                  30                       CHICAGO
OPERATIONS             40                       BOSTON

====

Q2
SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
where loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
OPERATIONS             40                       BOSTON

첫 번째 예는 Q1이 보여주는 "상수에 결합"의 예입니다.기본적으로 필터 조건은 외부 접합을 수행하기 전에 적용됩니다.따라서 행을 제거하고 나중에 외부 결합의 일부로 다시 추가됩니다.꼭 틀린 것은 아니지만, 그게 당신이 정말로 요청한 질문인가요?흔히 Q2에 표시된 결과가 필요하며, 여기서 필터는 (외부) 결합 후에 적용됩니다.

대용량 데이터 세트의 경우에도 성능에 영향을 미칩니다.많은 경우 상수에 대한 결합은 측면 뷰를 생성하여 옵티마이저에 의해 내부적으로 해결되어야 합니다. 이 뷰는 일반적으로 해시 조인이 아닌 중첩 루프 조인을 통해서만 최적화될 수 있습니다.

Oracle 외부 조인 구문에 익숙한 개발자의 경우 쿼리는 다음과 같이 작성되었을 수 있습니다.

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
        ,emp e
where  d.deptno = e.deptno(+)
and loc in ('NEW YORK','BOSTON' )

이 쿼리는 위의 Q2와 의미론적으로 동등합니다.

따라서 요약하자면, ANSI outer join을 작성할 때 JOIN 절과 WHERE 절의 차이점을 이해하는 것이 매우 중요합니다.

언급URL : https://stackoverflow.com/questions/444820/left-outer-join-on-two-columns-performance-issue