데지덤

  1. 데이터관련 직무와 자격
    1. 데이터베이스 직무

    2. 데이터베이스 자격

  2. 데이터관련 학습방법
    1. 데이터베이스 개론 학습

    2. DBMS 학습

    3. 읽어볼만한 DB책

  3. 최신동향과 유명한 Things
    1. DB최신동향

    2. 데이터로 유명한 Things

  4. 데이터베이스 개념
    1. 데이터베이스 개념

    2. DBMS

    3. 데이터베이스 개발과운영

  5. 데이터베이스 설계(1/2)
    1. 데이터표준

    2. 데이터모델링

    3. 데이터모델 디자인패턴

  6. 데이터베이스 설계(2/2)
    1. 프로세스및상관모델링

    2. 정규화

    3. 반(역)정규화

    4. DB물리설계

  7. 인덱싱과 DB프로그래밍
    1. 인덱스와 해싱

    2. 관계연산

    3. DB언어

    4. SQL

    5. 데이터베이스 미들웨어

  8. 데이터베이스 운영
    1. 트랜잭션

    2. 병렬처리

    3. 데이터베이스 복구

    4. 데이터베이스 성능

    5. 병행제어(동시성제어)

  9. 분석계 및 빅데이터기술
    1. 데이터웨어하우스

    2. 데이터마이닝

    3. 빅데이터기술

  10. 데이터거버넌스
    1. 데이터거버넌스

    2. 데이터베이스 감리/진단

  11. 데이터베이스 종류와 보안
    1. 데이터베이스 종류

    2. 데이터베이스 보안

  12. DBMS
    1. 오라클

    2. SQL Server

    3. DB2

    4. Sybase

    5. Altibase

SQL 부분범위처리

개념
조건을 만족하는 전체범위를 처리하는 것이 아니라 일단 운반단위(Array Size) 까지만 처리 하여 추출하는 처리방식

1. SQL 부분범위처리의 개요

  가. SQL 부분처리의 정의

     조건을 만족하는 전체범위를 처리하는 것이 아니라 일단 운반단위(Array Size) 까지만 처리 하여 추출하는 처리방식.

  나. SQL 부분처리의 목적

     스캔범위를 나누어서 운반단위를 가능한 빨리 채워서 처리속도를 향상.

     일부분만 처리하고서도 Optimizer의 특성을 이용하여 정확한 결과 도출.

     처리 범위가 넓더라도 빠른 속도를 얻도록 하기 위함

 

  다. SQL 부분처리의 적용원칙

구분

설명

부분범위처리의 자격

논리적으로 전체범위를 읽어 추가적인 가공을 하지 않고도 동일한 결과를 추출할 수 있다면 자격이 있다.

 

부분범위처리를 할 수 없는 경우

- ORDER BY가 사용된 경우

- UNION, MINUS, INTERSECT를 사용한 경우

부분범위처리를 할 수 없는 경우의 대체

- ORDER BY -> INDEX를 이용하여 ORDER BY를 하지 않아도 되는 형태로 대체

- MINUS, INTERSECT -> EXISTS, NOT EXISTS, IN, NOT IN

 

2. SQL 부분범위처리의 구성도 및 수행속도 향상의 원리

  가. SQL 부분범위처리의 구성도 (1)

    1) 전체범위처리와 부분범위처리의 차이점을 표현한 구성도 (1).

 

    SQL 부분범위처리의 구성도 (2)

   2) 전체범위처리와 부분범위처리의 차이점을 표현한 구성도 (2)

 

  다. 부분범위처리의 수행속도 향상원리

   1) 수행속도는 운반단위를 채우는 속도에 비례한다.

액세스주관 조건의 범위

검증조건의 범위

수행속도

조치사항

좁다

좁다

양호

 

좁다

넓다

양호

 

넓다

좁다

불량

주관조건과 검증조건의 역할을 교체

넓다

넓다

양호

 

 

3. SQL 범위처리로의 유도

구분

내용

인덱스나 클러스터를 이용하여 SORT를 대체

SQL구문에 order by가 있는 경우 인덱스등을 이용하여 order by를 빼도 되는 형태로 변환한다.

인덱스만으로 엑세스해도 되는 구조

결과 컬럼을 얻어올때 인덱스에서 모두 가져올 수 있는 항목인지를 살펴서 인덱스가 다시 테이블을 읽지 않아도 되는 형태로 사용한다.

MAX값을 얻어와야 할 경우 인덱스를 이용

보통 사용하는 MAX(seq)+1 형태를 버리고 역순 인덱스를 이용하여 next seq를 구하는 형태로 변경한다

EXISTS를 활용

데이터의 존재여부를 체크하는 등의 로직을 수행해야 할때 count()를 수행하는 것보다는 exists를 이용하여 존재여부를 파악한다.

ROWNUM을 활용

rownum은 오라클의 pseudo column이다. 이를 이용하여 부분적으로 필요한 데이터만을 얻어올 수 있는 구조로 정의하여 사용한다.

 

select ..., ROWNUM from t where <where clause> group by <columns> having <having clause> order by <columns>;

 

