SQL문 작성시 속도를 고려하여 작성하기...

출처: http://clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=220509

 

개발하시는분들께 참고하시라고 팁을 간단하게나마 올립니다.

 

그냥 한번 쭉 읽어만 보셔도 조금은 도움 되실거에요~

 

쿼리짤 때 이것들만 지켜도 프로그램 속도는 향상됩니다.

 

 

 

1. 필요도 없는 아우터 조인을 남발하지 말기

 

 

 

2. 한번에 가져올 수 잇는 쿼리는 UNION ALL로 묶어서 가져온다음 로직단에서 처리

  - 메모리와 Disk간 I/O가 속도에 영향을 크게 줌

 

 

 

3. Join SQL의 경우 드라이빙 순서를 맞춤

  - NL-Join시 2개 테이블중 Size가 작은 것이 드라이빙는 것이 아니라.. 인덱스가 없는 테이블을 먼저 드라이빙

  - 복잡한 조건문이 포함될 경우.. 처리량이 작은 순서

 

 

 

4. 인덱스 제대로 탈 수 있도록 조건 주기

  - WHERE user_id > '' 같이 값이 안들어와도 인덱스를 타는 경우 속도가 더 잘 나올 수 있는 경우

    위와 같이 트릭조건을 걸어 옵티마이저가 인덱스를  선택 할 수 있도록 도와 줄 수 있음

  - 인덱스가 걸린 컬럼을 가공하지 않기

     where Substr(User_id, 1, 3) = '123'  ---->  where User_id like '123%' 으로 처리

 

 

 

5. 데이터 타입은 필요한 만큼만 잡아주기

   - 컬럼 길이의 편차가 심한 경우, NULL 로 입력되는 경우가 많은 경우 VARCHAR2 사용

   - 무조건 넉넉하게 잡기보다는 들어갈 내용에 알맞게 잡아주는게 좋습니다.

     varchar2(4000) 으로 잡아 놓았다가 텍스트 다운로드할 필요가 있을 때나 화면에 디스플레이 시켜야 할 필요가 있을 때 

     도대체 자리를 몇자리나 잡아야 하는지 고민될 수 있음

   - null은 말 그대로 값이 없음을 의미하기 때문에 물리적으로도 공간을 따로 할당하진 않습니다.

     하지만 null값에 접근하기 위해서는 4바이트 정도의 주소공간은 필요합니다.

 

 

6. char(5) 보다는 varchar2(5)를 쓰기

   - Space가 추가되는 char 형이 Scan할 데이터가 더 많아져서 성능이 떨어짐

   - 한바이트의 칼럼이라도 오라클에서는 char 보다는 varchar2 가 권장됨

   - 지금은 varchar라고 선언하면 내부에서 varchar2로 변환하는 것으로 알고 있습니다.

 

   tip)

    1. indentity를 가진다면 Integer로 만들어야 하고, 순수 값의 의미라면 int를 쓰는 것이 맞다.

       ex.)

         PK, FK의 시퀀스값(ID) : Integer

         나이, 점수 (int는 원시 자료형) : int

    2. DB에서 null이 반드시 필요한 경우 Integer를 사용한다. int로는 null이 불가능하다.

 

 

7. 반드시 필요한 경우 아니면 Date 보다 varchar2(8)로 사용 : 일자연산이 발생할 경우...

   - 성능상으로는 DATE 형이 우월한 이유

     1. DATE 타입은 옵티마이져가 날짜임을 인식한다

     2. DATE 타입은 7 byte를 차지하고 VARCHAR2(8) 은 8 byte를 차지한다

 

 

   - 그러나 성능 문제뿐만 아니라.. 물리 모델링, 개발효율성, 데이터 품질 등을 같이 생각 해야 함

   - 프로그램내부에서 날짜를 문자로 형변환하여 사용하는 패턴이라면.. 테이블 설계시 Date형이 아니 Varchar2로 하는 센스 필요!

     아래처럼 작성하는 것은 개발효율성이 떨어지고 성능에도 이롭지 못하다.

 

     SELECT ...

       FROM ...

      WHERE 기준일자 BETWEEN TO_DATE('20101021','YYYYMMDD') AND TO_DATE('201010212359', 'YYYYMMDDHH24MISS') ;

 

   - 형변환은 number <-> varchar2 <-> date 형태로 이루어 짐

     여기서 date로 <-> 이루어질시 오버해드가 큼

 

 

   - 단, 일자 데이터 타입에 VARCHAR2(8)을 사용할 때 날짜가 아닌 데이터가 들어가는 문제는 Constraint를 사용하여 해결

 

     ex) ‘20100230’을 걸러낸다.

           CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

 

          ALTER TABLE DT

          ADD CONSTRAINT V_DT_CHK

          CHECK (V_DT = TO_CHAR(TO_DATE(V_DT,'YYYYMMDD'), 'YYYYMMDD') OR V_DT IS NULL) ;

 

 

 

8. 부정형 조건 쓰지 말기

   - is not null, not exists 등

   - 애초에 null이 들어가는 경우가 문제가 있다면 null 안들어가게 default 값 잡아주기

 

 

 

9. select 시 * 쓰지말고 필요 컬럼만 적어 주기

   - 블럭단위로 퍼올리는데 쓸데없이 I/O 엑세스만 더 늘리지 말기

   - 내부적으로는 프로그램에서 사용안하는 컬럼들까지 I/O를 통해 통신됨

 

 

10. 테이블 설계할때 업무 조회시 자주 발생하는 경우와 조건 컬럼 분석해서 인덱스 잡기

  - 시스템 구축 초기에 화면정의서를 보고 사용 패턴을 분석하여 인덱스를 설계

 

 

 

11. distinct 보다 group by 쓰기

  - distinct 가 RDB체계에서는 temp space를 사용하며 그안에 임시로 저장하고 정렬하는 방식이라

    과부하가 많이 걸리기 때문에 group by를 사용

  - distinct 는 일단 원하는 select문들이 나타는데 중복이면 없애는 방식

    group by 는 조인을 했을때 양 그룹중 같은 필드를 한대 묶은 방식

 

 

 

12. if else로 쿼리를 여러개 만들어 하나 실행하는 것 보다 SQL에 상수를 이용해 처리하는게 더 퍼포먼스가 좋음

 

     SELECT ...

       FROM ...

      WHERE '1' = '상수'

     Union All

     SELECT ...

       FROM ...

      WHERE '2' = '상수'