Alice's Story

1. USER_ 데이터 딕셔너리 뷰
  :자신이 생성한 테이블, 인덱스, 뷰, 동의어 등과 같은 객체나 해당 사용자에게 부여된 권한 정보를 조회할 수 있다.
  - USER_TABLES : 테이블이 저장된 테이블 스페이스 이름, 데이터가 저장된 물리적 공간 그리고 블록 파라미터 정보 등이 저장된다.
  - USER_OBJECTS : 사용자가 생성한 테이블 정보와 함께 인덱스, 시퀀스, 동의어, 뷰 등과 같은 객체에 대한 이름, 종류, 생성 날짜 등의 정보를 저장한다.
  - USER_CATALOG : 사용자 소유로 생성된 모든 객체 이름과 객체 종류에 대한 정보를 저장한다.

  ex)SELECT table_name FROM user_tables;

2. ALL_ 데이터 딕셔너리 뷰
  : 자신이 생성한 테이블, 인덱스, 뷰, 동의어 등과 같은 객체나 자신에게 권한이 있는 객체나 정보를 조회할 수 있다.
  ex) SELECT table_name FROM ALL_tables;

3. DBA_ 데이터 딕셔너리 뷰
  : DBA 나 SELECT ANY TABLE 권한을 가진 사용자만 접근할 수 있다
  ex) SELECT owner,table_name FROM dba_tables;

4. v$_ 데이터 딕셔너리 뷰
  : DB의 관리나 상태의 정보
  ex) SELECT * FROM v$instance;

1. 데이터 무결성 제약 조건의 종류
  - NOT NULL : 해당 칼럼값은 NULL을 포함할 수 없음
  - 고유키(Unique key) : 테이블에서 해당 칼럼 값은 반드시 유일해야 함
  - 기본키 : 해당 칼럼 값은 반드시 존재해야 하며, 유일해야 함. Unique Key, NOT NULL 제약 조건이 결합된 형태
  - 참조(foreign key) : 해당칼럼 값은 참조되는 테이블의 칼럼 값 중의 하나와 일치하거나 NULL을 가짐
                           foreign key(자식)와 reference key(부모)는 index key가 있어야 속도가 빨라짐
  - CHECK : 해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

2. 테이블 생성 시 무결성 제약조건 생성
CREATE TABLE [schema.] table
    (column datatype [DEFAULT expression]
    [column_constraint],
    [table_constraint] [,…])

ex)CREATE TABLE subject
     (subno      NUMBER(5)
         CONSTRAINT subject_no_pk PRIMARY KEY
         DERERRABLE INITIALLY DEFERRED
         USING INDEX TABLESPACE indx,
      subname    VARCHAR2(20)
         CONSTRAINT subject_name_nn NOT NULL
      term       VARCHAR2(1)
         CONSTRAINT subject_term_ck CHECK(term in('1','2')),  or (term > 5) => 가능
      type       VARCHAR2(1));

3. 기존 테이블에 무결성 제약 조건 추가 방법
ALTER TABLE table
   ADD [CONSTRAINT constraint_name] constraint_type (column);

ex)ALTER TABLE department ADD CONSTRAINT dept_no_pk PRIMARY KEY(deptno);
ALTER TABLE department MODIFY (dname CONSTRAINT dept_dname_nn NOT NULL);
* NOT NULL 제약조건 추가는 NOT NULL 허용상태를 NOT NULL 불가로 변경하는 것이므로 MODIFY 명령문을 사용해야 한다.

4. 무결성 제약 조건 삭제
ALTER TABLE table
   DROP CONSTRAINT constraint_name [CASCADE];

ex)ALTER TABLE subject DROP CONSTRAINT subject_term_ck;

5. 제약조건의 비활성화
ALTER TABLE table
   DISABLE CONSTRAINT constraint_name [cascade];

ex)ALTER TABLE sugang DISABLE CONSTRAINT sugang_pk;

6. 제약조건의 활성화
ALTER TABLE table
   ENABLE [NOVALIDATE] CONSTRAINT constraint_name [CASCADE];

  * NOVALIDATE : 기존 데이터는 적용시키지 않고 새로 입력되거나 수정되는 데이터에만 적용시킨다는 의미.

7. 무결성 제약조건 데이터 딕셔너리
  : 사용자에 의해 정의된 무결성 제약조건을 조회하기 위한 데이터 딕셔너리 뷰는 USER_CONSTRAINTS,  USER_CONS_COLUMNS 가 있다.
  * USER_CONSTRAINTS : 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약 조건 종류 및 활성화 상태 정보 저장
  * USER_CONS_COLUMNS : 제약조건 정의된 칼럼과 제약조건 이름 저장

