신기한 연구소

안녕하세요. 신기한 연구소입니다.

SQL 개발을 하다 보면 전체 데이터 중 상위 몇 개만 조회하고 싶은 경우가 있습니다.

바로 이때 ROWNUM을 사용해서 순번을 정한 뒤 조건을 넣을 수 있는데요.

ROWNUM을 알기 위해서는 ROWID와 ORDER BY도 같이 알아보면 좋습니다.

우선 ROWID에 대해 알아봅니다.

테이블에 데이터를 입력하면 해당 행에 대해 ROWID를 생성합니다.

ROWID는 해당 행의 주소 값입니다.

모든 테이블의 행들은 각각의 고유한 주소 값을 가지고 있습니다.

즉, 이 ROWID를 알면 해당 행을 찾을 수 있다는 의미겠지요?

인덱스의 리프 블록(LEAF BLOCK)에는 인덱스 키 값과 그 인덱스에 해당하는 테이블의 행을 찾기 위한

해당 행의 ROWID를 포함하고 있습니다.

ROWNUM은 현재 조회한 쿼리에 순서 값을 나타냅니다.

1번부터 나타내는데 주의할 점이 있습니다.

다음 이미지를 살펴보세요.

처음 조회한 데이터는 ROWNUM 순번이 1번부터 순차적으로 나옵니다.

하지만 두 번째는 ENAME으로 ORDER BY를 적용했는데

ORDER BY 순서에 의해 ENAME은 정렬이 되었지만

ROWNUM은 순서대로 나오지 않습니다.

즉, ORDER BY 하기 전 조회된 데이터에 ROWNUM이 부여가 되고

그 뒤로 ORDER BY가 적용되기에 순번이 뒤섞여 나옵니다.

이 경우는 WHERE 조건을 명시했습니다.

조건에 맞게 조회를 하면서 ROWNUM이 순서대로 조회가 됩니다.

하지만 두번째 쿼리는 역시 ORDER BY에 맞게 SAL은 정렬이 되었지만

ROWNUM은 ORDER BY 하기 전에 순번이 적용되어서

또 뒤섞여 나오게 됩니다.

그렇다면 ORDER BY를 한 대로 ROWNUM을 적용하고 싶다면 방법이 있을까요?

이렇게 서브 쿼리(인라인 뷰)를 사용해서 미리 ORDER BY를 적용한 뒤

메인 쿼리에서 ROWNUM을 읽으면 미리 ORDER BY가 된 뒤로 순번을 정했기 때문에

ENAME도 정렬이 되었고 ROWNUM도 순서대로 순번이 붙어서 나오게 됩니다.

결론은

SQL문을 실행하면 실행 순서가 있습니다.

FROM 절에서 테이블을 확인하고

WHERE 절에서 조건에 맞는 데이터를 가공하고

GROUP BY 절에서 그룹핑하고

SELECT로 데이터를 출력합니다.

그리고 마지막으로 ORDER BY로 정렬을 하는데..

SELECT로 출력하는 시점에 ROWNUM을 적용하기에

그 이후 작업인 ORDER BY로 정렬을 하면 ROWNUM이 섞이게 되는 겁니다.

마지막으로 ORDER BY는 조회된 데이터를 정렬하는 경우 사용하는데

원하는 컬럼 1개 이상을 콤마(,)를 기준으로 나열하고

해당 컬럼마다 오름/내림 차순 설정을 하면 됩니다.

컬럼이 아닌 SELECT 절의 순서대로 숫자로 1, 2, 3으로 표시할 수도 있으며

ALIAS(별칭)을 사용해도 됩니다.

주의할 점은 테이블의 순서가 아닌 SELECT문의 순서를 사용합니다.

3가지 조건을 사용한 예제입니다.

주의할 점은 첫 번째 항목이 우선으로 정렬을 하고

첫번째 항목에 중복이 나오면 두 번째 항목을 기준으로 정렬합니다.

세 번째도 마찬가지고요. DESC는 역순을 의미합니다.

NM은 컬럼명이 아니고 SELECT문에서 ALIAS(별칭) 명입니다.

이렇게 ALIAS명으로 사용할 수 있습니다.

지금까지 ROWNUM, ROWID 그리고 ORDER BY에 대해 살펴봤습니다.

잘못된 내용이나 수정이 필요하면 댓글 주세요

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

SQL작업을 하면 정말 거의 필수적으로 사용하는 것 중 하나가 조인(JOIN)인데요.

이번 포스팅은 조인이 어떤 방식으로 수행되는지 옵티마이저의 조인 수행 방법에 대해 살펴볼게요.

사실 이런 부분을 몰라도 그냥 두 개 이상의 테이블을 조인해서 사용하는 데 문제는 없지만

기본 원리를 알고 간다면 무작정 사용하는 것보다 나을 겁니다.

조인(join)은 두 개 이상의 테이블들을 조회해서 하나의 데이터 그룹(집합)으로 만든다고 볼 수 있습니다.

내부 조인(inner), 외부 조인(outer) 등 개발자는 이런 조인을 이해하고 사용할 것이고

옵티마이저는 이런 조인(join)을 세 가지 조인 방법을 사용합니다.

그럼 하나씩 살펴보겠습니다.

Nested Loop Join (중첩 루프 조인, NL Join)

