[SQL] SQL Booster - SQL 개발 가이드

2024, Jun 26

11.1 WHERE 절 가이드

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

WHERE 절의 컬럼을 변형하면 인덱스를 사용할 수 없다.

17년 3월달의 고객ID별 주문 건수 구하기

3월 ORD_YMD WHERE절로 SUBSTR(T1.ORD_YMD,1,6,) = ‘201703’ 사용했을때

"SQL_ID  7jp29f0aszs75, child number 0"
-------------------------------------
"SELECT  T1. CUS_ID ,COUNT(*) ORD_CNT  FROM    T_ORD_BIG T1  WHERE   "
"SUBSTR(T1.ORD_YMD,1,6) = '201703'  GROUP BY T1.CUS_ID"
 
Plan hash value: 2405517600
 
-----------------------------------------------------------------------------------
| Id  | Operation             | Name          | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |        |       |       |          |
|   1 |  HASH GROUP BY        |               |     90 |  1036K|  1036K| 2445K (0)|
|*  2 |   INDEX FAST FULL SCAN| X_T_ORD_BIG_3 |    304K|       |       |          |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - filter(SUBSTR(""T1"".""ORD_YMD"",1,6)='201703')"
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
"       * parameter 'statistics_level' is set to 'ALL', at session or system level"
 

INDEX FAST FULL SCAN 사용됨

3월 ORD_YMD WHERE절로 T1.ORD_YMD LIKE ‘201703%’ 사용했을때

"SQL_ID  auqhb8zajk8zk, child number 0"
-------------------------------------
"SELECT  T1. CUS_ID ,COUNT(*) ORD_CNT  FROM    T_ORD_BIG T1  WHERE   "
T1.ORD_YMD LIKE '201703%'  GROUP BY T1.CUS_ID
 
Plan hash value: 3782533188
 
-------------------------------------------------------
| Id  | Operation            | Name          | E-Rows |
-------------------------------------------------------
|   0 | SELECT STATEMENT     |               |        |
|   1 |  SORT GROUP BY NOSORT|               |     90 |
|*  2 |   INDEX SKIP SCAN    | X_T_ORD_BIG_4 |   1793K|
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - access(""T1"".""ORD_YMD"" LIKE '201703%')"
"       filter(""T1"".""ORD_YMD"" LIKE '201703%')"
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
"       * parameter 'statistics_level' is set to 'ALL', at session or system level"
 

INDEX SKIP SCAN 사용됨

그 외에도 컬럼변형에 들어가는 경우

컬럼을 결합
1. T1.ORD_ST T1.PAY_TP = ‘COMP’ ’BANK’
컬럼을 소문자로 변경 (→ 조건 값 쪽을 건드려야 한다.)
1. LOWER(T1.CUS_ID) = ‘cus_0022’;

11.1.2 날짜 조건 처리하기

(1) 문자열 자료형 컬럼 vs 문자열 자료형 조건 값

ORD_YMD가 20170313인 데이터 조회하기

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

여기서 T1.ORD_YMD는 문자열 자료형이기 때문에 ‘20170313’ 같이 문자열로 된 값으로 비교해야 함.
T1.ORD_YMD = TO_DATE(’20170313’, ‘YYYYMMDD’) 이렇게 사용할 경우
- 테이블의 모든 데이터를 DATE 자료형으로 변환 처리한다. (FULL SCAN 발생)

(2) DATE 자료형 컬럼 VS 문자열 자료형 조건 값

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

"SQL_ID  au7kfkzv6jj8c, child number 0"
-------------------------------------
"SELECT  T1.PAY_TP ,COUNT(*) CNT  FROM    T_ORD_BIG T1  WHERE   "
T1.ORD_DT = '20170313'  GROUP BY T1.PAY_TP
 
Plan hash value: 531602481
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |        |       |       |          |
|   1 |  HASH GROUP BY               |                    |      2 |  1096K|  1096K|  700K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_BIG          |  87307 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | X_T_ORD_BIG_ORD_DT |  87307 |       |       |          |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   3 - access(""T1"".""ORD_DT""='20170313')"
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
"       * parameter 'statistics_level' is set to 'ALL', at session or system level"
 

INDEX RANGE SCAN이 일어남
문자열과 DATE를 비교하면, 문자열이 DATE로 자동 변환됨
TO_CHAR(T1.ORD_DT, ‘YYYYMMDD’) = ‘20170313’ 이렇게 사용할 경우
- 테이블의 모든 데이터를 DATE 자료형으로 변환 처리함 (FULL SCAN 발생)
- T1.ORD_DT = TO_DATE(’20170313’, ‘YYYYMMDD’) 이렇게 쓰는건 괜찮음 (제일 추천!)

