Neo-Oracle Home

1. rollup을 grouping sets로 바꿔본 예

-rollup

select manager_id, job_id, sum(salary)
from employees
where manager_id is not null
and manager_id < 120
group by rollup(manager_id, job_id);

-rollup to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets (
                            (manager_id, job_id),
                            (manager_id),
                            ()
                          );

2. cube를 grouping sets로 바꿔본 예

-cube

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by cube(manager_id, job_id);

-cube to grouping sets

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          );

그런데 정렬이 동일하지 않다. 그래서 정렬도 추가한다. 단 null값은 기본적으로 오름차순시 가장 큰 값 위치로 정렬되기에 이를 바꾼다.

select manager_id, job_id, sum(salary)
from em
where manager_id is not null
and manager_id < 120
group by grouping sets(
                          (),
                          (job_id),
                          (manager_id),
                          (manager_id, job_id)
                          )
order by 1 nulls first, 2 asc nulls first;

한번 grouping sets을 터득해보니 cube와 rollup보다 자유로워진 느낌이다.
 

자주 헷갈리는 제약조건 지연에 대해 적어본다.

- 기능 : 기본적으로 제약조건이 걸린 컬럼에 대한 DML 수행시 한건씩 제약조건을 검사하며 제약조건 위반시 statement level rollback되는데

제약조건에 deferred를 설정하면 이를 한건씩이 아닌 commit 시에 트랜잭션 단위로 제약조건을 검사하고 제약조건 위반시 자동으로 트랜잭션을 rollback하는 기능

- 언제 쓰나? : 참조무결성 제약조건이 설정된 테이블들에 DML을 하려고 할 경우. 예를 들어 사원테이블의 부서번호를 60번에서 65번으로 바꿔야 하는데 부서테이블에는 60번만 존재할경우 부서테이블이나 사원테이블 어느 한군데서 update를 치면 FK 제약조건(걸려있으면)에 위배되게 된다. 이때 사원테이블의 FK를 deferred 시키고 진행하고 commit 하면 되겠다.

DEFERRABLE | NOT DEFERRABLE

- deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하며 not deferrable이 기본값이다.

- 애초에 deferrable이 설정 되어 있어야 그 아래 단계인 deferred와 immeidiate를 변경 가능하게 된다.

- 생성시 구문. 컬럼 레벨 테이블 레벨, alter 등 방법을 다 적기엔.. 밤이 늦었기에 alter table 기준으로 설명

SQL> create table dept2 as select * from departments;

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable;

SQL> select status, deferrable, deferred
            from user_constraints where constraint_name ='DEPT2_ID_PK';

STATUS   DEFERRABLE     DEFERRED
-------- -------------- ---------
ENABLED  DEFERRABLE     IMMEDIATE

DEFERRED | IMMEDIATE

- 기본 값은 immediate이며, 만약 deferrable 상태가 not deferrable인데 deferred로 변경하면 아래와 같은 에러가 나온다.

SQL Error: ORA-02447: cannot defer a constraint that is not deferrable

에러 해설을 찾아보면

02447. 00000 -  "cannot defer a constraint that is not deferrable"
*Cause:    An attempt was made to defer a nondeferrable constraint
*Action:   Drop the constraint and create a new one that is deferrable

action에 제약조건을 drop하고 새로운걸 만들면서 deferrable로 설정하라고 한다.

위에서 말했듯이 deferrable 과 not deferrable은 constraint(제약조건)를 지정(생성) 할때만 설정가능하다는 것을 여기서도 알 수 있다.

- 방법. 제약조건 생성때 아예 아래처럼도 가능하다.

SQL> alter table dept2

add constraint dept2_id_pk

primary key (department_id)

deferrable initially deferred;

- 방법2 set constraints 사용. 이 방법이 가장 많이 쓰일 것이다.

SQL> set constraints dept2_id_pk deferred;

SQL> set constraints dept2_id_pk immediate;

- 방법3 alter session set constraints 사용. 그런데 테스트 해보니 결국 constraints의 속성에 따라 가기에 의미가 없어보인다. 아니면 약간 다른 용도로 사용하는 것도 같다.

SQL> alter session set constraints = immediate;

SQL> alter session set constraints = deferred;

평균의 신뢰도를 파악하기 위해 분산//혹은 표준편차를 구한다.

관련한 오라클 함수는 아래와 같다.

variance(=var_samp) : 표본분산

stddev(=stddev_samp) : 표본표준편차

var_pop : 모집단 분산

stddev_pop : 모집단 표준편차

분산을 먼저 구하고 표준편차를 구함

사례수가 20이고 평균이 8775 이며, 편차들의 제곱의 합은 608597500 이다.

모집단(전체) 분산과 모집단(전체) 표준편차

- 모집단 분산: 편차의 제곱의 합을 사례수로 나눈다. => 608597500/20 = 30429875

- 모집단 표준편차: 모집단분산을 제곱근한다. => 30429875^(1/2) = 5516.328036

표본(샘플)분산과 표본(샘플)표준편차

- 표본 분산: 편차의 제곱의 합을 사례수-1로 나눈다. => 608597500/19 = 32031447.37

- 표본 표준편차: 표본분산을 제곱근한다. => 32031447.37^(1/2)=5659.633148

샘플분산과 샘풀표준편차는 만약 이 사례수가 200명 중 20명을 샘플로 추출한 것이라면 20이 아닌 '자유도' 즉 N-1 개념이 들어가야 한다. 통계개념.