메뉴 건너뛰기

bysql.net

1.4 부분범위처리로의 유도


  • 부분범위처리의 효과적 사용으로 넓은 범위의 처리의 수행속도 향상↑

  • 모든 경우를 부분범위로 처리할 수 없음



1.4.1. 액세스 경로를 이용한 SORT의 대체

  • 일반 인덱스, 클러스터 인덱스는 컬럼값으로 정렬
  • "ORDER BY"는 전체범위 처리
  • 정렬 순서와 액세스 주관의 인덱스 컬럼의 앞부분이 같을 시 힌트 사용으로 부분처리 유도
    • 정렬 순서 컬럼 : ORDER BY에 사용되는 컬럼, 
    • 액세스 주관 인덱스 컬럼 : 조건절에서 사용되는 컬럼

  • 액세스 주관 인덱스 컬럼이 있는 예제

2_1_7.jpg

  • 액세스 주관 인덱스 컬럼이 없는 예제

☞ 조건절에 정렬 순서 컬럼(인덱스 소유)을 조건절에 추가하고 힌트 사용으로 해당 컬럼을 액세스 주관 컬럼으로 하여 

    "액세스 주관 컬럼의 처리범위가 넓어도 다른조건의 처리범위도 같이 넓으면 빠르다" 라는 (?)

    원리에 의해 빠른 수행속도를 얻을 수 있음


※ 만약 주어진 Main 조건이 좁은 범위를 갖을 시 위의 예처럼 부분범위로 유도하는 방법이 오히려 수행속도를 나쁘게 함

    ☞ 해결방안 : 동적(Dynamic) SQL 사용 또는 별도의 SQL 사용



1.4.2. 인덱스만 처리하는 부분범위처리

  • 옵티마이저는 인덱스만으로 처리할 수 있을 시 테이블 액세스를 생략 후 인덱스만 액세스하는 실행계획 수립
  • 처리범위가 넓어지면 인덱스를 통해 테이블을 액세스하는 랜덤 처리량 크게 증가
  • 인덱스는 첫 번째 로우를 찾을 때만 랜덤 액세스 후 스캔, 테이블 액세스 시 항상 랜덤 액세스 수행

☞ 인덱스로만 처리 시 처리 범위가 넓더라도 매우 효율적 처리 가능


  • 인덱스만 사용하는 실행계획 수립 조건

    1. 쿼리에 사용된 모든 컬럼들이 하나의 인덱스에 결합
    2. 인덱스 먼지가 되는 실행계획이 수립되려면 머지되는 두 개의 인덱스 내에 모든 컬럼들이 모두 사용
    3. 쿼리에 사용된 전체 컬럼들이 몇 개의 인덱스에 모두 포함될 수 있다면 인덱스 조인 성립
              cf.) 인덱스만 이용하여 조인을 하는 실행계획 수립 인덱스 조인(p.235-238) 참조
2_1_8.jpg
  • 인덱스 구성 전략을 수립하는 방법

    1. 친밀도가 높은 컬럼 모음
    2. 사용되는 빈도, 액세스 조건 연산자의 형태와 분포도 등을 이용한 컬럼의 순서 정하기
    3. '후보선수'에 해당하는 컬럼 선정 (후보 컬럼도 액세스 효율에 많은 영향을 미치기 때문에 전략적 선택 필요!)

※ 인덱스 구성 시 반드시 액세스 형태를 조사하여 전략적 결정 필요


1.4.3. MIN, MAX의 처리


가. 단일 컬럼의 일련번호 증가 시 (SEQ : 일련번호) 

1) 각 분류단위마다 하나의 로우를 가진 별도 테이블 생성으로, 트랜잭션마다 읽어서 사용하여 1증가 후 저장하는 방법

    ☞ 대개 트랜잭션의 병목현상 및 Lock을 발생시키는 원인


2) 시퀀스 생성기(Sequence Generator) 사용

    ☞ 테이블이나 뷰같은 데이터베이스 객체 중 하나로 사용권한만 있을 시 ,SQL 내에서 별도의 제약없이 사용 가능


