10g Workshop 7장

1. Schema 정의
   1) 개별 유저가 소유하는 데이터베이스 객체의 집합
   2) 사용자와 동일한 이름을 가짐 Ex) scott 사용자=scott 스키마
   3) 스키마와 테이블 스페이스 간에는 연관성 없음 : 하나의 테이블스페이스에 두개 이상의 스키마가 존재할수도 있고 두개 이상의 테이블스페이스에 하나의 스키마가 존재 할 수도 있음
   4) 데이터 베이스 생성시 생성되는 스키마

sys 스키마	데이터베이스 안의 모든 기본 table/view/data dictionary 를 sys 스키마 안에서 구성 Create any table 권한 소유   ※ data dictionary   - 데이터베이스 안의 유저/스키마/객체 에 대해서 묘사하고 수집하며, 수정/삭제/변경 시 갱신(system tablespace에 존재한다.)   - 읽기 모드로 사용가능
system 스키마	관리자 정보에 지속적으로 table/view 생성 내부 테이블/뷰가 여러가지 오라클 옵션과 툴에 의해서 지속적으로 생성 <사용자가 생성할 필요가 없음>

2. Naming Database Object
  1) Default : 1 ~ 30 bytes
  2) 예외
    ① Database Name : 8bytes
    ② Database link Name : 128 bytes
  3) 주의사항 
    ① 오라클 예약어 사용 불가
    ② object명 지정 시, " "(인용부호) 사용 자제 : select 등 수행 시에도 " "(인용부호) 사용해야 함
        " "(인용부호) 없이 지정 시, 모두 대문자로 변환 되며 대소문자 구분하지 않음
    ③ 사용가능한 특수문자 : _, $, # ($, # : " "(인용부호)를 사용하지 않고 지정한 object 명에서는 사용을 권장하지 않음)
         데이터베이스 링크의 경우 : . 과 @ 포함 가능

3. 데이터유형

데이터 유형	설                                       명
Char(size byte|char)	Size의 고정길이 문자 데이터
Varchar2(size byte|char)	최대길이가 size인 가변길이 문자열
Date	기원전 4712년 1월 1일부터 서기 9999년 12월 31일까지의 유효 날짜.
Number(p,s)	전체 자릿수가 p이고 소수점 이하 자릿수가 s인 숫자
Float(p)	이진수 전체 자릿수가 p인 부동소수점 수 (기본 전체 자릿수는 이진수 126 /십진수 38임.)
integer	Number와 동일
Nchar(size)	유니코드 전용데이터 유형 열 길이는 문자임. 최대길이 2000byte 고정길이 문자열
Nvarchar2(size byte|char)	유니코드 전용데이터 유형 열 길이는 문자임. 최대길이 4000byte 가변길이 문자열
LOGN	최대 2GB 가변길이 문자 데이터 logn형의 경우 같은 extent공간에 존재하게 되어 데이터의 엑세스 효율성을 저하 시켜서 좋지 않아 lob 타입을 지원함.
Long raw	최대 2GB 가변길이 원시 이진데이터
Raw(size)	Size 바이트 길이의 원시 이진데이터 최대 크기는 2000byte
Rowid	해당 테이블의 고유한 행 주소를 나타냄 64-진수 문자열
Urowid	인덱스 구성 테이블에 있는 행의 논리 주소를 나타내는 64-진수 문자열
BLOB	Binary large object를 의미함.
Clob	Character large object 를 의미함, 단일바이트 멀티바이트 문자 포함 고정너비 가변너비 모두 지원
NCLOB	유니코드 문자를 포함 하는 CLOB 동일
BFILE	데이터베이스 외부에 저장된 대형 binary File에 대한 위치자를 포함합니다. 이를 통해 바이트 스트림 I/O 데이터 베이스 서버에 이/t는 외부 LOB에 액세스할수 있다. 최대 크기는 4GB이다.
TIMESTAMP(size)	날짜값과 시간값을 지정 SIZE는 1~9이고 기본값은 6이며 이는 초의소수부분자릿수임.

4. 제약조건
   1) 개요 : 무결성 제약 조건을 사용하여 열 입력 값에 제한을 둠
       ① 종류

