[오라클] 서브쿼리와 성능 문제 이해하기

서브쿼리에 대한 기본 내용

---

서브쿼리는 WHERE 절에 비교조건으로 사용되는 SELECT 쿼리를 의미합니다. 통상적으로 SQL을 작성할 때, 모든 테이블을 조인으로 작성하는 것 보다, 메인 집합을 만들고 서브쿼리를 사용하여 값을 비교해가는 절차적 SQL 작성이 쉽다는 점에 공감할 것입니다.

 

하지만 서브쿼리를 사용하지 않고 조인으로 처리가 가능한 SQL임에도 불구하고, 단지 SQL 작성이 쉽다는 이유 만으로 서브쿼리를 남용할 경우, DB서버에 심각한 성능 문제가 발생할 수 있으므로 각별히 주의해야 합니다. 왜냐하면, SQL에 서브쿼리가 여러 개 존재할 경우, Optimizer가 최적화 과정에서 잘못된 Cost 계산을 하는 경우가 많이 발생하기 때문입니다.

 

물론 서브쿼리를 사용해야 의도한 결과 값을 가져올 수 있는 경우도 있고, 서브쿼리를 이용할 경우 성능이 좋아지는 SQL도 분명히 존재합니다. 그렇지만 위 두 가지 상황이 아니라면 서브쿼리를 사용하기 보다 조인으로 작성하는 것이 Optimizer가 정확한 판단을 할 수 있도록 도와 성능 문제를 예방할 수 있습니다.

 

실제 프로젝트 진행시 서브쿼리의 개수가 많은 SQL을 Optimizer가 잘못된 Cost 계산으로 비효율적인 실행계획이 수립되어, DB 서버의 성능에 악영향을 미치는 경우는 적지않습니다.

 

서브쿼리의 동작방식이나 발생 가능한 성능 문제에 대한 이해 없이, 무분별하게 서브쿼리를 사용하는 것은 지양해야 합니다.

 

 

서브쿼리의 사용 패턴

서브쿼리는 경우에 따라 다양한 형태로 작성할 수 있기 때문에, 이 책에서는 모든 패턴을 나열하기 보다는 자주 사용되는 두 가지 패턴만을 가지고 서브쿼리의 개념에 대해 설명하도록 하겠습니다.

 

* 사용 패턴[1]

SELECT *
  FROM emp
 WHERE sal > (SELECT AVG(sal) FROM emp)

 

사용 패턴[1]과 같이 작성된 서브쿼리의 특징은 추출 결과가 반드시 1건 이어야 합니다. 만약, 서브쿼리의 결과로 2건 이상이 추출된다면 에러가 발생합니다. 위와 같은 패턴은 보통 서브쿼리부터 먼저 수행된 후, Main SQL의 컬럼 값과 비교하는 형태로 수행되는 것이 일반적입니다.

 

 

* 사용 패턴[2]

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
WHERE c2= 'A'
  AND EXISTS (
	SELECT /*+ NO_UNNEST */
		'x'
	FROM SUBQUERY_T1 t1
	WHERE t1.c5 = t2.c2
	)

 

사용 패턴[2]는 EXISTS나 IN 연산자 (또는 NOT EXISTS, NOT IN)를 사용한 경우로 서브 쿼리의 결과가 여러건 추출될 수 있습니다. 서브쿼리 내에 Main SQL과 연결 조건인 T1.C5 = T2.C2가 존재(EXISTS, NOT EXISTS의 경우)하여, Main SQL에서 추출한 값을 상속 받아 서브쿼리의 테이블에 해당 값이 존재하는지 체크하는 방식으로 수행됩니다. 물론 반대로 서브쿼리가 먼저 수행되고 Main SQL에 값을 전달할 수도 있습니다.

실제 서브쿼리 사용시 성능 문제를 발생시키는 대부분의 유형은 사용 패턴[2]와 같은 형태입니다. 그러므로 사용 패턴[2]와 같은 형태의 SQL을 작성 후 서브쿼리가 사용 의도에 맞게 수행되는지 실행계획을 반드시 확인해야 합니다. 왜냐하면, 서브쿼리는 잘못 수행될 경우 DB서버의 성능에 미치는 영향력이 크기 때문입니다. 이와 같은 검토는 DB 서버에서 발생 가능한 성능 문제를 예방할 수 있는 좋은 방법입니다.

