2부-1.4-부분범위처리로의유도.hwp

 

1.4 부분범위처리로의 유도

- 이장에서는 어떤 SQL 문들을 부분범위 처리로 유도할 수 있는 지 각 방법들을 살펴본다.

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

-  ORDER BY에 의한 전체처리 범위

- 예 1 : 인덱스를 이용하여 정렬된 순서대로 추출하는 부분범위 처리 방법

  (SQL 문) - 전체처리

 SELECT *

 FROM product

 WHERE item_cd LIKE 'AB%' AND category LIKE 'C%'

 ORDER BY item_cd DESC

  (SQL 문) - item_index를 이용하여 부분범위처리.

 SELECT /*+ INDEX_DESC(product item_index)*/ *

 FROM product

 WHERE item_cd LIKE 'AB%' AND category LIKE 'C%'

- 예 2 :

  (SQL 문) - 전체처리 - ORDER BY 컬럼과 인덱스가 다를때

 SELECT *

 FROM order

 WHERE ord_date like '2005%'

 ORDER BY ord_dept DESC

  (SQL 문) - 부분범위처리 - ord_dept를 액세스할 때 자동으로 정렬됨.

 SELECT /*+ INDEX_DESC(a ord_dept_index)*/ *

 FROM order a

 WHERE ord_date like '2005%' AND ord_dept > ' ';

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

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

 질의에 사용된 모든 컬럼이 하나의 인덱스에 결합되어 있거나

 인덱스 머지되는 실행계획의 경우 머지되는 두 개의 인덱스에 모든 컬럼이 사용되어야 한다.

 질의에 사용된 전체 컬럼이 몇 개의 인덱스에 포함되면 인덱스 조인 성립

- 예 1 : ord_date 인덱스보다는 (ord_date+qty) 인덱스가 2배 이상 빠르다.

  (SQL)

 SELECT ord_date, SUM(qty)

 FROM order

 WHERE ord_date like '200510%'

 GROUP BY ord_date

- 예 2 : 적절한 인덱스 구성

  (SQL) (ord_date+agent_cd)를 사용한다면 약간 비효율적이다.

 SELECT ord_date, count(*)

 FROM order

 WHERE ord_date like '200510%'

 GROUP BY ord_dept

  (SQL) (ord_date+agent_cd)를 사용한다면 매우 효율적이다.

 SELECT ord_date, count(*)

 FROM order

 WHERE ord_date like '200510%'

 GROUP BY agent_cd

1.4.3. MIN, MAX의 처리

- sequence를 사용하는 방법(생략)

1.4.4. FILTER 형 부분범위 처리

- 예 1 : 필터형 처리를 유도하는 것이 좋은 사례

  (SQL) 전체범위 처리 예(COUNT()함수의 목적이 결과의 존재를 확인)

 SELECT count(*) INTO :cnt

 FROM ITEM_TAB

 WHERE DEPT='101' AND seq > 100

   ...

 IF cnt > 0 ... 

  (SQL) 부분범위 처리 예(EXIST로 변환한 예)

 SELECT INTO :cnt FROM DUAL

 WHERE EXISTS (SELECT 'X' FROM ITEM_TAB

              WHERE DEPT='101' AND seq > 100)

   ...

 IF cnt > 0 ... 

  (실행계획)

 SELECT STATEMENT

   FILTER

     TABLE ACCESS (FULL) OF 'DUAL'

     TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_TAB'

       INDEX (RANGE SCAN) OF item_dept_idx(NON-UNIQUE)

- 예 2 : 필터형 처리를 유도하는 것이 좋은 사례

  (SQL) 전체범위 처리 예(MINUS)

 SELECT ord_dept, ord_date, custno

 FROM order

 WHERE ord_date like '2005%'

 MINUS

 SELECT ord_dept, ord_date, '12541'

 FROM sales

 WHERE cust_no='12541'

  (SQL) 부분범위 처리 예(EXIST로 변환한 예)

 SELECT ord_dept, ord_date, custno

 FROM order x

 WHERE ord_date like '2005%' AND

  NOT EXISTS ( SELECT * FROM sales y

    WHERE y.ord_dept=x.ord-dept AND

     t.ord_date=x.ord_date AND cust_no='12541')

1.4.5. ROWNUM의 활용

- ROWNUM - 가상의 컬럼으로 SQL 결과 튜플의 일련 번호이다.

