DataBase/SQL

SQL BOOSTER - SQL 개발 가이드

now0204 2024. 7. 12. 14:01

 

1. WHERE절 가이드 

 

1.1 WHERE 절의 컬럼은 변형하지 않는다. 

 

WHERE 절에서 사용하는 컬럼은 절대 변형해서는 안 된다. WHERE 절의 컬럼을 변형하면 인덱스를 사용할 수 없기 때문이다.

 

--인덱스 사용불가 
WHERE SUBSTR(T1.ORD_YMD,1,6) = '201703'
--인덱스 사용 가능 LIKE 조건을 사용
WHERE T1.ORD_YMD LIKE '201703%'

 

 

SQL BOOSTER

실행계획을 잘 살펴보면, ORD_YMD를 SUBSTR한 뒤에 처리하며, INDEX FAST FULL SCAN하고 있다. 이는 인덱스의 리프 블록을 모두 읽어서 필요한 데이터를 찾아내는 방식이다. 

ORD_YMD를 변형하지 않는 경우 INDEX RANGE SCAN을 사용한다. 

 

1.2 WHERE 조건절 안티 패턴 

 

-- 컬럼을 결합해 조건 처리 
SELECT * 
FROM T_ORD_BIG T1
WHERE T1.ORD_ST || T1.PAY_TP = 'COMP' || 'BANK'

--컬럼을 소문자로 변경해서 조건 처리 
SELECT * 
FROM T_ORD_BIG T1
WHERE LOWER(T1.CUS_ID) = 'cus_0022';

 

첫 번째 SQL은 컬럼별로 조건을 각각 사용해야 한다. 두 번째 SQL은 T1.CUS_ID의 LOWER를 제거하고 조건 값 쪽을 UPPER처리하는 것이 맞다.

 

다만, 실제 프로젝트에서 개발된 SQL을 보면 다양한 이유로 WHERE조건절에 테이블의 컬럼을 변형한다.

그러한 방법들이 무조건 잘못되었다고 단정할 수는 없다. 하지만 가능하면 테이블의 컬럼은 그대로 사용하는 습관을 갖는 것이 좋다. (유용한 인덱스를 사용하지 못할 수 있기 때문이다.) 

1.3 날짜 조건 처리 

 

오라클의 날짜 속성을 저장할 수 있는 자료형은 DATE와 TIMESTAMP가 있다. DATE는 시분초까지, TIMESTAMP는 밀리 세컨드까지 저장할 수 있다. 

WHERE 절에서 DATE 자료형의 컬럼에는 DATE 형태의 조건을, 문자열 자료형의 날짜 컬럼에는 문자열 조건값을 사용하자

 

1.3.1 문자열 VS 문자열 조건값 

 

- 올바른 사용법

SELECT T1.PAY_TP, COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_YMD = '20170313'
GROUP BY T1.PAY_TP;

 

WHERE 조건절의 ORD_YMD는 문자열 자료형이다. 이에 맞게 조건 값도 문자열을 사용하고 있다. 

 

- 잘못된 사용법 

SELECT T1.PAY_TP, COUNT(*) CNT
FROM T_ORD_BIF T1
WHERE T1.ORD_YMD = TO_DATE('20170313','YYYYMMDD')
GROUP BY T1.PAY_TP;

 

문자열 자료형 조건으로, 문자열을 TO_DATE로 변경한 조건을 달았다.

SQL BOOSTER

 

실행계획을 보면, T_ORD_BIG를 FULL SCAN처리하고 있다. 또한 ORD_YMD컬럼을 INTERNAL_FUNCTION처리하고 있다. 

이에 따라 테이블의  ORD_YMD를 DATE 자료형으로 모두 자동 변환할 것이다. 이와 같이 형변환이 되는 경우에도 인덱스를 제대로 사용할 수 없다. 

 

1.3.2 DATE 자료형 VS 문자열 자료형 조건

 

- 올바른 사용법

SELECT T1.PAY_TP, COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = '20170313';

 

테이블 자료형이 DATE일 때 문자열 조건을 사용해도 잘 작동한다! 

이는 조건이 걸린 문자열을 DATE로 자동 변환하기 때문이다. 

 

- 잘못된 사용법 

SELECT T1.PAY_TP, COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE TO_CHAR(T1.ORD_DT,'YYYYMMDD') = '20170313';

 

ORD_DT를 TO_CHAR 처리하고 있다. 이는 ORD_YMD에 SUBSTR을 처리 한 것과 같다.

테이블의 컬럼을 변형했으므로, ORD_DT에 대한 인덱스를 사용할 수 없다. 

SELECT T1.PAY_TP, COUNT(*) CNT
FROM T_ORD_BIG T1
WHERE T1.ORD_DT = TO_DATE('20170313','YYYYMMDD');

 

조건을 걸 생각이라면, 반드시 위와 같이 처리하자. 위에 자동 변환이 발생하는 SQL도 위와 같이 변환을 명확하게 명시하는 편이 좋다.

 

1.3.3 DATE 자료형 컬럼에 범위 조건 처리 

 

DATE자료형에는 시분초가 다양하게 들어갈 수 있다. 시분초가 다양하게 입력되어 있으면, DATE 자료형에 같다 조건을 사용해 특정 일자 데이터를 조회할 수 없다. 

이와 같은 상황에서 특정 날짜의 데이터를 조회하기 위해서 (모든 시분초 포함) 범위조건을 사용해야 한다. 

SELECT * 
FROM ( -- 생략) T1
WHERE T1.ORD_DT >= TO_DATE('20170313','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170313','YYYYMMDD') +1

