오라클 쿼리 성능향상을 위한 sql 구문 작성 가이드

컬럼 select시 * 를 사용하지 않고, 불필요한 열을 참조하지 않는다.

을 사용할 경우, 모든 컬럼을 fetch 하는데 불필요한 메모리가 소요될 수 있습니다.
또한, count() 의 경우에도 count(1) 이나 count(col1) 처럼 작성하도록 합니다.

--수정 전 
SELECT * FROM tbl1; 
--수정 후
SELECT col1, col2 FROM tbl1;

Bind 변수 사용

bind변수를 사용하지 않고 SQL을 작성하면,
각각 다른 SQL로 인식되어 수행 시마다 매번 각 SQL을 파싱/옵티마이징등을 통해 실행계획을 생성해내는 등
DB 부하를 추가적으로 줄 수 있습니다.

-- 수정 전 
SELECT col1, col2 FROM tbl1 WHERE col1 = 'A';

-- 수정 후
SELECT col1, col2 FROM tbl1 WHERE col1 = :col1_value; -- 'A' 를 parameter로 따로 입력

Where 절에 부정형 조건 사용 지양

인덱스 컬럼인 경우 부정형 조건을 사용할 경우 인덱스를 타지 못하는 경우가 있습니다.
index 조건에 따라 탈 수도 있는데, 긍정형으로 자동변환 해주는 경우도 있습니다.
(ID <> 3 을 ID >3 OR ID <3으로 변경한다던지..)
이 경우에도 추가적인 오버헤드가 생기게 되므로 부정형 조건 사용을 지양하는게 좋습니다.

-- 수정 전
SELECT col1
  FROM tbl1
 WHERE col1 IS NOT NULL

-- 수정 후
SELECT col1
  FROM tbl1
 WHERE col1 > 0

WHERE 조건 컬럼에 함수등을 써서 변형하지 않도록 한다.

컬럼에 변형을 가하면 인덱스틀 타지 못하고,
매 row마다 column에 대한 value를 수정하게 되어 추가적인 부하가 가하게 되므로 변형하지 않도록 합니다.

-- 수정 전1
SELECT col1
  FROM tbl1
 WHERE col1 * 10 = 10

-- 수정 전2
SELECT OrderID, OrderDate, CustomerID 
FROM Northwind.dbo.Orders
WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704'


-- 수정 후1
SELECT col1
  FROM tbl1
 WHERE col1 = 10/10

-- 수정 후2
SELECT OrderID, OrderDate, CustomerID 
FROM Northwind.dbo.Orders
WHERE OrderDate >= '19960704' AND OrderDate < '19960705'

조인 조건 Column은 데이터 타입 일치

조인 조건시 컬럼 타입이 다르면 인덱스 효율이 떨어지기에 데이터 타입을 일치시켜야 합니다.

UNION 에 대신에 UNION ALL 사용

UNION 연산자는 정렬 operation을 사용하여 결과값을 만들기 때문에,
가능하다면 UNION ALL을 사용하는게 좋습니다.

--수정 전
SELECT col1 FROM  tbl1
WHERE col1 = 'G'
UNION
SELECT col2 FROM  tbl2
WHERE col2 = 'M'

--수정 후
SELECT col1 FROM  tbl1
WHERE col1 = 'G'
UNION ALL
SELECT col2 FROM  tbl2
WHERE col2 = 'M'

LIKE 연산자 사용시 비교 문자 앞에 ‘%’를 가급적 사용하지 않는다.

LIKE 연산자를 사용시 문자 앞에 ‘%’를 사용할 경우 조건절 컬럼으로 구성된 인덱스를 타지 못 하므로 가급적 사용하지 않도록 한다.

-- 수정 전
SELECT col1 FROM tbl1
WHERE col1 LIKE '%NAME%'

-- 수정 후
SELECT col1 FROM tbl1
WHERE col1 LIKE 'NAME%'

ANSI SQL로 작성하지 않는다.

Oracle RDBMS 일 경우 ANSI SQL로 작성 하면 내부적으로 Oracle 표준 SQL로 변환하는 작업을 한번 더 거치므로
Oracle 표준 SQL 사용을 기본으로 한다.

-- 수정 전 
SELECT a, b, c FROM tbl1 
   INNER JOIN tbl2
    ON tbl1.a = tbl2.d

-- 수정 후
SELECT a, b, c FROM tbl1, tbl2
WHERE tbl1.a = tbl2.d

결과 row가 1건인게 확정적이라면 where절 마지막에 명시적으로 rownum=1을 추가

Select 구문에서 result row가 한 건이라면 rownum을 이용하여 한 건만 scan하도록 하여 추가적인 scan을 방지한다.

-- 수정 전
SELECT col1
  FROM tbl1
 WHERE    
       AND col1 = '1'

-- 수정 후
SELECT col1
  FROM tbl1
 WHERE    
       AND col1 = '1'
       AND ROWNUM = 1

WITH절 사용 지양

1) SQL Transformation을 할수 없어, 효율적인 PLAN구성이 어렵기 때문입니다.
2) 인덱스를 생성할수 없어 항상 FULL SCAN을 유발하고 NL조인이 일어날 경우 반복적으로 부하를 유발합니다.
3) WITH절은 통계정보를 생성할 수 없어서 비효율적으로 Plan이 생성됩니다.
4) TEMP TABLESPACE를 과도하게 사용하여 다른 TEMP TABLESPACE를 사용하는 프로그램에 영향을 줄 수 있습니다.

<=, >= 보다 BETWEEN을 사용

BETWEEN은 오라클 Optimizer에게 더 효과적인 접근 경로를 선택하게 합니다.

-- 수정 전
 SELECT col1
FROM tbl1
WHERE col1 >= :a1
    AND col1 <= :a2

-- 수정 후
SELECT col1
  FROM tbl1
WHERE col1 
BETWEEN :a1 AND :a2