(3) DATE 자료형 컬럼에 범위 조건 처리 (≥ VS BETWEEN)

범위 조건 처리 할 수 있는 방법

WHERE T1.ORD_DT >= TO_DATE('20170313','YYYYMMDD')
	AND     T1.ORD_DT < TO_DATE('20170313','YYYYMMDD') + 1;

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

BETWEEN으로 사용했을경우에는 23:59:59.01 같은 데이터가 누락될 수 있음
≥ <을 사용하도록 한다.

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

WHERE T1.CUS_ID = 2000;

문자열 데이터를 숫자로 비교하게 되면, 인덱스를 활용하지 못하고 FULL SCAN으로 TO_NUMBER 처리하게 됨.

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

(1) NOT IN VS IN

	SELECT  /*+ INDEX(T1 X_T_ACC_TRN_1) */
			*
	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_ID  0mz1umyqvu2sx, child number 0"
-------------------------------------
SELECT  /*+ INDEX(T1 X_T_ACC_TRN_1) */    *  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"
 
Plan hash value: 1000468887
 
--------------------------------------------------------------
| Id  | Operation                   | Name          | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ACC_TRN     |      1 |
|*  2 |   INDEX SKIP SCAN           | X_T_ACC_TRN_1 |      1 |
--------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - access(""T1"".""TRN_REQ_DT"">=TIMESTAMP' 2017-03-01 00:00:00' AND "
"              ""T1"".""TRN_REQ_DT""<TIMESTAMP' 2017-03-02 00:00:00')"
"       filter((""T1"".""TRN_REQ_DT"">=TIMESTAMP' 2017-03-01 00:00:00' AND "
"              ""T1"".""TRN_REQ_DT""<TIMESTAMP' 2017-03-02 00:00:00' AND "
"              ""T1"".""TRN_HND_ST""<>'CNCL' AND ""T1"".""TRN_HND_ST""<>'COMP'))"
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
"       * parameter 'statistics_level' is set to 'ALL', at session or system level"
 

INDEX SKIP SCAN이 발생함 (인덱스의 첫 칼럼이 WHERE에 있지 않아도, 인덱스를 이용할 수 있는 방식)
IN을 사용하면 INDEX RANGE SCAN이 발생하고, Buffers를 줄일 수 있음

(2) ≠ VS IN

근소한 차이로 IN이 더 좋음

(3) NOT IN VS =

NOT IN은 최악
= 으로 사용하도록 함

11.1.5 불필요한 LIKE는 제거하자

LIKE가 많으면, 인덱스를 구성할 때 많은 어려움이 있다. (복합 인덱스의 선두 컬럼으로 선택되지 못할 수 있음)

11.2 불필요한 부분 제거하기

11.2.1 불필요한 COUNT는 하지 않는다

오늘 주문 존재 확인하기

SELECT  COUNT(*)
	FROM    T_ORD_BIG T1
	WHERE   T1.ORD_YMD = '20170225'
	AND     T1.CUS_ID = 'CUS_0006';

테이블 데이터 모두 액세스해야함

SELECT  NVL(MAX(1),0)
	FROM    DUAL A
	WHERE   EXISTS(
				SELECT  *
				FROM    T_ORD_BIG T1
				WHERE   T1.ORD_YMD = '20170225'
				AND     T1.CUS_ID = 'CUS_0006');

DUAL ~ EXISTS를 사용하면 한 건만 있어도 바로 종료됨

	SELECT  NVL(MAX(1),0) EX_DATA
	FROM    T_ORD_BIG T1
	WHERE   T1.CUS_ID = 'CUS_0006'
	AND     ROWNUM <= 1;

이런식으로 ROWNUM으로 대체 가능

11.2.2 COUNT에 불필요한 부분은 제거한다.

COUNT 결과에 영향을 끼치지 않는 부분은 제거한다. (특히 서브쿼리, ORDER BY)

11.2.3 불필요한 컬럼은 사용하지 않는다

SELECT절에 T1.* 같이 불필요한 컬럼도 쓰게되면, 인덱스를 활용하지 못하고 테이블에 접근할 수도 있다.

11.2.4 동일 테이블의 반복 서브쿼리를 제거하자.

합칠 수 있는지 생각할것~

# SQL # Oracle