- WHERE절의 인덱스 컬럼에 인위적인 변형을 가하지 않는다. 필요 시 상수(변수) 쪽에 변형을 가하도록 SQL을 작성한다.
- 불필요한 부분을 액세스하지 않는다.
- SELECT * 는 사용하지 않는다.
- WHERE절에 부정형 조건보다 긍정형 조건을 사용한다.
- 조인의 연결고리가 되는 컬럼의 데이터 타입을 같게 한다.
- 인덱스 지정이 예상되는 컬럼은 문자타입으로 하는 것이 좋다.
- 컬럼이 숫자 타입이 확실한 경우를 제외하고는 비교하는 상수 값을 숫자타입으로 표기하지 않는다.
- OR사용을 가급적 제한해서 사용한다.
- 불필요한 NVL함수는 사용하지 않도록 한다.
- 개발 시에는 Hint 를 사용하지 않는다.
- 여러 개로 나누어져 있는 쿼리는 가능하면 하나로 만든다.
- 주석은 /* COMMENT */을 사용한다. (-- 는 사용하지 않는다.)
- 테이블 생성시에는 Default 값을 지정한다. (특히 FLAG 성격의 컬럼)
1.2. 예제로 보는 효율적인 SQL 작성법
1.2.1. 문자타입과 상수 값의 비교
BAD | GOOD |
SELECT CHR, NUM, VAR, DAT FROM SAMPLET WHERE CHR = 10 | SELECT CHR, NUM, VAR, DAT FROM SAMPLET WHERE CHR = ‘10’ |
1.2.2. 인덱스 컬럼의 변형
BAD | GOOD |
- SUBSTR(JOB,1,4) = ‘ - SAL * 12 = 35000 - TO_CHAR(HIREDATE, ’YYMMDD’) = ‘960101’ | - JOB Like ‘ - SAL = 35000/12 - HIREDATE >= TO_DATE(‘960101’, ‘YYMMDD’) AND HIREDATE < TO_DATE(‘960101’, ‘YYMMDD’) +1 |
- 인덱스 컬럼에 변경이 일어나면 인덱스를 이용하지 못하게 된다.
1.2.3. WHERE and HAVING
BAD | GOOD |
SELECT DEPTID, SUM(SALARY) FROM EMP GROUP BY DEPTID HAVING DEPTID = 100; | SELECT DEPTID, SUM(SALARY) FROM EMP WHERE DEPTID = 100 GROUP BY DEPTID; |
- Having 을 사용하게 되면 해당 컬럼은 인덱스를 사용하지 못한다.
1.2.4. OR 연산자 처리 (NOT EXIST)
BAD |
WHERE (SEGMENT >= 1000 AND SEGMENT <= 1490) OR (SEGMENT >= 1500 AND SEGMENT <= 1999) |
GOOD |
WHERE SEGMENT >= 1000 AND SEGMENT <= 1999 AND NOT EXISTS (SELECT SEGMENT FROM TB_SAMPLE WHERE SEGMENT > 1490 AND SEGMENT < 1500) |
1.2.5. OR 연산자 처리 (UNION)
BAD |
SELECT KEY, NEWDATE FROM TB_SAMPLE WHERE (KEY = 1 AND NEWDATE = ‘20020628’) OR (KEY = 2 AND RTRIM(NEWDATE) LIKE ‘200206%’); |
- OR는 나중 조건부터 풀리고 OR특성상 나쁜 쪽 실행계획으로 풀림
GOOD |
SELECT KEY, NEWDATE FROM TB_SAMPLE WHERE KEY = 1 AND NEWDATE = ‘20020628’ UNION ALL SELECT KEY, NEWDATE FROM TB_SAMPLE WHERE KEY = 2 AND RTRIM(NEWDATE) LIKE ‘200206%’; |
1.2.6. 범위 비교대신 IN 을 사용
BAD |
SELECT PART_NO FROM TB_SAMPLE WHERE PART_NO BETWEEN ‘111’ AND ‘113’; |
- 선형 관계가 점 관계로 바뀌게 만들도록 한다..
GOOD |
SELECT PART_NO FROM TB_SAMPLE WHERE PART_NO IN (‘111’,‘112’,‘113’); |
1.2.7. 부정형 대체 (NOT EXISTS)
* 부서원이 없는 부서를 찾는 경우
BAD |
SELECT * FROM EMP WHERE JOB <> 'CLERK' |
- JOB에 인덱스가 있는 경우에도 인덱스를 사용하지 못하게 된다.
GOOD |
SELECT * FROM EMP A WHERE NOT EXISTS ( SELECT 'X' FROM EMP WHERE A.JOB = 'CLERK') |
- NOT IN 보다는 NOT EXISTS 를 권장한다.
1.2.8. 강제적인 인덱스 참조 금지 (Index Suppressing)
- 인덱스 생성 조건 자체가 20% 미만의 분포도를 갖는 데이터를 상대로 한다.
- 따라서 분산이 적은 데이터의 경우 인덱스를 사용하지 않는 경우, 더 빠를 수 있다.
- 방법은 위에서 열거된 인덱스 참조 실패의 경우를 만들면 된다.
BAD | GOOD |
- JOB Like ‘ - SAL = 35000/12 | - RTRIM(JOB) = ‘ - SAL + 0 = 35000/12 |
- 3.3.2. 항목을 참조
1.2.9. Null 비교
- Null 값은 비교를 할 수 없는 값이다.
- IS NOT NULL 키워드에 한정되어 처리가 가능하다.
- 테이블 생성시 가급적 Default 값을 지정하도록 한다.
BAD |
SELECT * FROM EMP WHERE ENAME IS NOT NULL |
- ENAME에 인덱스가 있는 경우에도 인덱스를 사용하지 못하게 된다.
GOOD |
SELECT * FROM EMP WHERE ENAME > ‘ ‘ |
- 단, NULL 값을 찾는 IS NULL 의 경우에는 별다른 방법이 없다.
1.3. 인덱스 선정 기준
- 최종 사용자에 의해 사용되는 어플리케이션 SQL문의 WHERE 절에서 빈번하게 사용되는 컬럼에 인덱스를 생성함
- SQL문에서 테이블을 JOIN하는데 자주 사용되는 연결고리 컬럼에 인덱스를 생성하며, 자주 조합되어 사용되는 경우는 결합인덱스를 생성함
- 같은 값을 가지는 ROW가 적은 비율을 가지는 컬럼 즉, 컬럼의 분포도가 10 ~ 15 % 이내인 경우 적용함. 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상시키도록 함. 분포도가 범위가 아니더라도 부분범위 처리를 목적으로 하는 경우에는 적용할 수 있음
- SQL문의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는 컬럼에는 인덱스를 만들지 않음. 단, ORACLE 8i부터 FUNCTION BASED INDEX를 생성할 수 있음.
- WHERE절의 컬럼뿐 아니라 SELECT 절이나 ORDER BY 절의 컬럼을 인덱스 컬럼으로 구성하여 INDEX만으로 ACCESS할 수 있게 하여 성능향상을 기할 수 있음
- UNIQUE 인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋음. UNIQUE한 컬럼에는 UNIQUE 인덱스를 생성하도록 함
- FOREIGN KEY 컬럼에는 반드시 인덱스를 생성하여 PARENT 테이블에 DML 작업 발생시 CHILD 테이블에 LOCK이 걸리는 것을 방지함
- 모든 Access 경우의 수를 만족할 수 있도록 인덱스간의 역할분담을 고려하여 생성함
0 댓글