서브쿼리의 동작방식과 활용 부분을 들어가기에 앞서 서브쿼리의 기본 특성에 대해 알아둘 필요가 있습니다. 이 특성은 바로 서브쿼리에서 추출되는 데이터가 중복 값이 많더라도, Main SQL의 데이터를 증가시키지 않는다는 것입니다.

* 테스트[1]. 서브쿼리의 기본적인 특성 알아보기

SELECT /*+ QB_NAME(B) */col1
FROM (
	SELECT LEVEL col1
	FROM DUAL
	CONNECT BY LEVEL <= 3
	) a
WHERE a.col1 IN (
	SELECT /*+ QB_NAME(A) */col1 -- 1,2,3가 각각 3개씩 출력
	FROM (
		SELECT LEVEL col1
		FROM DUAL
		CONNECT BY LEVEL <= 3
		UNION ALL
		SELECT LEVEL
		FROM DUAL
		CONNECT BY LEVEL <= 3
		UNION ALL
		SELECT LEVEL
		FROM DUAL
		CONNECT BY LEVEL <= 3
	)
);

서브쿼리에서는 Main SQL과 조인 연결조건에 만족하는 데이터가 9건 추출되나, 전체 SQL에서 최종 추출된 데이터는 Main SQL의 추출 데이터인 3건만 추출되었습니다. 즉, 서브쿼리에서 추출되는 데이터가 중복 값이 많더라도 Unique 값만 처리하므로, 서브쿼리를 조인으로 변경하는 SQL 작성시 서브쿼리의 중복된 데이터는 제거된다는 특성을 SQL에 꼭 반영해야 합니다.

 

* 테스트[2]. 서브쿼리를 조인 (뷰)으로 수행되도록 유도

SELECT /*+ ORDERED QB_NAME(B) */col1
FROM (
	SELECT
	) a
WHERE a.col1 IN (
		SELECT /*+ QB_NAME(A) */
			col1 -- 1,2,3가 각각 3개씩 출력
		FROM (
			SELECT LEVEL col1
			FROM DUAL
			CONNECT BY LEVEL <= 3
			UNION ALL
			SELECT LEVEL
			FROM DUAL
			CONNECT BY LEVEL <= 3
			UNION ALL
			SELECT LEVEL
			FROM DUAL
			CONNECT BY LEVEL <= 3
		)
	)

실행계획을 살펴보면 VIEW라는 오퍼레이션이 존재하여 서브쿼리가 뷰로 변경되었음을 알수 있습니다. 또 HASH UNIQUE 오퍼레이션을 통해 Oracle이 서브쿼리를 부로 변경시 내부적으로 중복값을 제거 (DISTINCT 처리) 하였음을 알 수 있습니다.

서브쿼리 동작방식 이해하기

---

서브쿼리의 동작방식은 크게 Filter 동작방식과 조인 동작방식으로 나눌 수 있으며, 어떤 동작방식으로 처리되느냐에 따라 SQL의 성능 차이가 크게 발생할 수 있습니다. 그러므로 서브쿼리가 효율적인 수행을 할 수 있도록 동작방식을 이해하고 제어할 수 있어야 합니다.

이번 장에서는 서브쿼리의 동작방식을 이해하는 한편, 실행계획을 (동작방식) 제어할 수 있는 힌트들을 살펴보고 직접 실행계획을 제어함으로써, 서브쿼리에 의한 성능 문제가 발생하지 않도록 SQL을 작성하는 방법에 대해 알아보도록 하겠습니다.

FILTER 동작방식

Filter 동작방식은 Main SQL에서 추출된 데이터 건수만큼 서브쿼리가 반복적으로 수행되며 처리되는 방식입니다. 즉, Main SQL의 추출 결과에 대해서, 매 로우마다 서브쿼리에 조인연결 값(이하 Input값)을 제공한 후 수행해 보고, 결과가 TRUE일 경우 데이터를 추출합니다.

