SQL Program 개발자가 반드시 지켜야할 가장 일반적인 Recommend

프로그래밍/DB

by 라제폰 2008. 12. 22. 17:48

SQL Program 개발자가 반드시 지켜야할 가장 일반적인 Recommand 에 대한 요약입니다.

1. driving table 선정이 가장 중요함

optimizer 가 가장 먼저 읽는 table 을 driving 이라함
driving table 이 되기 위해서 driving table 의 key column 에 상수를 주어야함
상수가 아닌 변수나, ||, not , substring , rtrim 등 변형을 가하면 driving 조건에서 배제됨
driving table 에서 가능한 범위를 줄여주어야함
(예 실적->조직구조-> 조직 => 조직구조->조직->실적 )
driving table 의 조건이 동일하다면 ordered hint 지정

2. join 의 횟수를 줄여야 함

Detail 이 driving 되었을때 master 를 detail 건수 만큼 join 할 필요는 없음
예) detail 이나, master table 로 조직을 join 하는 경우=> 한번만 join
예) 상위조직으로 조직별 제품별 실적을 조회하는데 매번 제품을 join 할 필요 없슴
Inline view 이용

3. outer join 은 꼭 필요한 경우가 아니면 쓰지 말며 가능한 inline view 로 처리

4. nvl 함수 남발은 결국 performance 저하

table layout 상에 Notnull 로 지정된 경우는 nvl 함수 사용금지
수량,금액 fields 는 DB team 에서 default 0 로 변경할 예정임

5. index column 에 변형을 가하면 index scan 하지 못함

6. sort, group by, order by 는 부분범위처리를 하지 못하며 performance 를 저하시키니 꼭 필요한 need 외에는 쓰지 않는 것이 유리함

7. 검증된 hint 외의 hint 사용은 DBA팀 CONFIRM 후에 사용할것 INDEX DESC 는 예외

8. MAX 함수는 INDEX DESC 로 대체

9. PLAN 출력후 operation 이 sort merge나 hash join 이면 nested loop 로 hint 줘야 함

10. 실적을 조회하는 경우 data 를 최소로 read 할 수 있는 table 선정이 중요 하위조직별로
실적 sum 을 요구하는 UI 에서 조직별,제품별을 GROUP BY 하는 것보다는 원장 TABLE 을 SUM 하는것이 빠름

11. UI 구조상 여러 조건에 따라 SQL 문이 달라지는경우 SQL 을 분리하거나, Dynamic sql 을 사용할것

12. UI 를 보고 어떤 TABLE을 DRIVING 으로 쓸것인지 어떤 INDEX 를 쓸것인지 결정한후 설계->프로그램 작성하는 것이 좋음

13. 동일 table 을 두번 select 하지 말아야 함

예 UI 에서 월, 분기, 누계실적을 요구하는 경우 월로 Select, 분기 select,누계
select 를 따로 하는 것보다 누계로 한번만 select 후 분기, 월을 계산하면 빨라질 수 있슴

14. PLAN 출력후 PK 로 INDEX SCAN 된다면 ORDER BY 쓰지 말것
(이렇게하면 전체범위 처리를 함)

===========================================================================================

DATABASE

1. 파일시스템

2. 계층형 모델
3. 네트워크형 모델(망형)

4. 관계형모델
    1) 링크,포인터가 없다. (파일시스템)
    2) Optimizer가 있다


     -> 따라서 모든 집합들을 연결할 수 있음으로 당연한 귀결로 정규화를 하게된다.

% 집합개념은 RDBMS의 고유의 성질이 아니라 DBMS의 고유한 성질이다.

이론적으로 관계형DATABASE 장점: 쉽다.
초보SQL개발자 : 집합들간의 연산을 통해 원하는 집합을 만들어낼수 있다.
-- Optimizer는 일반적인 상황전제 -> 구체적인 상황에서 멍청

고수SQL개발자 : 원하는 집합을 만들어낼때 Optimizer에게 명령을 내릴수 있다.
-- Optimizer는 고수SQL개발자에 의도대로 움직여서 똑똑한 Optimizer가된다.

고수SQL개발자가 되는 첩경은 정규화된 집합들간에 연결하는 방법들간의 차이를 잘이해해서
구체적 상황에 적합한 방법을 택하는 것이다. -> 다음은 적적한 index선택

===================================================================================================

관계형 데이터베이스에서 각 집합간의 데이터를 연결 할 수 있는 방법 5가지.

1. 조인을 이용한 방법,

2. Operators (UNION [ALL], INTERSECT, MINUS)를 이용한 방법

3. 사용자함수를 이용한 방법,
4. SUBQUERY를 이용한 방법,
5. SCALAR SUBQUERY를 이용한 방법.

---------------------------------------------------------------------------------------------------
1. 조인을 이용한 방법

- 두 집합간의 곱으로 데이터를 연결하는 가장 대표적인 데이터 연결 방법이다.
종류에는

1) Nested Loop Join -> 작은row들의 연산에서 가장빠른 응밥속도를 낼수 있다. driving table에 종속
2) Sort Merge Join -> hash join으로 유도 driving table 무관
3) Hash Join이 -> 많은 로우들의 연산에 적절 driving table 무관