1. 생성
CREATE [UNIQUE] INDEX index
     ON table (column1 [ASC| DESC] [, column2 [ASC|DESC],…]);

2. 인덱스 생성 조건
  1) WHERE 절이나 조인 조건 절에서 자주 사용되는 칼럼
  2) 전체 데이터 중에서 10 ~ 15% 이내의 데이터를 검색하는 경우 - 옵티마이져가 계획을 세워서 자동으로 판단 후 실행
  3) 두개 이상의 칼럼이 WHERE 절이나 조인 조건에서 자주 사용되는 경우
  4) 테이블에 저장된 데이터의 변경이 드문 경우

3. 인덱스의 종류
  1) 고유 인덱스 :  유일한 값을 가지는 컬럼에 대해 생성하는 인덱스
   ex) CREATE UNIQUE INDEX idx_dept_name ON department (dname);
  2) 비고유 인덱스 : 중복간 값을 가지는 칼럼에 대해 생성하는 인덱스
   ex) CREATE INDEX idx_stud_birthdate ON student(birthdate);
  3) 단일 인덱스 : 하나의 칼럼으로만 구성된 인덱스
   ex)CRAETE INDEX idx_stud_dno_deptno ON student(deptno);
  4) 결합 인덱스 : 두 개 이상의 칼럼을 결합하여 생성되는 인덱스. WHERE 절의 조건 비교에서 두 개 이상의 칼럼이 AND로 연결되어 자주 사용되는 경우에 주로 생성함.
   ex)CRAETE INDEX idx_stud_dno_grade ON student (deptno, grade);
5) Descending index : 인덱스에서 정렬을 desc, asc를 정할 수 있다.
6) 함수기반 인덱스(FBI) : 오라클 8i부터 지원되는 것으로 컬럼에 대한 연산이나 함수의 계산 결과를 인텍스로 생성 가능
  ex)create index idx_standard_height on student((height-100)*0.9);

4. 인덱스 정보 조회 : 인덱스에 대한 정보는 user_indexes , user_ind_columns 로 조회 가능함. 그러나 함수식이 변경되면 인덱스를 삭제 후 새로 생성해야하는 단점이 있다.
   ex) SELECT index_name, uniqueness FROM user_indexes WHERE table_name='STUDENT';
       SELECT index_name, column_name FROM user_ind_columns WHERE table_name='STUDENT';

5. 인덱스 삭제
DROP INDEX index;
  ex)DROP INDEX fidx_stud_no_name;

6. 인덱스 재구성 : 테이블에 정의된 컬럼 값에 대해 변경 사항이 자주 발생할 경우 인덱스 키의 정렬 순서를 유지하기 위하여 노드값을 조정
alter index [schema] index rebuild [tablespace tablespace]
ex)alter index stud_no_pk rebuild;

1. 단순 뷰 생성
CREATE [OR REPLACE] [ FORCE | NOFORCE] VIEW view
   [ (alias, alias,;;;)]
   AS subquery;

   * OR REPLACE : 기존 뷰와 동일한 이름으로 뷰를 재생성하는 경우
   * FORCE : 기본 테이블의 존재 여부에 상관없이 뷰 생성
   * NOFORCE : 기본 테이블이 존재할 경우에만 뷰 생성, 기본 값
   * ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 뷰의 칼럼 이름

ex)CREATE VIEW v_stud_dept101(학번, 이름, 학과번호)
   AS SELECT studno, name, deptno
     FROM   student
     WHERE  deptno = 101;

2. 복합 뷰 생성
CREATE VIEW v_stud_dept102(학번, 이름, 학년, 학과이름)
AS SELECT s.studno, s.name, s.grade, d.dname
     FROM   stduent s, department d
     WHERE   s.deptno=d.deptno
     AND   s.deptno=102;

3. 함수를 이용한 뷰 생성
CREATE VIEW v_prof_avg_sal
AS SELECT deptno, SUM(sal) sum_sal, AVG(sal) avg_sal
     FROM professor
     GROUP BY deptno;

4. 인라인 뷰 (inline view) : FROM 절에 서브쿼리 사용하여 생성한 임시 뷰를 의미
SELECT dname, avg_height, avg_weight
FROM  ( SELECT deptno, avg(height) avg_height,
             avg(weight) avg_weight
FROM student
GROUP BY deptno) s, department d
WHERE s.deptno = d.deptno;

5. 뷰의 삭제
DROP VIEW view;

ex) DROP VIEW v_stud_dept101;  

6. 뷰와 관련된 데이터 딕셔너리
: USER_VIEWS - 사용자가 생성한 모든 뷰를 볼 수 있다.

* view는 index를 만들 수 없다.