예를 들어, Main SQL의 추출 결과가 100만건이라면 서브쿼리는 최대 100만번 수행됩니다. 그런데 만약, 위와 같은 SQL에서 서브쿼리의 조인 연결 컬럼에 적절한 인덱스가 없다면 어떻게 될까요? 독자들도 예상할 수 있겠지만 서브쿼리를 100만번 반복적으로 Full Table Scan을 수행하게 될 것입니다. 이와 같은 실행계획을 가진 SQL이 최악의 성능을 보일것이란 점은 물론이거니와 DB 서버의 성능에도 심각한 악영향을 미칠 것입니다.

반면에, MainSQL의 추출건수가 2건인 경우 Filter 동작방식으로 처리 된다면, 서브쿼리는 최대 2번만 수행하면 됩니다. 게다가 서브쿼리의 조인 연결 컬럼으로 구성된 Unique Index가 존재한다면, 매우 효율적인 처리를 할 수 있을 것입니다.

그런데 Main SQL의 추출 결과가 많은 경우 Filter 동작방식으로 처리된다면, 항상 성능이 안 좋을까? 라는 의문이 생길 수 있습니다. 결론부터 말하면 항상 그렇지는 않습니다. Main SQL의 추출 결과가 많더라도 서브쿼리의 Input 값이 모두 동일하다면, 마치 Main SQL에서 1건만 추출한 것과 같이 1번만 수행하기 때문에 성능 문제가 발생하지 않습니다. 이는 Oracle이 내부적으로 Filter 동작방식에 대해 Filter Optimization이라고 불리는 최적화 작업을 수행하기 때문입니다. 그러나 일반적으로 서브쿼리의 Input 값이 모두 동일한 값일 확률은 매우 희박합니다. 따라서 추출 건수가 많은 경우 서브쿼리를 Filter 동작방식으로 처리할 경우 성능상 비효율적인 경우가 더 많습니다. 따라서 Filter 동작 방식으로 수행될 경우 Input 값의 종류가 적어 성능에 유리한지를 반드시 확인해야 합니다.

FilterOptimization: 서브쿼리를 수행하는 Input값을 Cache하며, 동일한 Input 값이 사용되는 경우 추가적인 읽기 작업 없이 Cache 된 값을 사용합니다.

이제부터 Filter 동작방식이 MainSQL 추출 결과와 Input 값의 종류에 따라, 어떻게 SQL의 성능이 달라지는지, 세 가지 테스트를 통해 자세히 알아 보도록 하겠습니다.

* Script. 서브쿼리 테스트용

DROP TABLE SUBQUERY_T1 PURGE ;
DROP TABLE SUBQUERY_T2 PURGE ;
DROP TABLE SUBQUERY_T3 PURGE ;

< SUBQEURY_T1 >

* 생성 요건

- 테이블건수는 1,500,000 로우

- 컬럼 C4는 값의 종류가 250,000 가지

- 컬럼 C5는 값의 종류가 26가지

- 컬럼 C6는 100,000부터 시작하며, 순차적으로 증가하며, 값의 종류는 250,000가지

CREATE TABLE SUBQUERY_T1 AS
SELECT LEVEL AS C4, CHR(65+MOD(LEVEL,26)) AS C5, LEVEL+99999 AS C6
FROM DUAL
CONNECT BY LEVEL <= 250000;

250,000개의 데이터를 생성한 후 동일한 테이블 값을 6번 반복하여 복사함.

BEGIN
FOR I IN 1..6 LOOP
INSERT INTO SUBQUEYR_T1 SELECT * FROM SUBQUERY_T1;
COMMIT;
END LOOP;
END;
/

* 각 컬럼에 인덱스 생성 및 통계정보를 수집

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'SUBQUERY_TB',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T1_IDX_01 ON SUBQUERY_T1 (C4, C5);
CREATE INDEX SUBQUERY_T1_IDX_02 ON SUBQUERY_T1 (C5);

* 생성 요건

- 테이블 데이터 건수는 500,000 로우

- 컬럼 C1은 값의 종류가 500,000가지 즉, UNIQUE한 값임

- 컬럼 C2는 값의 종류가 26가지

- 컬럼 C3은 값의 종류가 500,000가지이며, 100,000부터 순차적으로 증가