이름이 좀 어렵게 느껴질 수 있습니다.

NL Join으로 불리는데 쉽게 말해서 for문을 2개 사용한다고 보면 됩니다.

두 개의 테이블을 조인(join)하는 경우 선행되는 테이블과 후행 테이블과의 연결을

for문 2개를 사용해서 처리하는 방법과 비슷한 구조입니다.

위 이미지는 tableA와 tableB를 조인합니다.

먼저 tableA를 for문으로 돌리 듯 조건에 맞는 첫 번째 행을 가지고

두 번째 테이블에 조인을 시도합니다.

두번째 for문의 tableB도 조건에 맞는 첫 번째 행을 추출합니다.

그리고 tableA의 첫번째 행의 조인 조건 값으로 tableB의 첫번째 행의 조인 조건 값이 같은지 비교합니다.

같으면 버퍼에 저장하고 다르면 tableB의 두 번째 행과 비교합니다.

이런 식으로 조건에 맞는 데이터를 찾아다닙니다.

여기서 tableA가 먼저 주도했기에 드라이빙 테이블 또는 외부(Outer) 테이블이라고 합니다.

tableB는 내부(Inner) 테이블이라고 합니다.

NL Join 방식을 보면 드리이빙 테이블은 조건 필터된 데이터를 풀 스캔하게 됩니다.

그래서 드리이빙 데이블의 데이터 양이 성능에 영향을 미치게 됩니다.

해당 결과를 가지고 두 번째 테이블에 접근할 때 보통 인덱스를 사용하게 됩니다.

그렇다면 두번째 테이블의 인덱스가 잘 구성되어 있어야 좋은 성능을 낼 수 있겠지요.

보통 A=B 조인 조건으로 전체 대상의 15% 이내인 적은 수의 결과를 조회하는 경우

옵티마이저가 이 방식을 선택하게 됩니다.

한 건씩 처리하는 방식으로 작업에 필요한 메모리가 따로 필요하지 않으며

어떤 테이블을 드라이빙 테이블로 선택하는지

인덱스 설정은 잘 되어 있는지에 따라 성능이 좌우됩니다.

Sort Merge Join(정렬 병합 조인,  SMJ)

이 방식은 조인하는 컬럼을 기준으로 정렬을 먼저 시도합니다.

테이블 A에서 조건에 맞는 데이터를 추출해서 메모리에 정렬해 둡니다.

테이블 B에서도 조건에 맞는 데이터를 추출해서 메모리에 정렬해 둡니다.

그리고 정렬이 끝나면 메모리에 있는 두 정렬된 데이터를 조인을 진행해서

데이터를 버퍼에 넣습니다.

이렇게 양쪽 테이블을 조건에 맞게 정렬을 먼저 시도하기에 작업 중 메모리의 사용이 필요합니다.

정렬하는 데이터의 양이 많아지면 결국 임시 테이블을 사용해야 하기에

디스크 IO가 발생하게 되므로 성능에 영향을 끼치게 됩니다.

이 경우 각자 정렬을 한 뒤 조인하기에 인덱스를 사용하지 않으므로

조인 컬럼에 대한 인덱스가 없어도 사용할 수 있는 방식입니다.

정렬된 데이터를 메모리에 올려서 조인을 하기에 많은 양의 데이터 처리에 유리합니다.

보통 범위 검색 연산자인 <, > 등을 사용하는 경우 옵티마이저가 선택하게 됩니다.

Hash Join (해쉬 조인, HJ)

드라이빙 테이블의 추출 데이터를 해쉬 테이블로 구성합니다.

그리고 조인 조건에 따라 해쉬 테이블의 값 하나씩 내부 테이블에 조인을 시도합니다.

HASH_AREA_SIZE에 지정된 메모리 값 내에서 해쉬 테이블을 만들고 = 연산자인 경우에만 사용하게 됩니다.

이 또한 메모리를 사용하기에 드라이빙 테이블의 조건 검색 후

해쉬 테이블로 사용될 데이터가 적으면 좋습니다.

옵티마이저는 이렇게 선택을 할 것이고

혹시 데이터가 메모리 저장 영역을 넘게 되면

임시 테이블에 접근해야 하기에 테이블 IO가 발생하게 됩니다.

그래서 드라이빙 테이블은 결과 값이 적은 게 좋고 이너 테이블은 행이 많아도 괜찮습니다.

해시 조인에서는 드라이빙 또는 Outer 테이블을 Build Input이라고 하고

Inner테이블의 경우는 해쉬 값의 존재를 체크하기에 Prove Input이라고도 합니다.

방식은 NL Join과 비슷하지만 NL Join은 별도 메모리 사용 없이 사용하고 랜덤 엑세스를 하는 반면

HJ은 별도 메모리에 해쉬 테이블을 만들어 드라이빙 테이블에서 검색 된 데이터를 만들고 

이너 테이블의 데이터와 조인 조건에 맞게 해쉬 테이블에 엑세스하는 방식으로 차이점이 있습니다.

지금까지 우리가 조인을 요청할 때 어떤 방식으로 최적의 성능을 낼 수 있는지

옵티마이저가 선택할 수 있는 조인 방법들에 대해 살펴봤습니다.

잘못된 정보나 수정이 필요하면 댓글 주세요

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.