[ORACLE] SQL 튜닝

1. WHERE절 내에서의 JOIN의 위치

테이블 조인은 WHERE절의 조건 보다 먼저 기술되는 것이 좋다. 이는 SQL Parser에 의해 SQL이 해석될 때 WHERE절의 조건이 밑에서부터 위로 해석이 되기 때문이다. 그러므로 아래 부분에서 건수를 줄여 주면 위쪽에서 처리하는 건수가 적어지므로 더 효율적이다. 하지만 ORACLE 버전에 따라 OPTIMIZER가 서로 다른 실행 경로를 생성하고, 데이터의 건수나 통계의 유무등에 의해 생성되는 경로가 달라지므로 반드시 실행계획을 본 후에 실행 시키는 것이 좋다.

 

2. EXISTS 대신 JOIN의 사용
일반적으로 sub-query 보다는 다음과 같이 join을 하는 것이 더 좋다.:

SELECT …
FROM ...
WHERE EXISTS (SELECT ...
FROM FROM T1 X, T2 Y
WHERE X.A = Y.A
AND X.B = 'A');

SELECT ...
FROM T1 X, T2 Y
WHERE X.A = Y.A
AND X.B = 'A';

아래 쿼리가 성능 향상에 도움이 된다.:

 

3. Equal 비교와 범위 비교
한 테이블에 대해 equal 비교와 범위 비교를 동시에 할 경우 ORACLE은 이 인덱스 들에 대해 merge를 하지 않는다. 예를 들어 테이블의 A 컬럼과 B 컬럼에 각각 non-unique index가 있다고 하자.

SELECT ...
FROM ...
WHERE A > 20
AND B = 'ABC';

위 query는 B에 대한 인덱스만을 사용한다.

 

4. 비교문 사용하기 ( > 와 >= )

A > 3 에서는 인덱스에서  A가 3인 row부터 scan을 시작하고,  A>=4에서는 A가 4인 row부터 scan을 하게 된다. 만약 A가 3인 row가 많다면 scan시 그만큼의 I/O가 추가로 발생하게 되므로 결과값 추출에 더 오랜 시간이 걸린다.

>보다 >= 쓰는것이 효율적이다.

 

5. IN 대신 EXISTS의 활용
IN과 Sub-query를 사용하는 것 보다 EXISTS나 NOT EXISTS를 사용하는 것이 더 나은 성능을 보여준다.

SELECT *
FROM A
WHERE ...
AND EXISTS (SELECT 
FROM B
WHERE A.COL = B.COL
AND A.COL = 'ABC');

 

6. NOT IN 대신 NOT EXISTS의 활용
NOT IN은 내부적으로 sort와 merge를 수반한다. NOT IN을 사용하면 대체적으로 가장 효율이 나쁜데, 이는 sub-query select에 대상이 되는 테이블을 강제로 full table scan 하도록 하기 때문이다. NOT IN 보다는 Outer Join 이나 NOT EXISTS를 사용하는 것이 좋다.

 

7. DISTINCT 대신 EXISTS의 활용
EXISTS가 더 빠르게 결과값을 가져 올 수 있는 이유는 RBDMS Kernel이 sub-query 내에서 만족시키는 값을 하나 찾게 되면 바로 그 sub-query를 종료 시켜 다음 query가 진행되도록 하기 때문이다.

--비효율적인 방법
SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM DEPT D, EMP E
WHERE D.DEPT_NO = E.DEPT_NO;

--효율적인 방법
SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (SELECT 'X'
FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);

8. 중복되는 레코드의 삭제 방법
중복되는 레코드를 효율적으로 삭제하는 방법은 RowID를 활용한 방법이다. 개발 과정에서 발생하는 데이터의 중복을 배제하고 Primary키 등 제약 조건을 걸고자 할때 활용할 수 있다.

예) DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

위 예에서 MIN()의 사용은 경우에 따라 MAX()로도 사용할 수 있다. 그러나 중복되는 데이터
중 지워야 하는 것을 지정하고자 할 때에는 그에 맞게 SQL문을 수정하여야 함을 잊지 말자.