- 컬럼 C4는 값의 종류가 26가지

* 테이블 생성

CREATE TABLE SUBQUERY_T2 AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL, 26)) AS C2, LEVEL+99999 AS C3,
CHR(65+MOD(LEVEL,26)) AS C4
FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼에 인덱스 생성 및 통계정보 수집

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCITT',TABNAME=>'SUBQUERY_T2',CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T2_IDX_01 ON SUBQUERY_T2 (C2, C1);
ALTER TABLE SUBQUERY_T2 ADD CONSTRAINT PK_SUBQUERY_2 PRIMARY KEY (C1);

* 생성 요건

- 테이블 데이터 건수는 500,000 로우

- 컬럼 C1은 값의 종류가 500,000가지이며, UNIQUE한 값을 가지도록 생성

- 컬럼 C2는 값의 종류가 26가지가 되도록 생성

- 컬럼 C3은 값의 정료는 500,000가지이며, 100,000부터 순차적으로 증가하도록 생성

* 테이블 생성

CREATE TABLE SUBQUERY_T3 AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+99999 AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼에 인덱스 생성 및 통계정보 수집

EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'SUBQUERY_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T3_IDX_01 ON SUBQUERY_T3 (C1, C2);
ALTER TABLE SUBQUERY_T3 ADD CONSTRAINT PK_SUBQUERY_3 PRIMARY KEY (C1);

테스트[1]. Main SQL의 추출 결과가 많고 Input값이 Unique한 경우

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS (	SELECT /*+ NO_UNEST */
			'x'
		FROM SUBQUERY_T1 t1
		WHERE t1.c4 = t2.c1   )

위의 SQL의 실행계획에는 FILTER 오퍼레이션이 있기때문에 Filter 동작방식으로 수행되었음을 알 수 있습니다.

가장 먼저 SUBQUERY_T2 테이블을 Full Table Scan으로 읽으며 "C1 >= :b1 AND c1 <= :b2" 조건에 만족하는 데이터를 추출하는데, 총 380,001건이 추출됩니다. 이 중에서 최종 데이터를 추출하기 위해서는 서브쿼리의 결과도 만족하는지를 확인해야 합니다. 이 중에서 최종 데이터를 추출하기 위해서는 서브쿼리의 결과도 만족하는지를 확인해야 합니다. 따라서 서브쿼리로 C1 값을 넘겨준 후 서브쿼리에 결과 값이 존재하는지 확인합니다. 이때 서브쿼리는 C1 값을 SUBQUERY_T1_IDX_01 인덱스를 사용해 총 380,001번 조회하고, 최종 데이터 230,001건을 추출하게 됩니다.

테스트[1]의 SQL의 트레이스 결과를 보면, Main SQL의 추출 결과가 38만건인데, Filter 동작방식으로 수행되다 보니 서브쿼리가 38만번이나 반복적으로 수행되었습니다. 결국 SQL 전체 I/O 발생량의 대부분은 서브쿼리에서 반복적으로 사용하는 SUBQUERY_T1_IDX_01 인덱스에서 발생한 것입니다.

테스트[2]. Main SQL의 추출 건수가 적고, Input 값이 Unique한 경우

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 20004

SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
		'x'
	     FROM SUBQUERY_T1 t1
	     WHERE t1.c4 = t2.c1 )

위 경우 또한 Filter 동작방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는 데이터 건수가 총 5건밖에 되지 않습니다. 그래서 서브쿼리도 5번만 수행하기 때문에, SQL의 성능이 테스트[1]과 비교하면 매우 양호합니다.

마지막으로 Main SQL의 추출 결과는 많지만, Input값(T1.C5)의 종류가 적은 (26가지) 경우의 성능을 확인해 보도록 하겠습니다.

테스트[3]. Main SQL의 추출 건수는 많지만, Input 값의 종류가 26가지인 경우

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
		    'x'
	     FROM SUBQUERY_T1 t1
	     WHERE t1.c5 = t2.c2  )