NOT NULL	기본적으로 테이블의 모든 열은 NULL 허용 NULL은 값이 없음을 의미 NOT NULL은 NULL 을 허용하지 않음
UNIQUE KEY	하나의 열 또는 여러열그룹 값이 고유해야 함. NULL을 허용함. 생성시 자동 인덱스 생성됨
PRIMARY KEY	1. NULL을 허용하지 않고, 해당 열에서 유일한 값이어야 함. 2. 생성시 자동 인덱스 생성됨 : PK이름 지정하지 않으면 시스템에서 자동 부여 3. PK 명 지정 문법 : tablespace까지 지정 가능 【 alter table ~ add constraints PK_NAME (pk_columns) tablespace ~ using index (local) ; 】
참조 무결성 제약 조건	관계형 데이터베이스에 있는 서로 다른 테이블을 공통열로 연결 가능 : 열의 관계를 통제하는 규칙이 유지 되어야 함 FOREIGN KEY : 자식테이블은 부모 테이블(해당열)에 존재하는 코드가 없을 경우 생성 될 수 없음
CHECK 제약 조건	열 또는 열 집합의 CHECK 무결성 제약 조건은 테이블의 모든 행에 대해 지정된 조건이 TRUE이거나 알 수 없음이어야 함.

       ② 제약조건 확인 view : DBA_CONSTRAINTS / DBA_CONS_COLUMNS
    ③ 제약 조건의 상태(비활성화)

 

1. 개요   1) 무결성 제약 조건은 활성화(ENABLE) 혹은 비활성화(DISABLE) 가능   2) 제약 조건 활성화 : 데이터베이스에서 데이터가 입력 또는 갱신될 때 검사   3) 제약 조건 규칙을 따르지 않는 데이터 입력 불가   4) 제약 조건 비활성화 : 규칙을 따르지 않는 데이터를 데이터베이스에 입력 가능
DISABLE NOVALIDATE	제약 조건 검사하지 않음.
DISABLE VALIDATE	제약조건이 있는 열을 수정할 수 없음 제약 조건에 대한 인덱스는 삭제(deferrable 인덱스 삭제 안됨)되고 제약조건은 비활성화 됨
ENABLE NOVALIDATE	새 데이터 : 제약조건을 위배 할 수 없음 기존 데이터 : 제약조건을 위배하는 데이터는 포함 할 수 있음               <OLTP 에 유용>
ENABLE VALIDATE	default state 새 데이터 : 제약조건을 위배 할 수 없음 기존 데이터 : 제약조건에 위배되는 데이터를 포함할 수 없음

 
    ④ Nondeferred Constraint(immediate constraint) DML문을 수행한 직후 데이터에 대한 무결성 검사를 하여 조작한 데이터가 제약조건에 맞지 않을 경우 바로 에러 발생
    ⑤ Deferred Constraint : Dml수행한 직후 제약 조건을 검사하지 않는 제약조건

   2) 제약조건과 DML

	Primary Key	Foreign Key	Not Null	Unique	Check
Insert	Null 불가 중복 값 불가	Parent 테이블에 값이 있어야만 child 테이블에서 값을 삽입 가능	Not null 조건 이 있을 때 : Null 불가	중복 값 불가(모든 로우는 null값 가질 수 있다)	데이터 유효성 여부 확인
Update	Null 불가 중복 값 불가	Parent 테이블에 값이 있어야만 child 테이블에서 값을 수정 가능	Not null 조건 이 있을 때 : Null 불가	중복 값 불가 (모든 로우는 null값 가질 수 있다)	데이터 유효성 여부 확인
Delete		child 테이블에 삭제하려는 값이 존재하고 있는 상태라면 parent 테이불에서는 삭제 불가능

  【 테스트 결과 】

5. 테이블 관련 작업

Create like	해당 테이블과 동일한 구조만 같은 테이블 생성 컬럼 삭제/추가 등 가능
Create index	해당 테이블에 인덱스 생성
Create trigger	해당 테이블에 트리거 생성
Gather statistics	옵티마이저 통계치를 수정
Generate DDL	DDL 작업 (create /drop/alter/rename/truncate)
Grant Privileges	다른 user에게 객체 사용 권한 부여 (select , insert, update, delete 등)
Drop table	테이블 삭제 Purge option : 이 옵션을 지정하지 않으면 휴지통에 남아 있으며, 이 옵션을 지정하면 flashback 사용 불가
truncate	테이블 구조만 남기고, 데이터 삭제 인덱스도 truncate 됨 DDL 문이므로 undo 데이터 생성 되지 않음 사용공간 반환 가능 : truncate table ~ drop storage;
Show dependencies	테이블 객체간의 관계에 대해서 보여줌
View data	데이터를 직접 소유/제어 하지 않고 구문으로 데이터를 조작.
Flashback table	해당 시점으로 데이블을 복원.테이블만 복원됨.