실행순서

 1). FROM/WHERE 절을 처리한다.

 2). ROWNUM이 할당되고 FROM/WHERE절에서 전달되는 각각의 출력 로우에 대해 증가한다.

 3). SELECT가 적용된다.

 4). GROUP BY 조건이 적용된다.

 5). HAVING 조건이 적용된다.

 6). ORDER BY 조건이 적용된다.

조건의 범위가 너무 넓다면 SQL을 이원화하여 처리

 

4. 전체범위처리와 부분범위처리 비교

구분

전체범위처리

부분범위처리

특징

주어진 조건의 범위가 좁은 경우는 문제가 없으나 넓은 경우는 빠른 수행속도를 기대하기 어려움

처리할 범위가 아무리 넓다고 하더라도 그 범위 중의 일부만 처리

스캔방법

드라이빙 조건을 만족하는 범위를 모두 스캔

드라이빙 조건을 만족하는 범위를 차례로 스캔함

체크조건 처리방법

체크조건 검증한 후 성공한 건에 대해 임시 저장공간에 저장

체크조건을 검증하여 성공한 건을 바로 운반단위로 보냄

결과추출방법

저장이 완료되면 필요한 2차 가공을 한 후 운반단위만큼 추출시키고 다음 요구가 있을 때까지 일단 멈춤

운반단위가 채워지면 수행을 멈추고 결과를 추출

공통점

항상 운반단위만 채워지면 일단은 멈춘다

참고

운반단위만큼만 추출되었다고 그것이 부분범위 처리를 한 것이라고 단정지어서는 안된다

 

5. 부분범위처리 예제

예제 1)

SELECT  MAX(ORDDATE)

FROM  ORDER1T

WHERE  ORDDEPT = '430'

AND  STATUS    = '30'

(2.53초 소요)

 

 

1         SORT AGGREGATE

15230         INDEX RANGE SCAN DEPT_DATE  

 

부서+일자로 인덱스로 잡혀 있다. 그런데 조건에 날짜가 없으므로 전체에서 15230건의 인덱스 스캔을 한 다음 소트를 하여 그 결과를 돌려 준다.

 

 

SELECT  /*+ INDEX_DESC(A dept_date) */

                          ORDDATE

             FROM  ORDER1T  A

          WHERE  ORDDEPT = '430'  AND STATUS='30'

                AND  ROWNUM = 1   

 (0.01 초 소요)

 

1    COUNT STOPKEY

1        TABLE ACCESS BY ROWID ORDER1T

2            INDEX RANGE SCAN DESCENDING DEPT_DATE

 

역순으로 인덱스를 읽으라는 힌트를 주고 1건만 읽어 온다.

 

 

 

예제 2)

SELECT  TYPE, COUNT(*)

               FROM  ORDER2T

            WHERE  ITEM  LIKE  'HJ%'

            GROUP BY  TYPE

(10.3초 소요)

 

 

 

 20        SORT GROUP BY

 36630      TABLE ACCESS BY ROWID ORDER2T

 36631           INDEX RANGE SCAN ITEM_STATUS   

 

아이템+스테이터스로 인덱스가 잡혀 있다. 아이템 인덱스 레인지 스캔 36631건을 한 다음, ORDER2T테이블 엑세스를 36630건 한 다음 소트하여 타입별로 그룹바이해서 count값을 을 돌려 준다.

 

 

 

SELECT  STATUS,  COUNT(*)

             FROM  ORDER2T

           WHERE  ITEM LIKE 'HJ%'

           GROUP BY  STATUS

 

(2.5초 소요)

 

20        SORT GROUP BY

36631       INDEX RANGE SCAN ITEM_STATUS

 

타입으로 order by하지 않고 인덱스에 status가 인덱스로 잡혀 있으므로 테이블 엑세스를 하지 않아도 되므로 속도가 엄청나게 향상 된다.

 

 

 

 

예제3)(EXISTS)

 

1.

SELECT COUNT(*) INTO :CNT

FROM     ITEM_TAB

WHERE  DEPT = '101'

      AND  SEQ > 100

. . . . . . .

IF CNT > 0 . . .

. . . . . . .

 

 

2.

SELECT 1 INTO :CNT

FROM  DUAL 

WHERE EXISTS

          (SELECT  'X'

                 FROM    ITEM_TAB

                 WHERE  DEPT = '101'

                       AND  SEQ  >  100 )

. . . . . . .

IF CNT > 0

. . . . . . .

 

 

 

첫번째의 경우 해당하는 것이 1000건 10000건에 상관 없이 무조건

만족하는 것의 모두를 카운트 한다. 그러나, 두번째에서 exists를 사용하여 1건이라도 해당 하면 쿼리 실행은 중단 된다.

 

SELECT 1 INTO :CNT

   FROM   ITEM_TAB

 WHERE  DEPT = '101'

       AND  SEQ  > 100 

       AND  ROWNUM = 1

. . . . . . . .

IF CNT > 0

. . . . . . . .

마찬가지로 rownum을 활용 해서도 할 수 있다.

 

댓글