SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY ROLLUP(DEPTNO,JOB)

ORDER BY 1,2;

는 기본적으로 다음과 같은 쿼리이다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT DEPTNO, NULL JOB, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

UNION ALL

SELECT NULL DEPTNO, NULL JOB, SUM(SAL)

FROM EMP

GROUP BY NULL

ORDER BY 1,2

;

즉,  

1. ROLLUP 안 전체 GROUP BY 

2. ROLLUP 안의 첫번째 컬럼부터 빠짐

그렇다면 다음과 같은 부분 ROLLUP은?

SELECT A, B, C, MAX(D)

FROM   TAB1

GROUP BY C, ROLLUP(A,B)

SELECT A, B, C, MAX(D)

FROM   TAB1

GROUP BY C, A, B

UNION ALL

SELECT A, NULL B, C, MAX(D)

FROM   TAB1

GROUP BY C, A

UNION ALL

SELECT NULL A, NULL B, C, MAX(D)

FROM   TAB1

GROUP BY C, NULL

즉 아까와 같다 . ROLLUP 밖의 컬럼과 ROLLUP 안의 컬럼포함 전부 GROUP BY 한 후 ROLLUP안의 컬럼이 순차적으로 빠진다.

CUBE는 가능한 모든 집합을 만들어낸다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM EMP

GROUP BY CUBE(DEPTNO,JOB)

ORDER BY 1,2;

는 아래와 같은 쿼리가 된다.

SELECT DEPTNO, JOB, SUM(SAL)

FROM   EMP

GROUP BY DEPTNO, JOB

UNION ALL

SELECT DEPTNO, NULL JOB, SUM(SAL)

FROM   EMP

GROUP BY DEPTNO

UNION ALL

SELECT NULL DEPTNO, JOB, SUM(SAL)

FROM   EMP

GROUP BY JOB

UNION ALL

SELECT NULL DEPTNO, NULL JOB, SUM(SAL)

FROM   EMP

GROUP BY NULL

ORDER BY 1,2;

Oracle XE is limited to:

• only use one core of CPU on a system
• use up to 1GB of RAM
• store up to 4GB of data, 11GB of data on 11.2

The following database features are not supported:

• partitioned objects
• internal Java virtual machine (JVM)  -> 조동욱님의 tpack 설치 불가
• materialized view query rewrite
• changing character set on 11.2 (only AL32UTF8)

오라클 성능 QnA

티팩 : 오동규님의 pl/sql 라이브러리

http://sites.google.com/site/otpack

예제

http://wiki.ex-em.com/index.php/Ask_exem_1

스크립트

http://sites.google.com/site/ukja/sql-scripts-1

http://yjan.tistory.com/