[오라클/티베로]WITH절 활용 - /*+ Inline */ /*+ Materialize */ 힌트 활용

 

 

동일한 서브쿼리의 결과가 반복적으로 사용될 때 비효율성을 방지하기위해 사용한다.

 

• 다수의 처리를 최대한 단순하게 처리가능
• 시스템에 영향 최소화
• 유지보수 업무수행시 편리
• 사용되는 SQL횟수를 줄일 수 있음

 

 

1. 단독 사용시

WITH v_dept_costs AS

( SELECT  dname, SUM(sal) dept_total

    FROM emp 3, dept d

   WHERE e.deptno = d.deptno

   GROUP BY dname

)

SELECT *

   FROM  v_dept_costs 

WHERE dept_total > 1000000

;

 

 

2.  복수로 사용시 

WITH v_dept_costs AS

( SELECT  dname, SUM(sal) dept_total

    FROM emp 3, dept d

   WHERE e.deptno = d.deptno

   GROUP BY dname),

v_avg_cost AS 

(SELECT SUM(dept_total)/COUNT(*) avg 

   FROM v_dept_costs 

)

SELECT *

FROM v_dept_costs 

WHERE dept_total > (SELECT avg  FROM v_dept_costs )

ORDER BY dname

;

 

반응형

3.   /*+ Inline */    /*+ Materialize */ 힌트의 개념과 활용

DBMS 내부실행 방식 2가지

- Materialize 방식 : 내부적으로 임시 테이블을 생성함으로써 반복 재상용

- Inline 방식 : 물리적으로 임시 테이블을 생성하지 않으며, 참조된 횟수만큼 런타임 시 반복수행 <-- 디폴트임

SQL문에서 반복적으로 참조되는 집합을 미리 선언함으로써 코딩을 단순화하는 용도로 사용됨

 

• 오라클의 경우 실행방식을 상황에 따라 옵티마이저가 결정하며, 필요하다면 사용자가 힌트( Materialize , Inline )로써 지정할 수도 있다.
• Materialize  방식의 With절을 통해 생성된 임시 데이터는 영구적인 오브젝트가 아니어서, With 절을 선언한 SQL문이 실행되는 동안만 유지된다.
• With절을 2개이상 선언할 수 있으며, With절 내에서 다른 With절을 참조할 수도 있다.
• 배치 프로그램에서 특정 데이터 집합을 반복적으로 사용하거나, 전체 처리 흐름을 단순화시킬 목적으로 임시 테이블을 자주 활용하곤 하는데, Materialize 방식의 With절을 이용하면 명시적으로 오브젝트를 생성하지 않고도 같은 처리를 할 수 있다.

2024.12.19 - [기술] - [오라클/티베로]순서를 처리하는 분석함수 - RANK() , DENSE_RANK(), ROW_NUMBER() 비교