[Oracle] WITH clause in SELECT statement

SELECT 구문에서 WITH를 사용하여 subquery에 alias를 지정함으로써, 이 alias를 다른 select 구문에서 사용가능하다.

(from "Oracle® Database SQL Language Reference")

WITH 
dept_costs AS (
      SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
      GROUP BY department_name),
avg_cost AS (
      SELECT SUM(dept_total)/COUNT(*) avg
      FROM dept_costs)
SELECT * FROM dept_costs
   WHERE dept_total >
      (SELECT avg FROM avg_cost)
      ORDER BY department_name;

위 구문처럼 두 개의 subquery를 dept_consts와 avg_cost로 alias를 부여함으로써, 하위의 select 구문에서 alias를 사용할 수 있다.

Oracle은 이 두개의 alias들을 inline view 또는 temporary table로 취급한다.

 

아래는 WITH clause를 사용한 또 다른 예이다.

WITH TEMP_SEA AS (     
        SELECT O.REG_DT, O.USER_ID
        FROM USER O 
        WHERE O.REG_DT BETWEEN TO_DATE (:FROM_DATE, 'YYYY-MM-DD HH24:MI') 
                   AND TO_DATE (:TO_DATE, 'YYYY-MM-DD HH24:MI'))
SELECT TO_CHAR(T.REG_DT, 'YYYY-MM-DD HH24:MI:SS'), 
       W.ORG_CODE, W.NOTICE_PRD, T.USER_ID
FROM ORG W
         INNER JOIN VW_USER G 
         ON (G.ORG_CODE = W.ORG_CODE AND G.GRP_CODE = 'A01'),
   TEMP_SEA T
WHERE W.GRP_CODE = 'A01' AND W.NOTICE_PRD = :NOTICE_PRD
      AND T.USER_ID = W.USER_ID;

 

[Oracle] Oracle sql hint

Just refer this page =>  http://such.egloos.com/tb/1472596

[Oracle] Oracle 테이블별 index 정보 조회

Oracle 데이터베이스에서 사용자 테이블 정보 및 index 정보를 조회하는 sql.

– owner별 테이블명, row count 및 row 평균 길이 정보 조회

select table_name, num_rows, avg_row_len

from all_tables

where owner = ‘사용자아이디’

and tablespace_name in (‘사용자테이블스페이스명1’, ‘사용자테이블스페이스명2’)

and num_rows > 0

order by num_rows desc;

 

– owner별 테이블의 테이블명, index명, index의 column명 정보 조회

select C.table_name, C.index_name, C.column_name, C.column_position, T.num_rows 

from all_ind_columns C,

          (select table_name, num_rows

            from all_tables

            where owner = ‘사용자아이디’

            and tablespace_name in (‘사용자테이블스페이스명1’, ‘사용자테이블스페이스명2’)

            and num_rows > 0) T

where C.table_name = T.table_name

order by T.num_rows desc, C.table_name, C.index_name, C.column_position;