CREATE SEQUENCE empno_sql -- 시퀀스 명

   INCREMENT BY 1 -- 증가 단위

   START WITH       1 -- 시작 숫자

   MAXVALUE 100000000 -- 최대값 제한

   NOCYCLE -- 순환 여부

   CACHE 20 -- 메모리 확보 단위


특징

  • SQL 내에서 Currval(현재값), Nextval(다음값) 을 이용하여 값 제공
  • 메모리 내에서 DBMS가 자동으로 관리하여 Lock 발생 없음


나. 특정 단위로 일련번호 증가 시 (DEPTNO + SEQ : 부서별 일련번호 발생)

1) 최종번호 관리 테이블 생성

    ☞ 심각한 Lock 발생 가능


2) 현재 생성 최대값 조회로 1증가

    ☞ 일련번호를 저장할 필요가 없기 때문에 Lock 발생이 없음, 넓은 범위에서 효율적으로 최대값 찾는 것이 중요


SELECT /* + INDEX_DESC(order pk_order) */  NVL(MAX(SEQ), 0) + 1

FROM  ORDER

WHERE DEPT_NO = '12300'

    AND ROWNUM = 1;




1.4.4. FILTER 형 부분범위처리


2_1_9.jpg



1.4.5. ROWNUM을 이용한 부분범위처리


2_1_10.jpg


2_1_11.jpg

2_1_12.jpg



1.4.6. 인라인뷰를 이용한 부분범위처리

구분 

일반 SQL 

인라인뷰를 이용한 SQL 

SQL

SELECT a.dept_name, b.empno, b.emp_name, 

                c.sal_ym, c.sal_tot

FROM  department a, employee b, salary c

WHERE b.deptno = a.deptno

    AND c.empno = b.empno

    AND a.location = 'SEOUL'

    AND b.job = 'MANAGER'

    AND c.sal_ym = '200512'

Order by a.dept_name, b.hire_date, c.sal_ym;            



             

SELECT /* + ORERED, USE_NL(x y) */

x.dept_name, x.empno, x.emp_name, 

                y.sal_ym, y.sal_tot

FROM  (SELECT a.dept_name, b.hire_date, 

                                b.empno, b.emp_name

 FROM department a, employee b

 WHERE b.deptno = a.deptno

     AND a.location = 'SEOUL'

          AND b.job = 'MANAGER'

Order by a.dept_name, b.hire_date) x, SALARY y   

WHERE y.empno = x.empno

    AND y.sal_ym = '200512';


   ※ 힌트사용은 반드시 인라인뷰를 먼저 수행 후 그 결과를 Nested Loops 조인이 되도록 하기 위함

   ☞ 처리 대상이 적은 테이블을 먼저 처리하여 전체범위 처리가 되도록 하고 대량의 데이터를 가진 집합은 부분범위로 처리되도록 함



1.4.7. 저장형 함수를 이용한 부분범위처리

  • 저장형 함수 사용 ☞ SQL 안에 절차형 처리 (다중 SQL, 다양한 연산, 조건처리, Loop 처리, 변수, 상수 등 활용)



1.4.8, 쿼리의 이원화를 이용한 부분범위처리

  • SQL은 분리가 되는 순간 사람이 직접 연결을 하는 작업이 필요하기 때문에 가능하면 SQL을 통합해주는 것이 좋음
  • but 상황에 따라 SQL을 분리함으로써 보다 유리할 경우 발생


▼ 주부(Master)와  술부(Detail)관계를 가진 경우 

2_1_13.jpg


※ 사용자가 너무 많은 주부(Master)항목에 대해 검색할 때, 주부에 대한 넓은 범위 조건 발생 시

    1:M 관계에서 데이터량이 많은 M쪽 집합을 포함하여 전체 데이터를 처리하는데 있어 부담이 큼


    ☞ 온라인 검색화면에서 사람이 한 순간에 모든 결과를 원하지 않기 때문에 부분범위로 주부의 일부를 추출하여 

        해당 주부에 대한 상세정보를 처리하게 되면 보다 빠른 수행속도를 얻을 수 있음