% Front 에서 모든 SQL문은 Nested Loop Join을 원칙으로 하고
   PLAN에서 Sort 또는 HASH가 나오면 DBA확인받을것

2. Operators (UNION [ALL], INTERSECT, MINUS)를 이용한 방법

- UNION ALL을 제외한 모든 Operators는 SORT를 동반하다.
- 연산된 집합은 세로로만 확장된다.
- 실행계획을 분리할수 있다.
% Where 조건이 처리시 INDEX를 선택해야하나 프로그램에서 OPTION션일때 UNION ALL을 써서 실행계획을 분리하라.

3. 사용자함수를 이용한 방법 5. SCALAR SUBQUERY를 이용한 방법은 대상 집합을 항상 한 개의 ROW로만 데이터를 연결한 후 하나의 컬럼을 Return 받는 방법이다. 결과 집합은 세로의 확장은 전혀 없고 가로의 확장은 단지 하나의 컬럼으로 확장하기 때문에 집합의 확장은 상당히 제한적이다.
   특히 사용자 함수를 사용하는 경우는 메인집합에서 추출된 ROW수만큼 반복 수행되기 때문에 응답속도에 큰 영향을 미치게 된다.
   SCALAR SUBQUERY도 사용자 함수와 비슷하지만 단지 Multi Block Buffer를 사용함으로써 Buffer에있는 데이터에 대해서는
   대상 집합의 데이터를 다시 액세스하지 않는다는 점이 다르다.

% 코드의 이름은 현재 사용자함수를 사용하고 있으나 무조건 SCALAR SUBQUERY를 이용한다.
% 3개정도 까지의 같은 SCALAR SUBQUERY의 이용도 허용한다.

SELECT site_code,
   lctg_id,
   mctg_id,
   sctg_id,
   ctg_id,
   item_code,
   (select xx_name from t_xx where id_code = a.id_code ) LCTG_NAME,

   UFN_NAME('CTG', SITE_CODE, LCTG_ID, '') LCTG_NAME,
   UFN_NAME('CTG', SITE_CODE, MCTG_ID, '') MCTG_NAME,
   UFN_NAME('CTG', SITE_CODE, SCTG_ID, '') SCTG_NAME,
   UFN_NAME('CTG', SITE_CODE, CTG_ID, '') CTG_NAME
FROM TSITECTGITEM a
WHERE ITEM_CODE =:1
   AND SITE_CODE = '07'
ORDER BY SITE_CODE

4. 조인과 서브쿼리는 둘 이상의 집합을 연결한다는 점에서는 같으나,
조인은 집합간의 독립성이 유지되는 반면, 메인쿼리와 그에 종속적인 서브쿼리가 존재하며
서브쿼리에서의 결과집합 레벨은 메인쿼리의 집합레벨과 동일하다

    1.제공자 역할의 서브쿼리
        select item_code , item_name
        from   titem_ss a
        where item_code in ( select item_code from tsitectgitem
                              where ctg_id = 'o002' );

    1.확인자 역할의 서브쿼리
        select item_code , item_name
        from   titem_ss a
        where item_code not in ( select item_code from tsitectgitem
                              where    item_code = a.item_code
                              and      ctgid_+0dkdkdk'


                              )

    % 이에대한 OPIMIZER의 많은 발전이 있었음.
    -- 따라서 제공자 확인자의 역확은 반드시 플랜에서 확인해야함.

% OUTER JOIN

===============================================================================================
----------------------------------------------------

   NULL 비교시.
   서브쿼리에서 NOT IN 과 NOT EXISTS의 사용시 주의점.

-----------------------------------------------

SELECT NVL(SUM( B.RSALE_AMT ), 0) AS ORDER_AMT1
FROM MALLOWN.TORDERDETAIL_S1 B
WHERE B.ITEM_GB IN ('10','20','33')
AND B.RSALE_AMT > 0ㅏ
AND B.ORDER_DATE >= TRUNC(SYSDATE)
UNION ALL
SELECT NVL(SUM( B.RSALE_AMT ), 0) AS ORDER_AMT1이
FROM MALLOWN.TORDERDETAIL_S1 B,
     MALLOWN.TITEMEXTN D
WHERE B.ITEM_CODE = D.ITEM_CODE(+)
AND B.ITEM_GB IN ('10', '20', '33')
AND B.RSALE_AMT > 0
AND B.ORDER_DATE >= TRUNC(SYSDATE)
AND D.IMD_CODE = '1139'

1> outer 조인의 문제.
2> 테이블 여러번 읽는 문제
3> 드라이빙테이블의 문제.
4> 드라이빙조건의 문제.
5> hint사용

-----------------------------------------------------------------------------------------------------
AND D.IMD_CODE = '1139'

OPTIMIZER의 COST BASEd에서는 D.IMD_CODE = '1139' 와 D.IMD_CODE = '0001'
은 실제 실행계획이 다르게 만들어질 수 있습니다. 이렇게 직접상수값을 이용하면.
직접상수의 분포도를 확인하기 때문입니다. --> 하지만 테스트해 보면 이 경우 성능이 더 느린 경우가 종종 있으므로 주의해야 합니다.

----------------------------------------------------------------------------------------------------