시소당
* 25가지 SQL작성법
1.데이터와 비즈니스 어플리케이션을 잘 알아야 한다.
동일한 정보는 다른 비즈니스 데이터 원천으로부터 검색될 수 있다. 이러한 원천에 익숙해야 한다.
당신은 당신의 데이터베이스 안의 데이터의 크기와 분포를 반드시 알아야 한다.
또한 SQL을 작성하기 전에 비즈니스 개체 안의 관계와 같은 데이터 모델을 전체적으로 이해해야
한다. 이러한 이해는 당신이 여러 테이블에서 정보를 검색하는데 있어서 보다 좋은 쿼리를 작성할
수 있다. DESIGNER/2000과 같은 CASE TOOLS은 다른 비즈니스와 데이터베이스 객체사이의 관계
를 문서화 하는데 좋은 역할을 한다.
2.실제 데이터를 가지고 당신의 쿼리를 검사하라.
대부분의 조직은 개발, 검사, 제품의 3가지 데이터베이스 환경을 가진다. 프로그래머는
어플리케이션을 만들고 검사하는데 개발 데이터베이스 환경을 사용하는데, 이 어플리케이션이
제품 환경으로 전환되기 전에 프로그래머와 사용자에 의해 검사 환경하에서 보다 엄격하게 검토되어
야 한다.
SQL이 검사 환경하에서 테스트될 때, 검사 데이터베이스가 가지고 있는 데이터는 제품 데이터베이스
를 반영해야 한다. 비실제적인 데이터를 가지고 테스트된 SQL문은 제품 안에서는 다르게 작동할 수
있다. 엄격한 테스트를 보장하기 위해서는, 검사 환경하에서의 데이터 분포는 반드시 제품 환경에서
의 분포와 밀접하게 닮아야 한다.
3.동일한 SQL을 사용하라.
가능한한 BIND VARIABLE, STORED PROCEDURE, PACKAGE의 이점을 활용하라. IDENTICAL SQL문
의 이점은 PARSING이 불필요하기에 데이터베이스 서버안에서 메모리 사용의 축소와 빠른 수행을
포함한다. 예로서 아래의 SQL 문은 IDENTICAL하지 않다.
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 20;
그러나 I_EMPID라고 이름 주어진 BIND VARIABLE을 사용하면 SQL 문은 이렇게 된다.
SELECT * FROM EMPLOYEE WHERE EMPID = :I_EMPID;
4.주의 깊게 인덱스를 사용하라.
테이블상에 모든 필요한 인덱스는 생성되어야 한다. 하지만 너무 많은 인덱스는 성능을 떨어뜨릴
수 있다. 그러면 어떻게 인덱스를 만들 칼럼을 선택해야 하는가?
*최종 사용자에 의해 사용되는 어플리케이션 SQL과 쿼리의 WHERE 절에서 빈번하게 사용되는 칼럼
에 인덱스를 만들어야 한다.
*SQL 문에서 자주 테이블을 JOIN하는데 사용되는 칼럼은 인덱스되어야 한다.
*같은 값을 가지는 ROW가 적은 비율을 가지는 칼럼에 인덱스를 사용하라.
*쿼리의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는 칼럼에는 인덱스를 만들면 안된다.
*자주 변경되거나 인덱스를 만들때 얻는 효율성보다 삽입, 갱신, 삭제로 인해 잃는 효율성이 더 큰
칼럼에는 인덱스를 만들면 안된다. 이러한 OPERATION은 인덱스를 유지하기 위한 필요 때문에 느려
진다.
*UNIQUE 인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋다. PRIMARY KEY 칼럼에
UNIQUE 인덱스를 사용한다. 그리고 FOREIGN KEY 칼럼과 WHERE 절에서 자주 사용되는 칼럼에는
NONUNIQUE 인덱스를 사용한다.
5.가용한 인덱스 PATH를 만들어라
인덱스를 사용하기 위해서는 기술한 SQL문을 이용할 수 있는 식으로 SQL을 작성하라. OPTIMIZER는
인덱스가 존재하기 때문에 인덱스를 사용하는 ACESS PATH를 사용할 수 없다. 따라서 ACCESS PATH
는 반드시 SQL이 사용할 수 있게 만들어 져야 한다. SQL HINT를 사용하는 것은 인덱스 사용을
보증해주는 방법중 하나이다. 특정 ACCESS PATH를 선택하기 위한 다음의 힌트를 참고 하라
6.가능하면 EXPLAIN과 TKPROF를 사용하라
만약 SQL문이 잘 다듬어지지 않았다면 비록 오라클 데이터베이스가 잘 짜여져 있어도 효율성이 떨어
질 것이다. 이럴 경우 EXPLAIN TKPROF에 능숙해져야 한다. EXPALIN PLAN은 SQL이 사용하는
ACCESS PATH를 발견할 수 있게 해주고 TKPROF는 실제 PERFORMANEC의 통계치를 보여준다.
이 TOOL은 오라클 서버 소프트웨어에 포함되어 있고 SQL의 성능을 향상시켜 준다.
7.OPTIMIZER를 이해하라.
SQL은 RULE-BASED나 COST-BASED중 하나를 이용해서 기동된다.기존의 소프트웨어는 RULE BASED
방식을 채택하고 있다. 그리고 많은 오라클 소프트웨어가 이러한 방식을 오랫동안 사용해 왔다.
그러나 새로 출시된 소프트웨어에 대해서는 COST BASED 방식의 OPTIMIZER를 고려해야 한다.
오라클은 새로 출시되는 프로그램을 COST BASED방식으로 업그레이드 시켜왔으며 이러한 방식은
시스템을 훨씬 더 안정적으로 만들었다. 만약 COST BASED방식의 OPTIMIZER를 사용한다면 반드시
ANALYZE 스키마를 정기적으로 사용해야 한다. ANALYZE스키마는 데이터베이스 통계를 데이터 사전
테이블에 기록하는 역할을 수행하며 그렇게 되면 COST BASED OPTIMIZER가 그것을 사용하게
된다. SQL은 COST BASED OPTIMIZER를 사용할 때만 잘 조정될 수 있다. 만약 RULE BASED에서
COST BASED로 바꾸고 싶다면 데이터베이스를 사용하는 모든 소프트웨어의 모든 SQL문의 성능을
평가해 보아야 한다.
8.지엽적으로 동작하더라도 전역적으로 생각하라
항상 주의할 것은 하나의 SQL문을 조정하기 위해 생긴 데이터베이스안의 변화는 다른 응용프로그램
이나 다른 사용자가 이용하는 다른 명령문에 영향을 미친다는 사실이다.
9.WHERE절은 매우 중요하다.
비록 인덱스가 가용하다고 해도 다음의 WHERE 절은 그 인덱스 ACCESS PATH 를 사용하지 않는다.
(즉 COL1 과 COL2는 같은 테이블에 있으며 인덱스는 COL1에 만들어진다.)
COL1 > COL2
COL1 < COL2
COL1 > = COL2
COL1 <= COL2
COL1 IS NULL
COL1 IS NOT NULL.
인덱스는 NULL값을 갖는 칼럼에는 ROWID를 저장하지 않는다. 따라서 NULL값을 갖는 ROW를 검색할
때는 인덱스를 사용하지 못한다.
COL1 NOT IN (VALUE1, VALUE2 )
COL1 != EXPRESSION
COL1 LIKE '%PATTERN'.
이럴 경우 THE LEADING EDGE OF THE INDEX(?) 는 작동되지 않고 인덱스가 사용되지 못하게 한
다. 한편 COL1 LIKE 'PATTERN %'이나 COL1 LIKE 'PATTERN % PATTERN%' 는 한정된 인덱스
스캔을 수행하기 때문에 인덱스를 사용할 수 있다.
NOT EXISTS SUBQUERY
EXPRESSION1 = EXPRESSION2.
인덱스된 컬럼을 포함하는 표현(EXPRESSION), 함수, 계산(CALCULATIONS)은 인덱스를 사용하지
못한다. 다음의 예에서 보면 UPPER SQL 함수를 사용하면 인덱스 스캔을 사용할 수 없고
FULL TABLE SCAN으로 끝나고 만다.
SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) LIKE 'SALES%';
10.레코드 필터링을 위해서는 HAVING보다는 WHERE를 사용하라
인덱스가 걸려있는 칼럼에는 GROUP BY와 같이 HAVING절을 사용하지 마라. 이 경우 인덱스는 사용
되지 않는다. 또한 WHERE절로 된 ROW를 사용하지 마라. 만약 EMP테이블이 DEPTID컬럼에 인덱스
를 가지고 있다면 다음 질의는 HAVING 절을 이용하지 못한다.
SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;
그러나 같은 질의가 인덱스를 사용하기 위해 다시 씌여질 수 있다.
SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;
11. WHERE 절에 선행 INDEX 칼럼을 명시하라.
복합 인덱스의 경우, 선행 인덱스가 WHERE절에 명시되어 있다면 쿼리는 그 인덱스 를 사용할 것이
다. 다음의 질의는 PART_NUM과 PRODUCT_ID 칼럼에 있는 PRIMARY KEY CONSTRAINT에 기초한
복합 인덱스를 이용할 것이다.
SELECT *
FROM PARTS
WHERE PART_NUM = 100;
반면, 다음의 쿼리는 복합인덱스를 사용하지 않는다.
SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;
같은 요청(REQUEST)이 인덱스를 이용하기 위해 다시 씌어 질 수 있다. 다음 질의의 경우,
PART_NUM컬럼은 항상 0 보다 큰 값을 가질것이다.
SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;
12.인덱스 SCAN과 FULL TABLE SCAN을 평가하라.
한 행(ROW)의 15% 이상을 검색하는 경우에는 FULL TABLE SCAN이 INDEX ACESS PATH보다 빠르
다. 이런 경우, SQL이 FULL TABLE SCAN을 이용할 수 있도록 여러분 스스로 SQL을 작성하라.
다음의 명령문은 비록 인덱스가 SALARY COLUMN에 만들어져 있어도 인덱스 SCAN을 사용하지 않을
것이다. 첫 번째 SQL에서, FULL HINT를 사용한다면 오라클은 FULL TABLE SCAN을 수행할 것이
다. 인덱스의 사용이 나쁜 점이 더 많다면 아래의 기술을 이용해서 인덱스 수행을 막을수 있다.
SELECT * --+FULL
FROM EMP
WHERE SALARY = 50000;
SELECT *
FROM EMP
WHERE SALARY+0 = 50000;
다음의 명령문은 비록 인덱스가 SS# COLUMN에 있어도 인덱스 SCAN을 사용하지 않을 것이다.
SELECT *
FROM EMP
WHERE SS# || ' ' = '111-22-333';
오라클이 불분명한 데이터 변환을 수행해야 하는 경우 인덱스가 항상 사용되지않는 것은 아니다.
다음의 예를 보면, EMP 칼럼에 있는 SALARY는 숫자형 칼럼이고 문자형이 숫자값으로 변환된다.
SELECT *
FROM EMP
WHERE SALARY = '50000';
테이블의 행이 15%이거나 그보다 작을 경우 인덱스 스캔은 보다 잘 수행 될 것이다. 왜냐 하면 인덱
스 스캔은 검색된 행(ROW)하나 하나 마다 다중의 논리적인 읽기 검색(READ)을 할 것이기 때문이
다. 그러나 FULL TABLE SCAN은 하나의 논리적 인 읽기 검색 영역 안의 BLOCK에 있는 모든 행들을
읽을 수 있다. 그래서 테이블의 많은 행들에 접근해야 하는 경우에는 FULL TABLE SCAN이 낫다.
예로 다음의 경우를 보자. 만약 EMP TABLE과 그 테이블의 모든 인덱스에 대해 ANALYZE라는 명령어
가 수행된다면, 오라클은 데이터 사전인 USER_TABLES와 USER_INDEXES에 다음과 같은 통계치를
산출해 낸다.
TABLE STATISTICS:
NUM_ROWS = 1000
BLOCKS = 100
INDEX STATISTICS:
BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1
이러한 통계치에 근거해서, 아래에 보이는 것이 각각의 다른 SCAN에 대한 논리적인 읽기(READ)-즉
ACESS된 BLOCK이 될 것이다.
USE OF INDEX TO RETURN ONE ROW = 3
(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) +
AVG_DATA_PER_KEY
FULL TABLE SCAN = 100
(BLOCKS)
USE OF INDEX TO RETURN ALL ROWS = 3000
(NUM_ROWS * BLOCKS ACCESSED TO RETURN ONE ROW USING INDEX)
13. 인덱스 스캔에 ORDER BY를 사용하라
오라클의 OPTIMIZER는 , 만약 ORDER BY라는 절이 인덱스된 칼럼에 있다면 인덱스 스캔을 사용할
것이다. 아래의 질의는 이러한 점을 보여 주는 것인데 이 질의는 비록 그 칼럼이 WHERE 절에 명시
되어 있지 않다고 해도 EMPID컬럼에 있는 가용한 인덱스를 사용할 것이다. 이 질의는 인덱스로부
터 각각의 ROWID를 검색하고 그 ROWID를 사용하는 테이블에 접근한다.
SELECT SALARY
FROM EMP
ORDER BY EMPID;
만약 이 질의가 제대로 작동하지 않는다면, 당신은 위에서 명시되었던 FULL HINT를 사용하는 같은 질의를 다시 작성함으로써 다른 대안들을 이용해 볼 수 있다.
출처 : http://www.ibm.com//developerworks/kr/forums/dw_thread.jsp?forum=7&thread=854&cat=4