Managing Schema Objects

● Tables

	Table	Segment
Heap	1	1
Partition	1	N
Cluster	N	1
IOT	1	N

cf. Table(LOB)                                                1                                                                 N

   ◎ (Heap Oraganized) Table
        : 테이블이 하나면 segment가 하나
   ◎ Partitioned Table 
        (참고1 : Learn How to Partition in oracle9i release 2, 참고2 : 파티션의 이해와 활용)
        : 실제 테이블은 하나이지만, 입력되는 데이터를 파티션으로 분류할 수 있는 테이블.
         파티션은 당 하나의 테이블 스페이스를 지정할 수 있다.
         테이블이 하나면 여러개의 extent를 가짐

         ○ range (8~)
             각 파티션의 크기에 차이가 많이 있을 경우, 병렬처리시에 문제 발생

             실습

-------------------   range partitioned table 생성 / 데이터 입력 -------------------
SQL> drop table t1 purge;

SQL> create table t1
         (col1 number,
          col2 number,
          flag varchar2(1) constraint t1_flag_ck check(flag in ('A', 'B', 'C', 'D'))
         )
        partition by range (flag)
        (partition p1 values less than ('B') tablespace ts1,                               -- p1 파티션에는 B 미만 즉 A만 입력됩니다.
         partition p2 values less than ('C') tablespace ts2,                               -- p2 파티션에는 C 미만 즉 B만 입력됩니다.
         partition p3 values less than ('D') tablespace ts3,
         partition p4 values less than (maxvalue) tablespace ts4
        );

SQL> insert into t1 values (1, 1, 'K');     -- ORA-02290: check constraint (SYS.T1_FLAG_CK) violated
SQL> insert into t1
         select level, level, decode(trunc(level/250), 0, 'A', 1, 'B', 3, 'C', 'D')
         from dual
        connect by level <= 1000;

-------------------------------   확인 ---------------------------------

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'T1')
SQL> set autot on
SQL> select * from t1 where flag = 'A';
SQL> select * from t1 where flag = 'B';
SQL> select * from t1 where flag in ('B', 'C');
SQL> select * from t1 partition (p2);
SQL> alter tablespace ts2 offline;
         select * from t1 where flag = 'B';
SQL> select * from t1 where flag = 'C';
SQL> select *
         from dict
         where table_name like '%PART%';
SQL> select *
         from dba_tables
         where owner = 'SCOTT'
         and table_name = 'T1';
SQL> select *
         from DBA_TAB_PARTITIONS
         where table_owner = 'SCOTT'
         and table_name = 'T1';
 

         ○ hash (8i~)
             있는 값 그대로가 아닌 어떤 연산을 하여 나온 결과로 분류
             hashing이란 : 불특정 다수의 데이터가 몇개 안되는 것으로 분류되는 것.

             실습

------------------------   hash partitioned table 생성 / 데이터 입력 -----------------------------

SQL> alter tablespace ts2 online;

SQL> drop table t1 purge;

SQL> create table t1
         (col1 number,
          col2 number,
          flag varchar2(1) constraint t1_flag_ck check(flag in ('A', 'B', 'C', 'D'))
         )
         partition by HASH (flag)
         (partition p1 tablespace ts1,
          partition p2 tablespace ts2,
          partition p3 tablespace ts3,
          partition p4 tablespace ts4
         );

SQL> insert into t1
         select level, level, decode(trunc(level/250), 0, 'A', 1, 'B', 3, 'C', 'D')
         from dual
        connect by level <= 1000;

-------------------------------   확인 ---------------------------------

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'T1')

SQL> set autot on

SQL> select * from t1 where flag = 'A';
SQL> select * from t1 where flag = 'B';
SQL> select * from t1 where flag = 'C';
SQL> select * from t1 where flag = 'D';

SQL> select * from t1 partition (p2);

         ○ list (9i)

----------------------   list partitioned table 생성 / 데이터 입력 ---------------------------
SQL> drop table t1 purge;

SQL> create table t1
         (col1 number,
          col2 number,
          flag varchar2(1) constraint t1_flag_ck check(flag in ('A', 'B', 'C', 'D', 'E', 'F' , 'G', 'H', 'I'))
         )
         partition by LIST (flag)
         (partition p1 values ('A', 'B')      tablespace ts1,
          partition p2 values ('C', 'D', 'E') tablespace ts2,
          partition p3 values ('F')           tablespace ts3,
          partition p4 values (default) tablespace ts4
         );

SQL> insert into t1
         select level, level, decode(trunc(level/250), 0, 'A', 1, 'B', 3, 'C', 'D')
         from dual
         connect by level <= 1000;

SQL> insert into t1
        select level, level, decode(trunc(level/200), 0, 'E', 1, 'F', 3, 'G', 4, 'H', 'I')
        from dual
        connect by level <= 1000;

---------------------------------   확인 ------------------------------------

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'T1')

SQL> set autot on

SQL> select * from t1 where flag = 'A';
SQL> select * from t1 where flag IN ('C', 'D', 'E')
SQL> select * from t1 where flag = 'C';
SQL> select * from t1 where flag = 'D';

SQL> select * from t1 partition (p2);

         ○ range + hash
             : range partiotion과 hash partition의 조합

-----------------   range + hash paritioned table 생성 / 데이터 입력 --------------------------

