Bohemian life

alter session set time_zone = '+3:0';

select dbtimezone, sessiontimezone from dual;

alter session set time_zone = dbtimezone;

select dbtimezone, sessiontimezone from dual;

alter session set time_zone = local;

select dbtimezone, sessiontimezone from dual;

alter session set time_zone = 'Asia/Seoul';

select dbtimezone, sessiontimezone from dual;

* 현재 세션의 타임 존 설정

    alter session set time_zone = 1) an absolute offset: '+09:00'

 2) database time zone: dbtimezone

 3) OS local time zone: local

 4) a named region    : 'Asia/Seoul'  <= v$timezone_names 질의

alter session set time_zone = '-18:00';

==> ERROR: ORA-01874: 시간대의 시간은 -12와 14 사이여야 합니다.

   cf.) alter system set time_zone  : (X)

alter database set time_zone: (O) --> 1) 데이터베이스의 타임존 설정

    2) 성능상 이유로 '+00:00'으로 설정이 권장됨

    3) TIMESTAMP WITH LOCAL TIME ZONE 타입의 데이터가 저장되어 있을           

  경우 바로 변경 불가

SELECT   department_id, job_id, manager_id 

      , SUM(salary)

      , grouping(department_id) as grp_d

      , grouping(job_id) as grp_j

      , grouping(manager_id) as grp_m

FROM     employees

GROUP BY department_id, --> department_id  --> 1개

        ROLLUP(job_id), --> job_id, ()   --> 2개 => 1*2*2=4개의 group by 절이 생성된다고 생각

        CUBE(manager_id); --> manager_id, () --> 2개

  ==> GROUP BY department_id, job_id, manager_id

      + GROUP BY department_id, job_id, () ==> department_id, job_id

      + GROUP BY department_id, (), manager_id ==> department_id, manager_id

      + GROUP BY department_id, (), () ==> department_id

- A cross-product of groupings from each grouping set

    * GROUP BY grouping sets (a, b), grouping sets (c, d)

=   GROUP BY a, c ┐ 

 + GROUP BY a, d ├ cross-product of (a, b) and (c, d)

 + GROUP BY b, c │

 + GROUP BY b, d ┘ 

  ==> http://goo.gl/a9DJ4

GROUP BY grouping sets(rollup(a, b, c))

==> GROUP BY grouping sets( (a, b, c), (a, b), (a), () )

    ex) select deptno, job

from emp

group by rollup(deptno, job)

order by 1, 2;

select deptno, job

from emp

group by grouping sets(rollup(deptno, job))

order by 1, 2;

select deptno, job

from emp

group by grouping sets( (deptno, job), deptno, ())

order by 1, 2;

  - Group By 절의 Rollup, Cube, Grouping Sets 에서 사용

  - 여러 컬럼을 하나의 컬럼처럼 여기도록

  - 괄호로 여러 컬럼을 묶음

SELECT   department_id, job_id, manager_id, 

        SUM(salary),

        grouping(department_id) as GRP_D,

        grouping(job_id) as GRP_J,

                 grouping(manager_id) as GRP_M

FROM     employees

GROUP BY ROLLUP( department_id,(job_id, manager_id));

  cf.) composite column이 아닌 경우

SELECT   department_id, job_id, manager_id, 

        SUM(salary),

        grouping(department_id) as GRP_D,

        grouping(job_id) as GRP_J,

                 grouping(manager_id) as GRP_M

FROM     employees

GROUP BY ROLLUP( department_id, job_id, manager_id);

  - 해당 컬럼의 값으로 그룹을 짓는데 사용되었는지와 NULL 값의 의미 판단

     해당 컬럼의 값 | Grouping 값 |       의  미

    -----------------------------------------------------------

         not NULL   |      0      | 그룹 짓는데 사용됨

                    |      1      |       "     사용되지 않음

    -----------------------------------------------------------

           NULL     |      0      | 데이터 NULL

                    |      1      | 그룹 짓는데 사용안된 컬럼

select deptno, mgr, sum(sal),

      grouping(deptno) as GRP_D, grouping(mgr) as GRP_M

from emp

group by rollup(deptno, mgr);

 1) rollup

  select deptno, job, sum(sal) as sum_sal

  from emp

  group by rollup(deptno, job)

  order by 1, 2;

↓ ↓ ↓

  select deptno, job, sum(sal) as sum_sal

  from emp

  group by grouping sets((deptno, job), (deptno), ())

  order by 1, 2;

  2) cube

  select deptno, job, sum(sal) as sum_sal

  from emp

  group by cube(deptno, job)

  order by 1, 2;

↓ ↓ ↓

  select deptno, job, sum(sal) as sum_sal

  from emp

  group by grouping sets((deptno, job), (deptno), job, ())

  order by 1, 2;

- 컬럼에 대해 임의의 그룹을 지음

    - ()

  - Union All 로 똑같은 결과를 얻을 수 있으나 보다 효율적임

select deptno, job, sum(sal) from emp

group by rollup(deptno, job);

  ↓↓

select deptno, job, sum(sal) from emp

group by deptno, job

union all

select deptno, to_char(null), sum(sal) from emp

group by deptno

union all

select to_number(null), to_char(null), sum(sal) from emp

group by ()

order by 1, 2;

    - 컬럼이 짝을 이룰 수 있는 모든 경우에 대해 그룹을 지음

    - 컬럼의 갯수가 n 개면 결과는 2^n 개 => n-cube

    - 컬럼의 나열 순서는 중요하지 않음

# Rollup, Cube, Grouping Sets

  - 컬럼에 대해 그루핑을 수행

  - Group By 절에 사용

  - Union All 로 똑같은 결과를 얻을 수 있으나 보다 효율적임

  - SELECT의 확장: VERSIONS ... BETWEEN ... 절

  - 데이터의 변경 이력 확인

  - Version Query Pseudocolumns

     : versions_starttime, versions_startscn

       versions_endtime,   versions_endscn

drop table c_emp purge;

create table c_emp as select * from emp;

select empno, ename, sal from c_emp

where empno = 7369;

    EMPNO ENAME             SAL

---------- ---------- ----------

     7369 SMITH             800

update c_emp

set sal = 8000

where empno = 7369;

commit;

----------

update c_emp

set sal = 1000

where empno = 7369;

commit;

----------

update c_emp

set sal = 300

where empno = 7369;

update c_emp

set sal = sal * 1.5

where empno = 7369; --> 한 Tx내에서 여러번의 DML

commit;

----------

update c_emp

set sal = sal + 1000

where empno = 7369; --> commit 하지 않음

select empno, ename, sal from c_emp

where empno = 7369;

    EMPNO ENAME             SAL

---------- ---------- ----------

     7369 SMITH            1450

select sal from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

      SAL

----------

      450

     1000

     8000

      800

commit;

select sal from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

      SAL

----------

     1450

      450

     1000

     8000

      800

  * flashback versions query pseudocolumns

col versions_starttime format a20

col versions_endtime   format a20

select versions_starttime as start

    , versions_endtime as end

    , sal as salary

from c_emp

 versions between scn minvalue and maxvalue

where empno = 7369;

oj_SQL.txt

  * MERGE┌ INSERT OR UPDATE

         └ INSERT OR DELETE

  - deterministic

  - WHEN MATCHED THEN

    WHEN NOT MATCHED THEN

- 교재 참조