9. Row수 COUNT 하기
COUNT(*)가 COUNT(1)보다 빠르다. 만약 인덱스를 통해 COUNT 한 값을 추출하고자 할 때에는 인덱스로 잡혀있는 컬럼을 COUNT(COLUMN)와 같이 추출하는 것이 가장 빠르게 결과값을 얻을 수 있다.

 

10. Table Alias의 사용
Query에서 여러 개의 테이블을 사용할 때 alias를 사용하고 각각의 컬럼에 alias를 붙여 사용하는 것이 좋다. ORACLE이 dictionary에서 해당 컬럼이 어느 테이블에 있는지를 찾지 않아도 되므로 parsing시간을 줄일 수 있고, 컬럼에 대한 혼동을 미연에 방지 할 수 있다.

 

11. WHERE와 HAVING의 차이
SELECT문에서 HAVING을 WHERE 대신 사용하는 것은 피하는 것이 좋다. HAVING은 fetch된 row들에 대한 filter 역할을 한다. 여기에는 sort나 sum 등의 작업이 수반된다. 만약 select 하고자 하는 데이터를 일정 조건에 따라 추출하고자 할 경우에는 where절을 사용해서 HAVING을 사용함으로써 발생할 수 있는 overhead를 줄여주는 것이 좋다.

 

12. SELECT절에서 Asterisk('*') 사용
Dynamic SQL 컬럼 '*'는 테이블의 모든 컬럼을 참조할 수 있게 해 준다. 그러나 이러한 '*'는 값을 되돌려 줄 때 테이블의 모든 컬럼을 변환하여 반환하므로 매우 비효율적이다. SQL Parser는 Data Dictionary에서 해당 테이블에 대한 모든 컬럼의 이름을 읽어서 SQL 명령문 내의 '*'을 대체하는 작업을 한다. 비록 0.01초 밖에 더 걸리지 않는 작업일 지라도 여러 번 반복하면 많은 시간이 걸릴 수도 있으므로 되도록 Asterisk(*)를 사용하지 않는 것이 좋다.

 

13. UNION-ALL의 활용

두개의 query에 대해서 UNION을 사용할 때, 각각의 query에 의한 결과값이 UNION-ALL에 의해 합쳐지고 다시 내부 작업인 SORT UNIQUE 작업에 의해 최종 결과값을 사용자에게 되돌려 준다. 이 때 UNION 대신 UNION-ALL을 사용하게 되면 SORT UNIQUE 작업은 불필요하게 되며, 그만큼의 시간을 줄일 수 있고 수행 성능을 향상시킬 수 있다. 이는 SORT가 필요하지 않은 경우에만 가능하므로 정확히 확인하고 사용하여야 한다.

 

14. DATE 사용시 주의점
DATE형의 컬럼에 대한 작업시 소수점 5자리 이하의 숫자에 대해서는 작업을 하지 않도록 한다.
소수점 6자리 숫자를 더하게 되면 다음 날짜에 대한 값을 결과로 받게 된다.

-- 2023/09/06 23:59:59
SELECT TO_DATE('20230906','YYYYMMDD') + 0.99999
FROM DUAL;


--2023/09/07 00:00:00
SELECT TO_DATE('20230906','YYYYMMDD') + 0.999999
FROM DUAL;

15. != 인덱스 타게하기

컬럼에 인덱스가 있는경우  !=0 하게되면 인덱스를 못타게 된다. != 대신 >0와 <0을 각각해서 UNION ALL을 하면 속도향상이 된다.

 

 

1. 조인조건보다 WHERE 조건을 아래에 쓰자

2. EXISTS 대신 JOIN을 사용하자

3. equal 비교와 범위 비교를 동시에 할 경우 하나의 인덱스만 사용한다.

4. >보다는 >=이 유리하다

5. IN을 써야하는 경우라면 EXISTS를 활용해보자

6. NOT IN 보다는 Outer Join 이나 NOT EXISTS를 사용하자

7. DISTINCT 대신 EXISTS을 활용하자

8. 중복되는 레코드를 삭제할때는 RowID를 사용하자

9. COUNT(*)가 COUNT(1)보다 빠르다.

10. Table Alias의 사용하자

11. HAVING보다 WHERE을 사용하자

12 .SELECT절에서 Asterisk('*') 사용을 자제하자

13. 중복문제가 없는경우라면 UNION-ALL을 활용하자

14. != 대신 >0와 <0을 사용하자