트레이스 결과를 보면, Main SQL의 추출 건수는 380,001 건으로 상당히 많지만, Input 값의 종류는 26가지에 불과합니다. 하지만 테스트[3]의 서브쿼리는 Main SQL의 추출 결과만큼 매번 수행하지 않았습니다. 그 이유는 서브쿼리의 Input 값을 Cache하여, Input 값이 같을 경우 서브쿼리를 수행하지 않기 때문입니다. 그래서 서브쿼리의 수행횟수는 Input 값의 종류만큼인 26번만 수행되어 성능이 양호하다는 것을 알 수 있습니다.

지금까지 세 가지의 테스트를 통해 서브쿼리가 Filter 동작 방식으로 수행될 경우 Main SQL 추출 결과와 Input 값의 종류에 따라 성능이 어떻게 달라지는지 알아보았습니다.

정리하면, Filter 동작방식은 MainSQL의 추출결과가 많고, 서브쿼리에 제공해주는 값(Input 값)의 종류가 많다면, 성능이 좋지 않다는 것을 알 수 있었습니다. 그러나 Main SQL의 추출 건수가 적거나, Main SQL의 추출 결과가 많다 하더라도 Input 값의 종류가 적은 경우 Filter 동작방식으로 수행되는 SQL도 성능이 양호하다는 것을 알 수 있었습니다.

Filter 동작방식의 경우 항상 Main SQL이 먼저 수행되며, 서브쿼리는 Main SQL에서 추출된 데이터의 값을 전달 받아 매번 확인 하는 형태로 수행됩니다. 이처럼 FILTER 오퍼레이션은 항상 한 가지 방법만을 고수하기 때문에 다양한 상황에서 유연하게 대처하기가 어려운 동작방식이라 볼 수 있습니다. 이와 같은 이유로 서브쿼리가 Filter 동작방식으로 수행되는 경우 SQL 성능이 좋지 않은 경우가 많습니다.

만약, SQL의 실행계획을 점검하다 서브쿼리가 Filter 동작방식으로 수행되고 있다면, 먼저 서브쿼리의 조인 연결 컬럼에 인덱스가 존재하는지 확인해야 합니다. 왜냐하면, 서브쿼리가 Filter 동작방식으로 수행되는데, Full Table Scan으로 처리하고 있다면 심각한 성능 문제가 발생할 수 있기 때문입니다.

조인 동작방식

조인 동작방식을 Filter 동작방식과 비교했을 때 가장 큰 차이점은 가변성이라고 생각합니다. 앞에서 알아본 Filter 동작방식은 수행 순서나 수행 방법이 고정되어, 다양한 상황에 유연한 대처가 어려운 반면, 조인 동작방식은 Nested Loops Joins, Hash Join, Sort Merge Join, Semi Join, Anti Join 등의 다양한 조인 방법 중 유리한 것을 선택할 수 있으며, Semi / Anti / Join을 제외하고 수행 순서까지 선택할 수 있어 보다 유연한 처리가 가능합니다.

기본적으로 Semi/Anti Join은 수행순서가 변경되지 않아 Main Query Block의 테이블이 먼저 수행됩니다. 이처럼 조인순서가 고정되고, Main SQL의 추출 건수가 많을 경우, Hash Join을 수행하면 Build Input 집합이 커져 비효율이 많이 발생하게 되는데, Oracle 10g부터는 Hash Join의 경우에 한해 드라이빙 테이블의 순서 변경이 가능해져, Build Input(선행) 집합이 커짐으로 발생하는 비효율을 효과적으로 개선할 수 있게 되었습니다.

하지만, 조인 동작방식 중에 Nested Loops Join Semi를 제외한 나머지 조인 방법은 Filter 동작방식이 가지고 있는 FILTER 오퍼레이션 효과에 의한 이득을 얻지 못합니다. 따라서 Input 값의 종류가 적은 경우라면, 오히려 Filter 동작 방식이 성능상 유리할 수도 있습니다.

이제부터 SQL의 트레이스 겨로가를 보면서, 조인 동작방식 원리 및 Filter 동작방식과의 성능 차이가 어디서 발생하는지 자세하게 알아 보도록 하겠습니다.

먼저 아래의 동일한 SQL을 각각 Filter 동작방식과 조인 동작방식으로 수행해 본 후, 성능 차이를 비교해 봄으로써 성능 차이가 발생하는 이유에 대해 알아보도록 하겠습니다.

