-
질문 & 답변
카테고리
-
세부 분야
데이터베이스
-
해결 여부
해결됨
Non Equi 조인과 Cross 조인 실습 질문있습니다.
23.06.01 21:48 작성 23.06.01 22:43 수정 조회수 214
0
select *
from emp_salary_hist a
join emp_dept_hist b
on a.empno = b.empno
and a.fromdate between b.fromdate and b.todate ; select *
from emp_salary_hist a
join emp_dept_hist b
on a.empno = b.empno
where a.fromdate between b.fromdate and b.todate ;
12:29에서 where 보다 and로 연산 했을때
join의 양을 확 줄여줘서 where 절로 했을때보다
속도가 빠르다고 말씀해주셨습니다.
이해가 안되는 부분이 있습니다.
where에서 조건을 걸어 필터링된 결과에서 join을 할텐데
on ~ and 로 하는게 더 빠른게 연산되는 이유가 궁금합니다.
where에서 인덱스가 있을경우 해당 값을 풀스캔하지 않고 index에서 걸러진 데이터만 가지고 join을 실행하는걸로 이해하고 있습니다.
지금 where a.fromdate와 b.fromdate는 인덱스가 있고 b.todate는 인덱스가 없는데
a.fromdate 와 b.fromdate를 비교할때는 인덱스를 사용하고
a.fromdate 와 b.todate를 비교할때에는 풀 스캔이 되는건가요?
만약에 반대로 a.fromdate는 인덱스가 없고
b.fromdate만 인덱스가 있다면 이것도 풀스캔이 되는 건가요?
답변을 작성해보세요.
2
권 철민
지식공유자2023.06.02
안녕하십니까,
where에서 조건을 걸어 필터링된 결과에서 join을 할텐데
on ~ and 로 하는게 더 빠른게 연산되는 이유가 궁금합니다.
=> where 조건은 a.fromdate between b.fromdate and b.todate인데 pk 인덱스는 empno + fromdate입니다. 인덱스를 제대로 이용하는 조건은 인덱스의 선두 컬럼이 where 조건에 있는 경우 입니다. 특히 = 조건으로 있으면 좋습니다. 하지만 where절에는 인덱스 선두 컬럼인 empno가 없기 때문에 인덱스를 이용하는 좋은 조건이 될 수 없습니다. 오히려 대용량 테이블의 경우 where 조건 인덱스로 pk를 먼저 타고 테이블을 액세스 하면 성능이 더 느려 질수 있습니다.
그리고 두번째 질문은 where 조건이 있는 조인시 오해하고 있는 부분이 있는 것 같아서 그 부분을 말씀 드리는게 좋을 것 같습니다.
where 조건이 있고, 해당 where 조건에 Index가 달려 있다고 무조건 where 조건이 있는 테이블 부터 Access해서 조인을 수행하지 않습니다. RDBMS는 주로 조인의 연결량이나 I/O 유형, I/O 량등을 종합적으로 판단해서 조인의 방향성을 정합니다.
만약 Where 조건의 일부 컬럼에만 해당하는 인덱스가 있어서 인덱스의 변별력이 좋지 않고 인덱스를 경유해서 테이블을 액세스하는 Random I/O량이 많다면, 그리고 이렇게 먼저 접근된 테이블에서 조인을 시도하는 I/O 량이 많다면 Where조건과 인덱스가 있더라도 해당 테이블을 먼저 조인의 대상으로 삼지 않습니다.
여기까지가 질문에 대한 답변이고, 튜닝에 관심있으신것 같으니 강의에서 설명드린 해당 부분에 대해서 추가적인 말씀을 드리고 싶습니다. 해당 부분에 대한 설명은 강의에서 성능이 좋아 질 수 있거나 동일하거나 수준으로 변경이 필요할 것도 같습니다.
먼저 왜 성능이 좋아지는지에 대해서 말씀 드리면,
조인은 드라이빙 조건과 필터링 조건에 따라서 크게 성능이 좌우 될 수 있습니다. 드라이빙 조건은 먼저 조인이 되는 테이블에 대한 조건으로 조인 되는 양을 줄여 주는데 큰 역할을 합니다. 반대로 필터링 조건은 조인이 완료된 후의 조건 즉 조인을 시작한 테이블이 아니라 조인되는(즉 Nested 되는) 테이블의 조건이며 이는 조인량을 줄이는데 역할을 거의 하지 못합니다.
강의에서 설명드리는 조인 연결은 emp_salary_hist가 먼저 조인되는 경우(그러니까 드라이빙 테이블이 emp_salary_hist)를 가정한 것입니다. 이렇게 emp_salary_hist가 먼저 드라이빙이 되면 a.empno와 e.fromdate는 상수값으로 결정되므로 emp_dept_hist의 PK인 b.empno + b.fromdate를 정확하게 이용할 수 있습니다. 앞 질문에서도 답변드렸듯이 인덱스를 제대로 이용하는 것이 중요하다고 말씀드렸는데, 이렇게 between 조인으로 연결을 해버리면 emp_dept_hist의 인덱스 이용을 제대로 적용할 수가 있습니다.
만약에 between 조인으로 연결하지 않고 where 절로 빠지면 필터링 조건이 되기 때문에 조인량을 줄이는데 큰 역할을 하지 못할 겁니다.
하지만 요즘 상용으로 나오는 DBMS라면 조인절에 기술하지 않고도 where절도 이용해서 emp_dept_hist의 pk를 잘 이용할 것 같습니다.
요약드리자면 제가 성능이 향상될 수 있다는 의미는 원론적인 의미로 조인의 데이터 연결량을 줄일 수 있다는 의미 정도로 해석해 주셨으면 합니다.
감사합니다.
cjh
질문자2023.06.05
강사님 친절한 답변 감사합니다.
답변 내용이 너무 상세하고, 제가 필요한 정보와 모르는 정보도 있어서
찾아보다보니 감사인사가 늦었습니다.
1. 인덱스는 효율적으로 사용하지 않으면 오히려 비효율적이다.
a.인덱스를 효율적으로 사용하는 방법은 선두 필드를 사용하면 ORDER BY,GROUP BY에서 정렬이 필요한경우에도 유용하다,
"=" 등호를 사용하여 정확한 인덱스 데이터를 검색할 수 있게 한다.
b.인덱스를 잘못 사용하는 경우로는 대용량 테이블, 인덱스 크기, 인덱스 중복, RANDOM I/O등을 피한다.
2. 데이터베이스 성능은 드라이빙 테이블에 따라 다르기 때문에 적합한 인덱스를 생성하고,
조인 조건의 필터링을 추가하거나 WHERE 절에 효율적으로 인덱스를 사용하여 ACCESS를 활용할 수 있게 한다.
WHERE 절에 인덱스를 사용하지 않고 조건을 추가하는건 데이터 베이스 성능에는 영향을 미치지 않는다.
3. 이와 같이 SQL을 작성하면 옵티마이저가 판단하에 최적의 실행 계획을 결정한다.
이렇게 정리할 수 있을 까요?
강사님 다시 한번 답변 감사합니다.
권 철민
지식공유자2023.06.07
아, 댓글 질문이 있었군요. 연휴라 제가 인지가 늦었습니다.
네 적어주신 부분이 대부분 맞습니다.
그리고,
WHERE 절에 인덱스를 사용하지 않고 조건을 추가하는건 데이터 베이스 성능에는 영향을 미치지 않는다.
=> 이건 Nested Loop 조건일때는 맞는 이야기 입니다. Hash 조인일때는 상황에 따라 영향을 미칠 수 있습니다.
답변 1