Oracle SQL 쿼리 튜닝 실습 2편 - Join

 

 

이번에는 Join문이 있는 SQL 쿼리를 튜닝 하는 것에 대해 알아보자.

 

 

test data에는, Orders 테이블은 1,048,575 개의 row, Customers 테이블은 17,416 개의 row를 가지고 있다.

초기에는 index도 없고, PK도 없고 FK도 설정되지 않은 상태이다.

아래와 같은 SQL을 실행한다고 가정하자.

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Execution Plan을 확인해보자

Hash Join이 사용된 것을 알 수 있다. 또한, 두 테이블 모두 Full scan을 하고 있다.

(Oracle에서는 비교적 큰 데이터를 join하고, equi join일 때 hash join을 우선적으로 사용한다.)

 

 

(Hash Join에 대해 알고 싶으면 아래 글을 참고하자)

 

[SQL Join 시리즈2] 데이터베이스 Hash Join

 

[SQL Join 시리즈2] 데이터베이스 Hash Join

 

 

그럼 한번, SQL 쿼리를 아래와 같이 변경해보자

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_name = 'Justin Ellison';

Execution Plan을 확인해보자

계속해서 Hash Join이 사용된 것을 알 수 있다. 또한, 두 테이블 모두 Full scan을 하고 있다.

 

Index를 설정해보자

customer_id에 unique 조건을 줘서 unique index가 설정되도록 해보자

그러나 여전히 index를 주어도 Full table scan을 하기 때문에  큰 변화가 없다.

여전히 Full table scan을 하는 이유는 customer_id에 unique 조건을 주었지만 customer_id 컬럼은 where문에는 사용되지 않고 o.customer_id = c.customer_id할 때 full table scan을 하는게 더 빠르게 옵티마이저가 판단했다고 추론할 수 있다.

 

 

그럼 아래와 같이 SQL문을 바꿔서 실행해보자

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id = 'PK-1891093';

Execution Plan을 확인해보자

Join 방식이 Hash Join에서 Nested Loop Join으로 바뀐 것을 알 수 있다.

where 절의 customer_id가 unique index가 걸려 있기 때문에 unique scan을 하는 것이 더 빠르다고 옵티마이저가 판단하였기 때문이다. 따라서, cardinality와 cost가 줄어든 것을 알 수 있다.

 

(Nested Loop Join에 대해 알고 싶으면 아래 글을 참고하자)

 

[SQL Join 시리즈1] 데이터베이스 Nested Loop Join

 

[SQL Join 시리즈1] 데이터베이스 Nested Loop Join

---

Hint

그럼 옵티마이저에게 어떠한 execution plan을 선택하도록 강제하려면 어떻게 해야 할까?

Hint를 주면 된다.

 

 

예를 들면, /*+ USE_MERGE */ 와 같이 사용하는데 merge join을 하도록 강제하는 것이다.

 

 

또한, Hint를 주는 범위를 설정할 수 있다.

하나의 table, 여러 개의 table, query block, 하나의 statement에도 hint를 줄 수 있다.

 

 

위의 SQL 쿼리들에 대해서 Hint를 주어서 Execution Plan을 비교해보자

SELECT /*+ USE_MERGE (c o) */o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_name = 'Justin Ellison';

이전에 Hash join을 하던 SQL 쿼리를 Merge Join을 하도록 강제한 것이다.

Execution Plan을 확인해보자

이전에 Hash join을 하던 SQL 쿼리를 Merge Join을 사용하고 있다.

또한, Merge Join이 Hash Join보다 cost가 크게 나온 것을 알 수 있다.