- 예 : 10개의 결과 튜플이 생성되면 실행중지, 아니면 전체를 모두 수행.

 SELECT *

 FROM ITEM_TAB

 WHERE DEPT='101' AND seq > 100 AND ROWNUM <= 10

- 실행계획의 예

 SELECT STATEMENT

   COUNT(STOPKEY)

     TABLE ACCESS (FULL) OF table

- ROWNUM 사례 - ROWNUM 순서와 item_cd 정렬순과 일치(내부저장을 하지 않으므로)

   SELECT ROWNUM, item_cd, category_cd

 FROM product

 WHERE deptno like '120%' and qty >0 and ROWNUM <=10

 ORDER BY item_cd

- ROWNUM 사례 - ROWNUM 순서와 item_cd 정렬순과 일치(전체범위처리)

   SELECT ROWNUM, item_cd, category_cd

 FROM ( SELECT *  FROM product

  WHERE deptno like '120%' and qty >0 ORDER BY item_cd)

  WHERE ROWNUM <=10

- ROWNUM 사례 - GROUP BY 결과를 원하는 수 만큼 출력하고 싶을 때

 SELECT ROWNUM, dept, totqty

 FROM ( SELECT deptno, sum(qty) totqty FROM product

  WHERE deptno like '120%' GROUP BY deptno)

  WHERE ROWNUM <=10

- ROWNUM 사례 - GROUP BY 결과중 5번째 결과만 출력하고 싶을 때

 SELECT ROWNUM, dept, totqty

 FROM ( SELECT ROWNUM as RNUM, deptno, totqty

  FROM ( SELECT deptno, sum(qty) totqty FROM product

   WHERE deptno like '120%' AND ROWNUM <= 100

   GROUP BY deptno)

     )

  WHERE RNUM=5 AND ROWNUM =1  /* 불필요한 처리 없애기 */

- ROWNUM 사례 - 사용자의 무제한 액세스를 방지하고 싶을 때

 SELECT *

 FROM table

  WHERE conditions AND ROWNUM <= 1000

 

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

- 인라인뷰를 활용하여 부분범위처리를 유도하는 원리는 전체범위처리가 되는 부분을 인라인뷰로 묶어서 다른 부분들은 부분범위처리가 되도록 한다.

- 예 1 : 세 개의 테이블을 조인 - 처리대상이 적은 테이블을 먼저 처리하여 전체범위가 되도록하고 대량의 테이블은 부분범위처리로 유도

   (SQL 1) 세 테이블 조인

 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

   (SQL 2) 세 개의 테이블을 조인 - salary 테이블이 대용량인 경우 인라인뷰를 이용하여 dept, employee 조인한 후 (empno+sal_ym) 인덱스 사용하여 salary와 조인

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

  a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot

 FROM (SELECT a.dept_name, b.hire_date, b.empno, b.emp_name

  FROM dept 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 c.sal_ym='200512'

- 예 2 : GROUP BY를 통하여 가동된 중간집합을 생성하고 나머지는 참조되는 일종의 디멘전 테이블들을 부분범위처리로 유도하는 경우

   (SQL 1)

 SELECT a.product_cd, product_name, avg_stock

 FROM product a,

  (SELECT product_cd, SUM(stock_qty) / (:b2-:b1) avg_stock

  FROM prod_stock

  WHERE stock_date BETWEEN :b1 AND :b2

  GROUP BY product_cd) b

 WHERE b.product_cd=a.product_cd AND a.category_cd='20';

   (최적이 수행절차)

 product의 category_cd 값이 20인 product_cd 속성을 찾는다.

 구해진 product_cd 값과 stock_date 값으로 prod_stock 테이블을

  (product_cd+stock_date) 인덱스를 이용하여 액세스한다.

 위 작업을 반복하다 운반단위가 채워지면 종료한다.

   (해결방법) 사용자지정 저장함수 사용 혹은 스칼라 서브쿼리 사용

   (SQL 2) 스칼라 서브쿼리 사용 예

 SELECT a.product_cd, product_name,

  (SELECT SUM(stock_qty) / (:b2-:b1) avg_stock

  FROM prod_stock b

  WHERE b.product_cd=a.product_cd AND

   stock_date BETWEEN :b1 AND :b2) avg_stock

 FROM product a,

 WHERE category_cd='20';

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

- (생략)