아래의 테스트 SQL은 Filter 동작방식으로 수행되어 성능 문제가 발생하고 있습니다. 아래의 SQL을 통해 Filter 동작방식이 가지고 있는 문제점을 알아보고, 조인 동작방식으로 유도하였을때 성능의 변화가 어떤지 알아보겠습니다.

테스트[1]. Filter 방식으로 수행되어 성능 문제가 발생하는 SQL

var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210

SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ NO_UNNEST */
	            'x'
	     FROM SUBQUERY_T1 t1
	     WHERE t1.c6 = t2.c3 AND t1.c6 >= :b1
	)

Filter 동작방식으로 수행한 위 테스트[1]의 경우, 테이블 T1의 컬럼 C6에 인덱스가 없어 Main SQL의 추출건수만큼 SUBQUERY_T1 테이블을 반복적으로 Full Table Scan 하고 있습니다.

위 SQL은 SUBQUERY_T1의 컬럼 C6에 인덱스를 생성해주면 성능이 개선될 것입니다. 그런데 실 운영환경에서는 인덱스를 생성하는 개선방법을 적용하지 못할 수 있습니다. 위 SQL의 성능 개선을 위해선 당연히 생성되어야 할 것 같지만, 인덱스 생성 작업은 많은 리스크와 체크 사항 등을 동반하는 비용이 큰 작업이고, 더욱이 생성할 인덱스를 자주 사용하지 않는다면 인덱스 생성 효과가 매우 적을 것이기 때문입니다.

인덱스를 생성하려면 우선 SUBQUERY_T1 테이블을 액세스하는 다른 SQL의 실행계획에 영향을 미치는지 확인해야 하고, 트랜잭션 프로그램의 부하도 고려해야 합니다. 그리고 인덱스 추가에 따른 디스크의 여유 및 앞으로의 증가량도 체크해야 합니다. 가끔 수행되는 SQL 하나를 개선하기 위해 신규 인덱스를 생성하는 것은 어쩌면 빈대 잡으려고 초가삼간을 태우는 격이 될 수 있으므로 반드시 득과 실을 따질 필요가 있습니다.

DB 서버의 성능 측면에서 인덱스를 생성하는 것의 이득이 크지 않다면, 인덱스를 생성하지 않고 성능을 개선할 방법을 찾아봐야 합니다.

테스트[1]의 성능을 개선하기 위해 가장 중요한 포인트는 반복적인 Full Table Scan을 줄이는 것입니다. 그러므로 서브쿼리를 조인 동작방식으로 변경하고, Hash Join Semi으로 수행하도록 /*+ UNNEST HASH_SJ */ 힌트를 부여하였습니다.

var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210

SELECT c1, c2, c3
FROM SUBQUERY_T2 t2
WHERE c1 >= :b1 AND c1 <= :b2
AND EXISTS ( SELECT /*+ UNNEST HASH_SJ */
		    'x'
	     FROM SUBQUERY_T1 t1
	     WHERE t1.c6 = t2.c3
	     AND t1.c6 >= :b1 )

힌트를 부여하자 실행계획에서 서브쿼리는 기존의 Filter 동작방식이 아닌 조인 동작방식으로 변경되었고, 조인은 Hash Join Semi로 수행되었습니다. 이와 같은 실행계획의 변경은 SQL이 Filter 동작방식으로 수행되어 221 번 반복적으로 Full Table Scan을 해야 했던 비효율을 제거하고, 단 한번 Full Table Scan으로 수행하도록 변경된 것입니다. 개선전 SQL의 경우 인덱스가 존재하지 않는 것이 비효율의 근본적인 원인이지만, 인덱스를 생성하기 힘든 경우에 서브쿼리의 동작방식을 이해하고 힌트를 사용하여 동작방식을 변경하는 것만으로도 만족할 만한 개선 효과가 있다는 것을 알 수 있습니다.

자주 발생하는 성능 사례를 하나 더 살펴보도록 하겠습니다. Main SQL의 추출 건수는 매우 많고 서브쿼리에 있는 상수 조건이 매우 효율적이어서, 서브쿼리를 먼저 수행해야 효율적인 처리가 되는