Managing Schema Objects-2

● 개요

  - 스키마란 무엇인가?
  - 데이터 타입은 무엇이고, 종류는 어떤 것들이 있는가?
  - 테이블의 생성과 수정은 어떻게 하는가?
  - 제약을 정의하는 방법은?
  - 컬럼과 테이블의 내용을 보자
  - 인덱스는 어떻게 생성하는가?
  - 뷰는 어떻게 생성하는가?
  - 시퀀스는 어떻게 생성하는가?
  - 템프테이블은 어떻게 사용하는가?
  - 데이터 딕셔너리를 사용할 수 있다.

● 인덱스 (index)
  ◎ index의 정의
     - ROWID를 전문적으로 보관하는 객체

  ◎ index의 타입
    ○ B-tree index
       - Balanced Index
       - root block, branch block, leaf block이 존재
       - branch block은 데이터의 양에 따라 여러 개가 존재할 수 있다.
       - 자주 데이터가 바뀌는 테이블에는 index를 만들지 않는 것이 좋다.
       - 어떠한 데이터를 찾아도 같은 속도가 보장.
          : 동등 질의(예) where empno = 1), 범위 질의(예) where empno > 46 and empno <= 86)
       - 성장 : leaf block -> branch block -> root의 순으로 커진다.
       - 유일한 값이 많은 컬럼에 주로 쓰인다. (OLTP환경에 많이 쓰인다.)

       - 유지보수

SQL> alter index 인덱스명 coalesce;
SQL> alter index 인덱스명 rebuild;

    ○ bitmap index
       - 유일값이 적은, 즉, 중복값이 많은 컬럼에 주로 쓰인다. (성별, 연령대 등)
       - null값도 보관
       - 전체 데이터 중 유일값이 1%이하면 bitmap인덱스로 만들어도 됨
       - DML에 취약하다. -> 광범위한 lock이 걸림
         (bit단위가 아닌 bitmap segment 단위(index block의 반정도)로 트랜잭션 lock을 건다.)
       - OLAP환경에 주로 쓰임.

SQL> create bitmap index sales_gender_bdix on sales(gender);
SQL> create bitmap index sales_age_bdix      on sales(age);

      - 참고 : Bitmap Index의 크기문제
                 Bitmap Index vs Btree Index

    ○ reverse key index
       : Byte단위로 값을 뒤집어서 저장한다.
         7001 -> 1007, 7002 -> 2007

       - 장점
          : 9:1 split이 줄어든다. 
            트랜잭션 경합을 줄일 수 있다.
            동등질의에만 사용
       - 단점 
          : 5:5 split이 늘어남.
            범위 질의에는 좋지않다.

SQL> create index t23_c1_idx on t23(c1) reverse;

  ◎ index의 옵션
      - compress
         : 2종류 : 손실압축, 패턴 치환
           io와 메모리에 효율적, 경합은 늘어날 가능성 있음
        - nosegment
        - invisible

        참고 : nosegment index vs invisible index
      
  ◎ EM을 이용한 index의 생성 및 관리
        - Database Instance > Indexes

● 뷰 (view)
  ◎ view의 정의
        - named select
        - 뷰에 대한 질의는 테이블에 대한 질의로 transform된다.

  ◎ EM을 이용한 view의 생성 및 관리
        - Database Instance > Views

● 시퀀스 (sequence)
  ◎ sequence의 정의

  ◎ EM을 이용한 sequence의 생성 및 관리
        - Database Instance > Sequences

  ◎ sequence의 이용

● 템프 테이블 (temporary table)
  ◎ temporary table의 정의
        - 세션이나 트랜잭션이 끝나면 자동으로 없어지는 장소를 제공한다.
        - temporary table에 데이터를 넣으면 redo가 남지 않는다.
        - commit시 데이터가 삭제된다. 

SQL> create global temporary table employees_temp
         on commit preserve rows
         as select * from employees;

  ◎ temporary table에 관한 고찰

● 데이터 딕셔너리 뷰 (data dictionary view)
  ◎ data dictionary view의 정의
     - 데이터베이스에 대한 설명서이다.

  ◎ data dictionary view의 구분

  ◎ data dictionary view의 예

     연관 포스트 : http://aozjffl.tistory.com/admin/entry/edit/183


기타참고사항

테이블 vs 인덱스

create table t23(c1 number)
pctfree 10                          -- update 여유 공간
pctused 40                        --  insert 가능 시점
initrans 2                           -- 최초 tx slot 갯수 : 최소 동시성 보장
maxtrans 5                        -- 최대 tx slot 갯수 : 최대 동시성 제한 (10g 부터는 안됨 오라클이 알아서함)
storage(initial      250k         -- 최초로 할당받는 extent의 크기
            next       250k         -- 두번째 부터 할당받는 extent의 크기
            minextents   3
            maxextents 10
            pctincrease  0)
tablespace users5;

block space utilization parameter
 : pctfree, pctused, initrans, maxtrans

storage(..) 는extents관련

create index t23_c1_idx on t23(c1)
pctfree 10                             -- insert용 여유공간
initrans 2                              -- 최초 tx slot 갯수 : 최소 동시성 보장
maxtrans 5                           -- 최대 tx slot 갯수 : 최대 동시성 제한 (10g 부터는 안됨 오라클이 알아서함)
storage(initial      250k            -- 최초로 할당받는 extent의 크기
            next       250k            -- 두번째 부터 할당받는 extent의 크기
            minextents   3
            maxextents 10
            pctincrease  0)
tablespace users5;

pctused 셋팅 못함

관련 내용 : 오라클 교재 Administration Workshop I 7장
INDEX 크기관련 동영상 : 1. index 크기관련 part1
                                   2. index 크기관련 part2
                                   3. index 크기관련 part3
INDEX SPLIT 관련 동영상 : 1. index split part1
                                      2. index split part2
                                      3. index split part3