오라클 쿼리 튜닝 가이드 - 기본

대용량 데이터 베이스를 다루다 보면 쿼리의 미세한 차이로 성능이 달라지는 경우가 더러 있습니다.

SQL 튜닝 전문가가 있어도 모든 쿼리를 튜닝해줄 수 없기 때문에 개발자가 기본적으로 좋은 쿼리를 짜는 방법을 알고 있는 것이 좋습니다. 쿼리를 튜닝하기 전에 좋은 쿼리를 짜기 위한 몇 가지 팁들을 소개합니다.

 

1. WHERE절 내의 JOIN 조건절 위치

SQL Parser에 의해 쿼리가 해석될 때 WHERE내의 조건은 밑에서 부터 위로 해석됩니다.

따라서 JOIN 조건절이 제일 위에 작성되었을 때 아래에서 처리건수를 줄여주기 때문에 효율적이게 됩니다.

처리건수나 통계정보에 따라 옵티마이저가 실행 경로를 다르게 실행할 수 있기 때문에 실행계획을 보는 것이 좋습니다.

 

1) 비효율적인 경우

SELECT ...
  FROM EMP E, DEP D
 WHERE E.JOB = 'manager'
   AND E.DEP_NM = D.DEP_NM

2) 효율적인 경우

SELECT ...
  FROM EMP E, DEP D
 WHERE E.DEP_NM = D.DEP_NM
   AND E.JOB = 'manager'

 

2. EXISTS(서브쿼리) 대신 JOIN 사용

1) EXISTS의 사용

SELECT ...
  FROM EMP E
 WHERE EXISTS (SELECT ...
 				 FROM DEPT D
                WHERE D.DEPT_NO = E.DEPT_NO
                  AND D.DEPT_CAT = 'A');

2) JOIN의 사용

SELECT ENAME
  FROM DEPT D, EMP E
 WHERE E.DEPT_NO = D.DEPT_NO
   AND D.DEPT_CAT = 'A';

 

3. EQUAL 비교와 범위 비교

한 테이블에 대해 두개의 인덱스를 가지고 있을 때 EQUAL 비교와 범위 비교를 동시에 할 경우 ORACEL은 인덱스 들에 대해 merge를 하지 않습니다. 저도 계속 인덱스를 안타는 경우가 있었는데 이 사실 때문이었습니다.

 

1) EMP 테이블에 DEPT_NO 컬럼과 EMP_CAT 컬럼에 인덱스가 있는 경우(merge를 하지 않음)

SELECT NAME
  FROM EMP
 WHERE DEPT_NO > 20
   AND EMP_CAT = 'A';

WHERE내의 조건절은 아래에서부터 읽어 들이기 때문에 EMP_CAT에 대한 인덱스만을 사용합니다. 이럴 경우에는 다른 인덱스를 사용하는 것을 검토해야 합니다.

 

4. 비교문의 사용

1) Case 1

SELECT *
  FROM EMP
 WHERE DEPT_NO > 3;

2) Case 2

SELECT *
  FROM EMP
 WHERE DEPT_NO >= 4

Case 1에서는 조건절이 DEPT_NO > 3이기 때문에 3인 row부터 스캔을 시작하고 Case 2에서는 4부터 스캔을 시작합니다. 따라서 Case 1은 3인 row가 추가로 I/O가 발생하므로 시간이 더 오래 걸립니다.

 

5. IN 대신 EXISTS의 사용

둘 다 컬럼 값의 존재 여부를 판단하지만, IN은 조건에 해당하는 ROW의 컬럼 값을 비교하지만, EXISTS는 조건에 해당하는 ROW의 존재 여부만 체크하기 때문에 성능이 더 좋습니다.

 

6. Table Alias의 사용

여러 개의 테이블 조인 시 항상 테이블에 대한 alias를 사용하고 각각의 컬럼에 대해 alias를 붙여 사용하는 것이 좋습니다.

테이블 조인 시에 같은 이름의 컬럼이 존재할 때, alias를 붙이지 않으면 아래의 에러가 발생할 수 있습니다.

ORA-00918 : column ambiguously defined

alias를 붙여 에러를 미연에 방지할 수 있습니다. 또한 오라클이 딕셔너리에서 해당 컬럼이 어느 테이블에 있는지를 찾지 않아도 되므로 파싱의 시간을 줄일 수 있습니다.

 

7. HAVING 대신 WHERE의 사용

HAVING은 row들에 대해서 필터 역할을 하는데, 이 작업에는 sort나 sum의 작업이 포함됩니다. 따라서 특수한 경우가 아니라면 WHERE을 사용하여 오버헤드를 줄여주는 것이 좋습니다.

 

8. UNION 대신 UNION-ALL의 사용

두 개의 쿼리에 대해서 UNION을 사용할 때, 각각 쿼리에 대해 UNION-ALL에 의해 합쳐지고 SORT 작업 후 사용자에게 결괏값을 리턴해줍니다. 따라서 UNION 대신 UNION-ALL을 사용하게 되면 SORT 작업이 없어지므로 시간을 줄일 수 있습니다. 하지만 SORT 작업이 필요하지 않을 때만 사용해야 합니다.

 

9. 인덱스 컬럼의 가공과 부정형 비교

 

인덱스를 사용하여 컬럼을 조회하려고 할때, 인덱스 컬럼을 가공하거나 부정형 비교를 할시에는 인덱스를 타지 않고 TABLE FULL SCAN을 합니다.

 

1) 부정형 비교

SELECt *
  FROM EMP
 WHERE EMP_NM <> 'manager';

2) 인덱스 컬럼의 가공

SELECT *
  FROM EMP
 WHERE EMP_SAL * 12 = 360000000;

SELECT *
  FROM EMP
 WHERE to_char(REG_DDTM, 'YYYYMMDD') = '20220123';

 

 

위의 9가지 팁들은 성능 향상뿐만 아니라 가독성에도 영향을 주기 때문에 잘 준수하여 작성하는 것이 좋습니다.

인덱스, 조인, 힌트에 대한 부분들은 좀 더 자세한 쿼리 튜닝에 대해 포스팅을 할 때 소개하도록 하겠습니다.