PURPOSE
ORACLE8i에 새롭게 도입된 개념에 대한 이해 및 활용 방안 연구.
SCOPE & APPLICATION
.
KEY IDEA
ROLLUP, CUBE, COPY_T, GROUP BY, 데이터의 복제, GROUPING
SUPPOSITION
1.아래와 같은 테이블이 있다고 가정하자.
CREATE TABLE COPY_T (
NO NUMBER NOT NULL,
CHR VARCHAR2(4),
CONSTRAINT COPY_T_PK
PRIMARY KEY ( NO )
USING INDEX PCTFREE 0
STORAGE(INITIAL 8192 NEXT 4096 PCTINCREASE 0 )
TABLESPACE TS)
TABLESPACE TS PCTUSED 40 PCTFREE 0
STORAGE(INITIAL 8192 NEXT 4096 PCTINCREASE 0 )
PARALLEL (DEGREE 1 INSTANCES 1) CACHE;
2.테이블 COPY_T의 인덱스는 다음과 같다.
CREATE UNIQUE INDEX COPY_T_UK ON
COPY_T(CHR)
TABLESPACE SYSTEM PCTFREE 0
STORAGE(INITIAL 8192 NEXT 4096 PCTINCREASE 0 ) ;
DESCRIPTION
여러 개의 속성을 여러 개의 튜플로 생성하거나 합계를 계산하기 위하여 데이타를 복제를 해야할 때, 우리는 위에서 생성한 COPY_T 라는 테이블을 이용하곤 하였다. 이러한 방법은 많은 COST를 필요로 하는 DISK I/O(테이블을 중복하여 읽어야 하는 비효율)를 최소화 함으로서 많은 성능 향상을 가져왔다.
COPY_T를 이용하면 데이타를 복제하고 DECODE를 이용해야 함으로 데이타의 복제와 DECODE의 부담이 발생할 수 밖에 없다. 그러나 ORACLE8i에서는 ROLLUP과 CUBE를 이용하여 데이타를 행과 열의 형태로 조회해 볼 수 있다.
1.MULTIDIDEMSIONAL ANALYSIS
DATA WAREHOUSING에서 다차원 분석(MULTIDIDEMSIONAL ANALYSIS)을 하기 위하여 MULTIDIMENSIONAL DATA는 DATA CUBE로 생성되어 DIMEMSION의 교차 영역에 존재하게 된다. 이때 CUBE는 FACT TABLE에서 분석하고자 하는 VALUE 만 저장되고, DIMEMSION은 저장되지 않는다. 때문에 DATA CUBE는 RDB의 FACT TABLE보다 아주 적은 SIZE를 유지할 수 있다.
다차원 분석에 대한 요구는 CUBE의 한 SLICE로 해결이 가능하므로 아주 빠른 수행속도를 보장하게 된다.SQL의 성능향상을 위하여 SQL 표준화를 담당하는 ANSI 및 ISO에서 ROLLUP과 CUBE 기능을 SQL3 표준으로 채택하도록 제안 하였다. 그 후에 일부 RDBMS에서 이 기능을 추가 하였으며, ORACLE에서는 ORACLE8i부터 이 기능을 추가하게 되었다.
2.ROLLUP
ROLLUP은 GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합하도록 되어 있다.
ROLLUP에 지정된 GROUPPING COLUMNS의 LIST는 SUBTOTAL를 생성하기 위해 사용 되어지며, GROUPPING COLUMNS의 수를 n 이라고 했을 때 n + 1 LEVEL의 SUBTOTAL이 생성되게 된다.
다음과 같이 ROLLUP을 사용한 SQL문이 있다고 가정해 보자.
SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB);
아래 결과에서 2개의 GROUPPING COLUMNS에 대하여 다음과 같은 3 LEVEL의 SUBTOTAL이 생성된 것을 볼 수 있다.
. GROUP BY로 부터 생성되는 표준 집계
. DNAME별 모든 JOB의 SUBTOTAL
. GRAND TOTAL
[다음은 위의 SQL를 수행한 결과를 표시한 것이다]
|
Dname |
JOb |
Total |
Average Salary |
|
ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING |
CLERK |
1 |
15600 |
|
MANAGER |
1 |
29400 | |
|
PRESIDENT |
1 |
60000 | |
|
All Jobs |
3 |
35000 | |
|
RESEARCH RESEARCH RESEARCH RESEARCH |
ANALYST |
2 |
36000 |
|
CLERK |
2 |
11400 | |
|
MANAGER |
1 |
35700 | |
|
All Jobs |
5 |
26100 | |
|
SALES SALES SALES SALES |
CLERK |
1 |
11400 |
|
MANAGER |
1 |
34200 | |
|
SALESMAN |
4 |
16800 | |
|
All Jobs |
6 |
18800 | |
|
All Departments |
All Jobs |
14 |
24878.5714 |
2.CUBE
ROLLUP에서는 단지 가능한 SUBTOTALS의 SUBSET만을 생성하였지만, CUBE는 DIMEMSION GROUP에 대하여 결합 가능한 모든 값에 대하여 cross-tabulation values를 생성하게 된다.
ROLLUP을 사용할 경우에는 GROUPPING COLUMNS의 순서를 바꾸어서 또 한번의 QUERY를 수행해야 한다. 뿐만 아니라 GRAND TOTAL은 양쪽의 QUERY에서 모두 생성이 되므로 한번의 QUERY에서는 제거되어야만 한다. 이처럼 GROUPPING COLUMNS이 가질 수 있는 모든 경우에 대하여 SUBTOTAL를 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하다.. ,
그러면, 다음과 같이 CUBE를 사용한 SQL문이 있다고 가정해 보자.
SELECT DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);
CUBE는 GROUPPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 SUBTOTAL를 생성하므로 GROUPPING COLUMNS의 수가 n 이라고 가정하면, 2n LEVEL의 SUBTOTAL를 생성하게 된다.
[다음은 CUBE를 사용한 SQL의 결과이다]
|
Dname |
Job |
Total |
Average Salary |
|
ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING |
CLERK |
1 |
15600 |
|
MANAGER |
1 |
29400 | |
|
PRESIDENT |
1 |
60000 | |
|
All Jobs |
3 |
35000 | |
|
RESEARCH RESEARCH RESEARCH RESEARCH |
ANALYST |
2 |
36000 |
|
CLERK |
2 |
11400 | |
|
MANAGER |
1 |
35700 | |
|
All Jobs |
5 |
26100 | |
|
SALES SALES SALES SALES |
CLERK |
1 |
11400 |
|
MANAGER |
1 |
34200 | |
|
SALESMAN |
4 |
16800 | |
|
All Jobs |
6 |
18800 | |
|
All Departments |
ANALYST |
2 |
36000 |
|
All Departments |
CLERK |
4 |
12450 |
|
All Departments |
MANAGER |
3 |
33100 |
|
All Departments |
PRESIDENT |
1 |
60000 |
|
All Departments |
SALESMAN |
4 |
16800 |
|
All Departments |
All Jobs |
14 |
24878.5714 |
3.GROUPING FUNCTION
이 FUNCTION은 ROLLUP과 CUBE와 같은 GROUP BY의 확장 SELECT 문에서만 사용되어질 수 있으며, FUNCTION에 사용되는 EXPRESSION은 GROUP BY에 지정된 EXPRESSION중 하나와 일치해야만 한다.
ROLLUP과 CUBE OPERATION에 GROUPING 함수를 꼭 사용해야 하는 것은 아니지만, 다음과 같은 문제점을 해결하기 위하여 만들어졌다고 할 수 있다.
첫번째는 SUBTOTAL를 프로그램적으로 구분할 수 없다는 것이며,
두번째는 주어진 SUBTOTAL의 정확한 집계 LEVEL를 구할 수 없다는 것이다.
RESULT SET은 ROLLUP이나 CUBE에 의하여 생성된 NULL과 stored NULL 모두를 가질 수 있다. 그러나 GROUPING 함수는 GROUP BY 에 의하여 생성된 STANDARD AGGREGATION 과 ROLLUP과 CUBE에 의하여 생성된 SUBTOTAL를 구분하기 위하여 NUMBER 타입으로 1의 값을 반환한다. 다시 말해 NULL이 SUBTOTAL를 나타낼 경우에 GROUPING 함수는 1를 반환하며, 그 외에 STANDARD AGGREGATION은 0을 반환한다.
다음 SQL를 통해 GROUPING 함수가 STANDARD AGGREGATION과 SUBTOTAL를 어떻게 구분하는지 알아 보도록 하자 .
SELECT DNAME,JOB,COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal",
GROUPING(DNAME) GRPDNAME,
GROUPING(JOB) GRPJOB
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);
[다음은 위의 SQL를 수행한 결과를 표시한 것이다]
|
Dname |
Job |
Total |
Average Salary |
Grp |
Grp |
|
ACCOUNTING ACCOUNTING ACCOUNTING |
CLERK |
1 |
15600 |
0 |
0 |
|
MANAGER |
1 |
29400 |
0 |
0 | |
|
PRESIDENT |
1 |
60000 |
0 |
0 | |
|
NULL |
3 |
35000 |
0 |
1 | |
|
RESEARCH RESEARCH RESEARCH RESEARCH |
ANALYST |
2 |
36000 |
0 |
0 |
|
CLERK |
2 |
11400 |
0 |
0 | |
|
MANAGER |
1 |
35700 |
0 |
0 | |
|
NULL |
5 |
26100 |
0 |
1 | |
|
SALES SALES SALES SALES |
CLERK |
1 |
11400 |
0 |
0 |
|
MANAGER |
1 |
34200 |
0 |
0 | |
|
SALESMAN |
4 |
16800 |
0 |
0 | |
|
NULL |
6 |
18800 |
0 |
1 | |
|
NULL |
NULL |
14 |
24878 |
1 |
1 |
4.RESTRICTIONS
ROLLUP과 CUBE는 사용하는데 있어 특별한 제한사항을 가지진 않는다. 이것은 GROUP BY의 확장으로 GROUP BY에서 사용할 수 있는 모든 FUNCTION을 사용할 수 있다.
1)COLUMN CAPACITY
ROLLUP과 CUBE에서 사용할 수 있는 GROUPPING COLUMNS의 수는 255까지 가능하다. 그러나 CUBE에 많은 COLUMNS을 지정하게 되면 생성되는 SUBTOTAL의 수(2n)가 폭발적으로 증가하여 시스템 RESOURCE를 극도로 사용할 수 있다. 때문에 지나치게 많은 COLUMN을 CUBE에 지정하는 것은 피해야 하며, 만약 이러한 상황이 발생한다면 시스템에 주는 부담과 성능을 테스트한 후에 적용을 하도록 해야 한다.
2)HAVING CLAUSE
HAVING 절은 ROLLUP과 CUBE에 영향을 받지 않는다. 이것은 RESULT SET의 SUBTOTAL과 NON SUBTOTAL에 모두 적용이 되며, 만약 SUBTOTAL과 NON SUBTOTAL을 구분하여 작업을 해야 한다면 GROUPING 함수를 사용하면 된다.
다음은 SQL은 ROLLUP에 HAVING 절을 적용한 예이다.
SELECT DECODE(GROUPING(dname),1,'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
HAVING GROUPING(DNAME) = 1 OR GROUPING(JOB) = 1;
[다음은 위의 SQL를 수행한 결과를 표시한 것이다]
|
Dname |
Job |
Total |
Average Salary |
|
ACCOUNTING |
ALL Jobs |
3 |
35000 |
|
RESEARCH |
ALL Jobs |
5 |
26100 |
|
SALES |
All Jobs |
6 |
18800 |
|
ALL Departments |
All Jobs |
14 |
24878 |
3)ORDER BY CLAUSE
ORDER BY 절은 ROLLUP이나 CUBE의 사용에 영향을 받지 않으며, 모든 RESULT SET에 적용이 된다. 우리가 원하는 순서로 RESULT SET을 정렬하기 위하여 GROUPING 함수를 ORDER BY 절에 사용할 수 있다.
다음은 SQL은 ORDER BY 절은 사용한 사례이다.
SELECT DECODE(GROUPING(dname),1,'All Departments',dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl",
AVG(sal) * 12 "Average Sal",
GROUPING(DNAME) GRPDNAME,
GROUPING(JOB) GRPJOB
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY GRPDNAME DESC,DNAME;
[다음은 위의 SQL를 수행한 결과를 표시한 것이다]
|
Dname |
Job |
Total |
Average Salary |
|
All Departments |
All Jobs |
14 |
24878.5714 |
|
ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING |
CLERK |
1 |
15600 |
|
MANAGER |
1 |
29400 | |
|
PRESIDENT |
1 |
60000 | |
|
All Jobs |
3 |
35000 | |
|
RESEARCH RESEARCH RESEARCH RESEARCH |
ANALYST |
2 |
36000 |
|
CLERK |
2 |
11400 | |
|
MANAGER |
1 |
35700 | |
|
All Jobs |
5 |
26100 | |
|
SALES SALES SALES SALES |
CLERK |
1 |
11400 |
|
MANAGER |
1 |
34200 | |
|
SALESMAN |
4 |
16800 | |
|
All Jobs |
6 |
18800 |
6.ROLLUP과 CUBE의 활용
ROLLUP과 CUBE OPERATORS를 PL/SQL 8.1.5 에서 사용하기 위해서는 Dynamic SQL를 사용해야만 한다. 이것은 아직까지 ROLLUP과 CUBE와 같은 향상된 SQL를 PL/SQL 8.1.5에서 지원하지 않는다는 것을 말한다. 만약 Dynamic SQL를 사용하지 않고 PL/SQL에서 ROLLUP과 CUBE를 사용하게 되면 ROLLUP과 CUBE를 단순한 변수로 인식하고 선언되지 않았다며 다음과 같은 에러 메시지를 나타내게 된다.
---------------------------------------------------------------------------
10/4 PL/SQL: SQL Statement ignored
13/13 PLS-00201: identifier 'ROLLUP' must be declared
ORACLE8i 이전 버전에서 PL/SQL에서 Dynamic SQL를 사용하기 위해서는 DBMS_SQL 패키지를 사용해야만 했다. 그러나 ORACLE8i 부터는 PL/SQL에서 직접 SQL 문장을 기술할 수 있는 Dynamic SQL를 도입 하였는데 이를 NATIVE Dynamic SQL이라고 한다.
Native Dynamic SQL를 사용 함으로서 PL/SQL에서 Dynamic SQL를 사용하기가 매우 용이하게 되었다. 여기에 대한 자세한 사항은 Oracle 8i PL/SQL User's Guide and Reference - Ch 10 Native Dynamic SQL를 참조하기 바란다.
그러면 PL/SQL 8.1.5에서 Native Dynamic SQL를 사용하여 ROLLUP과 CUBE를 어떻게 사용하는지 알아 보도록 하자. Native Dynamic SQL를 사용하기 위해서는 COMPATIBLE initialization parameter가 8.1.0 이상으로 설정이 되어있어야 하며, DBMS_OUTPUT.PUT_LINE의 결과를 보기 위해서 SET SERVEROUTPUT ON를 설정하면 된다.
다음 SQL은 ROLLUP과 CUBE를 PL/SQL 8.1.5에서 테스트 한 샘플이다.
CREATE OR REPLACE procedure test_rollup1 as
type curTyp is ref cursor;
sql_stmt varchar2(1000);
tab_cv curTyp;
my_deptno emp.deptno%type;
my_job emp.job%type;
my_sum int;
my_count int;
begin
sql_stmt := 'select deptno,job,sum(sal),count(*) ' ||
'from emp ' ||
'group by rollup(deptno,job)';
open tab_cv for sql_stmt;
loop
fetch tab_cv into my_deptno, my_job, my_sum, my_count;
exit when tab_cv%NOTFOUND;
dbms_output.put_line (my_deptno || ' '||nvl(my_job,' ') ||
' ' || my_sum || ' ' || my_count);
end loop;
close tab_cv;
end;
'SQL > ORACLE' 카테고리의 다른 글
| [펌] 오라클 nls_lang (0) | 2014.02.13 |
|---|---|
| [펌]인덱스 (0) | 2014.02.13 |
| [Oracle]토드(toad)를 이용한 데이터베이스 튜닝하기 (0) | 2014.02.13 |
| [펌] exists 및 not exists 연산자 (0) | 2014.02.13 |
| [펌] 오라클 > IMPORT and EXPORT (0) | 2014.02.13 |
| [펌] 제약조건(Constraint) (0) | 2014.02.13 |
| BIN$+/CQaefAAlDgNAADuiNeQw==$0 요런 테이블이 자동으로 생깁니다 (0) | 2014.02.13 |
| [펌] PL/SQL 에서 dbms_output.put_line으로 출력하기 (0) | 2014.02.13 |
nineDeveloper
안녕하세요 현직 개발자 입니다 ~ 빠르게 변화하는 세상에 뒤쳐지지 않도록 우리모두 열심히 공부합시다 ~! 개발공부는 넘나 재미있는 것~!