6. 인덱스

논리적 인덱스	단일열 인덱스(Single index)	하나의 컬럼으로 이뤄진 인덱스
	복합 인덱스 (Concanated Index)	2개 이상의 컬럼으로 이뤄진 인덱스
	유일 인덱스 (Unique Index)	중복된 값이 입력되지 않는 경우
	비유일 인덱스 (NonUnique-Index)	중복된 값이 입력되는 컬럼의 인덱스
물리적인덱스 `	B-tree	Btree 형태의 인덱스
	Bitmap	bit로 구성한 인덱스 많은 update사용시 사용 하지 않는 것이 좋음.
	Function-base	함수 지정 인덱스

    1) 인덱스 생성 문법
        create index index_name on table_name(columns)
        tablespace ~;

    2) B*Tree Index
       ① 가장 범용적으로 사용되는 인덱스(OLTP,DW..)
       ② Leaf Block에 각 value에 대한 Rowid를 포함 (Table의 Row를 가르키는 Pointer 역할)
       ③ Leaf Block의 값은 정렬되어 있음 (Leaf Block에 앞, 뒤의 Leaf Node에 대한 Link를 가지고 있음) 
       ④ 인덱스를 통한 Table의 1 row 조회시 최소 3 ~ 4 Block Access 

    3) Bitmap Index
       ① 장점
          ⓐ Cardinality가 낮은 열에 대해 사용
          ⓑ Small Storage 사용
          ⓒ AND/OR 등으로 결합된 복합 조건에 최적
          ⓓ 전통적인 b*_Tree 인덱스의 단점 해소(or, not, null,..)
          ⓔ null column도 Index에 저장
          ⓕ DATAWARE HOUSE등 대량의 Data를 Read Only Mode로 사용시에 적당
       ② 단점
          ⓐ DML 작업에 취약
          ⓑ Block Level Locking
          ⓒ Rule Base Optimizer에서는 사용 못함
          ⓓ Online option(build, rebuild) 사용 못함

※ bitmap과 b-tree 인덱스 사용에 관한 조동욱님의 글 : http://ukja.tistory.com/136


7. View
  1) 저장된 쿼리 : 실제 데이터를 포함하지 않으며, 뷰가 참조하는 테이블로부터 데이터를 읽어옴
  2) 뷰에서 데이터를 조회, 변경, 입력, 삭제 가능 : 실제로 수행되는 모든 작업은 테이블에 영향을 줌

8. Sequence : 여러 사용자가 고유한 정수값을 생성 할 수 있는 객체
   1) Name : 참조되는 방식을 나타내는 시퀀스 이름
   2) Schema : 시퀀시의 소유자
   3) Maximum value : 시퀀스가 생성할 수 있는 최대값을 지정. 28자리 이하의 정수. Unlimited 의 경우 10*27까지
   4) Minimum value : 시퀀스 최소값. 28자리 이하의 정수. Unlimited 의 경우 10*26까지
   5) Interval : 시퀀스 번호 사이의 간격 지정
   6) Initial : 시작 시퀀스 번호
   7) Cycle values : 오름 차순의 시퀀스가 최대값에 도달시 다시 최소값으로 생성됨.
   8) Order values: 시퀀스 번호가 요청순서대로 생성 
   9) cache options : 시퀀스의 빠른 접근을 위해 미리 할당하여 메모리에 저장시켜야 할 시퀀스의 개수를 지정함. 28자리 이하의 정수. 최소값은 2.
   10) SQL 문장에서 시퀀스 참조
      ① currval : 시퀀스의 현재값 리턴
      ② nextval : 시퀀스를 증가시켜 다음 값 리턴

9. Temporary Table
  1) 개요
    ① Temporary Table : 하나의 세션 동안이나 트랜잭션 동안 임시로 데이터를 보관하기 위한 테이블
                                   세션이나 트랜잭션이 종료 되면 임시 테이블의 내용은 Truncate 됨
    ② 주로 복잡한 질의나 트랜잭션을 다루는 경우 영구적인 테이블에 insert, update, delete전에 일시적으로 row 정보를 바로 바로 가져다 쓸 수 있게 하는 용도로 사용
    ③ 백업 불가능
    ④ 데이터 세그먼트, 인덱스 세그먼트 할당되지 않음
    ⑤ 장점 : 복잡한 질의를 사용 할 때 수행 속도 향상 가능
    ⑥ 사용자의 임시 테이블 스페이스에 만들어 짐
    ⑦ 첫번째 레코드가 입력된 후에 임시 세그먼트 (TemporarySegment)가 할당 됨 : 세션이나 트랜잭션이 종료 되면 세그먼트 할당이 해제 됨
  2) 특징
    ① 일반적인 데이터 테이블처럼 insert, update, delete 가능
    ② 세션이나 트랜잭션이 끝나면 데이터는 자동으로  truncate 됨
    ③ 인덱스, 뷰, 트리거 생성 가능
    ④ DML Lock 없음 : session간에 data에 대한 contention이 발생되지 않기 때문에 
    ⑤ table의 definition은 export/import utility를 이용하여 export/import 가능하나, 그 row들은 export의 대상이 될 수 없음
    ⑥ ON COMMIT DELETE ROWS 로 생성 : transaction단위이며 이때 data는 commit이 되는 시점에 자동으로 제거 (default)
    ⑦ ON COMMIT PRESERVE ROWS 로 생성 : session단위이며 이때 data는 해당 session이 종료되면서 제거
    ⑧ data는 session private : 특정 session에서 사용하는 temporary table data는 다른 session에서 access 불가
  3) 제약조건 (in ORACLE8i)
    ① partitioned, index-organized, clustered table로 생성 불가
    ② foreign key constraint를 설정 불가
    ③ nested table이나 varray type의 column은 포함 불가
    ④ 다음과 같은 LOB_storage_clause들은 지정불가 : TABLESPACE, storage_clause, LOGGING 또는 NOLOGGING, MONITORING 또는 NOMONITORING,LOB_index_clause
    ⑤ parallel DML이나 parallel query는 지원 안됨 : parallel hint는 무시될 것이며, table생성시 parallel clause를 지정하면 error return
    ⑥ storage나 tablespace 지정 불가
    ⑦ 분산 transaction 지원 불가
  4) 문법
CREATE GLOBAL TEMPORARY TABLE table_name (
    column data_type()
)
On commit preserve rows
as select * from ~;

10. Data Dictionary (데이터 사전)
   1) 정의 : 읽기전용 테이블 및 뷰들의 집합으로 데이터베이스 전반에 대한 정보 제공
   2) 저장되는 내용
      ① 오라클의 사용자 이름 
      ② 오라클 권한과 롤 
      ③ 데이터베이스 스키마 객체(TABLE, VIEW, INDEX, CLUSTER, SYNONYM, SEQUENCE..) 이름과 정의들 
      ④ 무결성제약 조건에 관한 정보 
      ⑤ 데이터베이스의 구조 정보 
      ⑥ 오라클 데이터베이스의 함수 와 프로지저 및 트리거에 대한 정보 
      ⑦ 기타 일반적인 DATABASE 정보 
   3) 특징
      ① SYS : 데이터 사전의 모든 기본 TABLE과 VIEW 소유 
      ② DDL 명령이 실행 될 때 마다 데이터 사전을 Access
      ③ 모든 사용자는 DB정보에 대한 읽기 전용 참조로 데이터 사전 사용 가능
      ④ DB작업동안 Oracle은 데이터 사전을 읽어 개체의 존재여부와 사용자에게 적합한 Access 권한이 있는지 확인
      ⑤ Oracle은 데이터 사전을 계속 갱신하여 DATABASE 구조, 감사, 사용자권한, 데이터등의 변경 사항 반영

	Who Can Query	Contents	Subset of	Notes
DBA_	DBA	모든 내용 조회 가능	N/A	SELECT ANY TABLE 권한이 있는 사용자 또한 질의 가능
ALL_	모든 사용자	권한을 가지고 있다면, 다른 사용자의 객체까지 조회 가능	dba_views	사용자 자신의 객체 포함
USER_	모든 사용자	특정 사용자 소유의 모든 내용 조회	all_views	all_view와 같으나, owner 부분이 빠짐