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저장형 함수를 이용한 부분범위 처리

- (생략)