마지막 라인에 TO_DATE 후 1을 증가시키고 있는데, 이는 다음날짜인 20170314보다 작은 데이터를 조회하기 위해서이다. 

WHERE T1.ORD_DT
BETWEEN TO_DATE('20170313','YYYYMMDD') AND TO_DATE('20170313 23:59:59','YYYYMMDD HH24:MI:SS');

이와 같은 SQL도 가능하지만, 23시 59분 59.01초는 조회 불가능하다. 

 

*날짜 속성 조건 정리 

  1. 날짜 컬럼이 문자형 자료형이면 문자열 변수로 비교한다.
  2. 날짜 조건이 DATE 자료형이면 문자열 변수로 처리해도 상관없다
    1. 자동으로 문자형이 DATE로 변환된다. -> 다만 명확성과 DBMS 이관을 고려해 TO_DATE를 명시한다.
  3. 테이블의 날짜 컬럼은 절대 변환하지 않는다.
  4. 시분초가 입력된 DATE자료형은 조회시 범위 조건을 사용하자

1.4 조건 값은 컬럼과 같은 자료형을 사용한다.

 

오라클은 서로 다른 자료형에 비교가 발생하면, 우선순위에 따라 한쪽 기준으로 자료형을 자동 변환한다.

이러한 특성으로 잘 못 비교하면 성능이 나오지 않을 수 있다. 따라서 칼럼과 같은 자료형의 조건을 사용하자

값만 보고 자료형을 추측하지말고, 반드시 테이블의 컬럼 자료형이 무엇인지 판단하고 조건을 작성하자

 

오라클은 문자와 숫자를 비교하면, 문자를 숫자로, 문자와 DATE라면 문자를 DATE로 변환한다. 따라서 조건 값이 무조건 문자형이라면, 테이블의 컬럼을 자동 변환하는 경우는 없다. 하지만, 가능하면 테이블의 원래 컬럼과 같은 자료형을 사용하는 습관을 가지자 (DBMS 이관 시 문제 발생을 줄이기 위함) 

 

 

1.5 NOT IN 보다는 IN을 사용하자 (긍정형 조건을 사용하자)

 

IN을 사용하면, 여러 개의 OR 조건을 하나의 조건으로 처리할 수 있다. 이때 되도록 NOT I?N은 사용하지 않는 편이 좋다. 

NOT IN 뿐 아니라, 같지않다아 같은 부정형 조건은 피하는 것이 좋다. 이는 인덱스를 효율적으로 사용하지 못할 가능성이 내포되어 있기 때문이다. 

 

(1) NOT IN VS IN

 

T_ACC_TRN테이블에서 2017년 3월 1일 데이터 중 이체처리상태가 취소나 완료가 아닌 테이블을 조회하기 위해 아래와 같은 SQL을 작성했다고 가정해보자 

 

-- T_ACC_TRN에는 TRN_HND,ST와 TRN_REQ_DT가 인덱스로 걸려있다.
SELECT * 
FROM T_ACC_TRN T1
WHERE T1.TRN_HND_ST NOT IN ('CNCL','COMP')
AND T1.TRN_REQ_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.TRN_REQ_DT < TO_DATE('20170301','YYYYMMDD')+1;

SQL BOOSTER

NOT IN의 실행계획은 위와 같다. 이때 눈에 띄는 것은 INDEX RANGE SCAN이 아니라, INDEX SKIP SCAN이라는 점이다. INDEX RANGE SCAN이 항상 옳은 것은 아니지만, 날짜 조건까지 고려하면 RANGE SCAN이 더 효율적이다.

 

SELECT * 
FROM T_ACC_TRN T1
WHERE T1.TRN_HND_ST IN ('REQ','WALT')
AND T1.TRN_REQ_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.TRN_REQ_DT < TO_DATE('20170301','YYYYMMDD')+1;

SQL BOOSTER

IN조건으로 바꾸었을 뿐인데, Buffers가 7로 개선되고, INDEX RANGE SCAN으로 바뀌었다.

 

실전에서는 IN조건으로 사용한다고 해도 성능 차이가 전혀 없거나, 성능이 안좋을 수도 있다.

다만, IN조건은 옵티마이저가 INDEX RANGE SCAN을 사용할 수 있는 가능성을 만들어 주기 때문에 성능 상 유리할 가능성을 조금 더 얹는 느낌으로 가져가자.

 

또한 NOT IN ('COMP','WAIT','CNCL')와 같은 부정형을 긍정형으로 쓰려는 노력을 통해, TRN_HND_ST ='REQ'와 같이 성능 상 더 이점이 있는 SQL을 작성할 가능성이 조금 더 높아진다! 

 

1.6 불필요한 LIKE를 제거하자 

 

T1.CUS_ID LIKE v_CUS_ID || '%' -- 값이 있으면 조회가 되고, 없으면, LIKE '%'로 모든 회원 조회

T1.ORD_ST LIKE v_ORD_ST || '%'

이와 같이 SQL을 작성하다보면 LIKE 조건을 사용하는 경우 가 많다. LIKE 조건을 사용하면, 하나의 SQL로 다양한 화면을 커버할 수 있기 때문에 자주 사용된다. 

 

하지만, LIKE 조건을 남발은 인덱스 구성을 어렵게 만든다. 같다(=)조건 대신 사용한 LIKE 조건으로 복합 인덱스의 선두 컬럼의 위치를 가지지 못할 수 있기 때문이다. 

 

따라서 LIKE 조건은 꼭 필요한 경우에만 사용하자