SQL> create tablespace ts5 datafile '/u01/app/oracle/oradata/orcl/ts5.dbf' size 10m;
SQL> create tablespace ts6 datafile '/u01/app/oracle/oradata/orcl/ts6.dbf' size 10m;
SQL> create tablespace ts7 datafile '/u01/app/oracle/oradata/orcl/ts7.dbf' size 10m;
SQL> create tablespace ts8 datafile '/u01/app/oracle/oradata/orcl/ts8.dbf' size 10m;

SQL> drop table t1 purge;

SQL> create table t1
        (col1 number,
         col2 number,
         flag varchar2(1) constraint t1_flag_ck check(flag in ('A', 'B', 'C', 'D', 'E', 'F' , 'G', 'H', 'I')))
         partition by range (col1)
         subpartition by hash (flag)
        (partition p1 values less than (1001)
           (subpartition sub1 tablespace ts1,
            subpartition sub2 tablespace ts2,
            subpartition sub3 tablespace ts3,
            subpartition sub4 tablespace ts4),
         partition p2 values less than (maxvalue)
           (subpartition sub5 tablespace ts5,
            subpartition sub6 tablespace ts6,
            subpartition sub7 tablespace ts7,
            subpartition sub8 tablespace ts8)
        );

SQL> insert into t1
         select level, level, decode(trunc(level/250), 0, 'A', 1, 'B', 3, 'C', 'D')
         from dual
         connect by level <= 2000;

---------------------------------   확인 ------------------------------------

SQL> set autot on
SQL> select * from t1 partition (p1);
SQL> select * from t1 partition (p2);

SQL> select * from t1 subpartition (sub1);
SQL> select * from t1 subpartition (sub5);

         ○ range + list
             : range partition과 list partition의 조합

 
   ◎ Index Organized Table
      : 인덱스가 필요없어지는 테이블
          primary key 기준으로 데이터 찾을 경우 빠름
          스토리지 적게 소비(인덱스에 대한 스토리지가 필요없으므로)
          물리적인 Rowid 대신 논리적인 RowID를 갖는다.
        예) 학생테이블, 과목 테이블은 N:N이므로 중간에 교차 entity(수강내역테이블)을 추가함에 따라
             1:N 의 관계로 만들어줌. 이때 수강내역 테이블의 primary key가 stu_id, class_id여여만함
             인덱스가 필요없어지는 테이블
             데이터가 주소 뒤에 컬럼과 데이터가 들어있음.

           ○ 실습

create table students (id number primary key, name varchar2(10));
create table classes (id number primary key, name varchar2(10));

- 수강 내역 관리 : 첫번째 방법 -

create table registers
(stu_id number references students(id),
 class_id number references classes(id),
 grade varchar2(1));

create index registers_pk_idx on registers (stu_id, class_id);

alter table registers add primary key(stu_id, class_id);

-- 수강 내역 관리 : 두번째 방법

drop table registers purge;

create table registers
(stu_id number references students(id),
 class_id number references classes(id),
 grade varchar2(1),
 primary key(stu_id, class_id))
 organization index;

   ◎ Clustered Table
         1. index cluster
            :

1. cluster 생성

create cluster ed_clu
(deptno number(2))
size 1k                     -- 중요한 결정사항
tablespace users;

if) 블럭 사이즈 8k - size가 1k이면, cluster가 7개 정도 들어감.
if) 블럭 사이즈 8k - size가 13k이면, cluster가 하나도 안들어갈까?
  --> cluster는 논리적 개념임.

2. cluster용 index 생성

create index ed_clu_idx
on cluster ed_clu;

3. table 생성 (...)

create table n_emp
cluster ed_clu(deptno)
as
select * from emp;

rename emp   to old_emp;
rename n_emp to emp;

create index emp_deptno_idx on emp(deptno);

         2. hash cluster
            : 특정 함수를 사용하여 hashing함
              범위 질의일 경우 사용할 수 없음.

(1) cluster 생성
create cluster emp_h_cluster
(empno number(4))
size 1k
hashkeys 100                    -- 오라클이 알아서 함수를 알아서 만들어 hashing함
--hash is mod(empno, 1000)
tablespace users;

(2) table 생성(...)

create table h_emp
cluster emp_h_cluster(empno)
as
select * from emp;

alter table h_emp add contraint h_emp_empno_pk primary key (empno);

select * from h_emp
where empno = 7788;

         3. sorted hash cluster(10g~)
            : Cluster Key 를 Hashing 하여 저장한것과 각 Hashing 값들에 대해 정렬된 값들을 합친 구조

create cluster emp_cluster
(deptno number(2),
 sal    number sort)
 hashkeys 4
 hash is deptno
 size 50;

create table emp1
(empno number,
 ename varchar2(30),
 sal   number,
 deptno number(2))
cluster emp_cluster(deptno, sal);

insert into emp1
select empno, ename, sal, deptno from emp;

set autot on

select * from emp1;

select * from emp1
where deptno = 10
order by deptno, sal;                       

select * from emp1
where deptno = 20
order by deptno, sal;

참조 : http://www.urbantree.wo.tc/entry/10-Managing-Schema-Objects

size?
growth trend
optimizer statistics
reorganization
 - 9i : dbms_redefinition 패키지
         http://kr.forums.oracle.com/forums/thread.jspa?threadID=453730
 - 10g : EM
 



기타 참고사항

- 데이터를 빨리 찾게 하기 위한 방법
  1. index를 이용
  2. 해싱기법을 이용
  3. partitioned table

- partition prunning
  : 조건에 맞는 partition만 scan

- AWR에는 segment의 공간이 성장하는 패턴도 들어있음.