Alice's Story

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를 만들 수 없다.

1. SELECT 실행 과정 {SELECT * FROM emp;}
-> user process (sqlplus)
-> server process : Parsing
  => ASCII 변환 (query를 변환)
  => Hash 함수
  => Cache가 있는지 확인
    ==> instance [Shared Pool:Library Cache] - soft parsing
  => 문법검사
    ==> instance [Shared Pool:Dictionary cache] - hard parsing
  => 권한검사
-> optimaser
  => 실행계획
    ==> 실행 계획까지의 모든 데이터를 저장
    ==> instance [Shared Pool:Library Cache]
    ==> Dictionary를 확인하고 계획을 세우기 때문에 Dictionary를 Update 해야함 [CBO:9i]
-> server process
  => Execute : 실행계획을 받아서 실행
    ==> Database buffer Cache에 있는지 확인
    ==> Data File에서 Block의 정보를 읽고 Database buffer Cache에 전체를 복사해서 결과 가지고 옴
  => Fetch
    ==> 필요한 정보만을 Block에서 간추린다. [PGA]
    ==> user process 전달

2. UPDATE 실행계획{UPDATE emp SET name='홍길동' WHERE empno=100}
-> SELECT의 parsing까지 동일
-> server process
  => Execute : 실행계획을 받아서 실행
    ==> Database buffer Cache에 있는지 확인
    ==> Data File에서 Block의 정보를 읽고 Database buffer Cache에 전체를 복사해서 결과 가지고 옴
    ==> Redo log Buffer 작업 내용 저장[작업일지]
      ===> instance 이기 때문에 수시로 Redo log file에 저장
        ====> 속도 fast - 수정 및 저장 내용이 적고 간결, Block의 위치에 상관없기 때문에
        ====> LGWR
      ===> 만약 불가피하게 디비가 죽는다면 다시 디비를 올리면서 Database File의 데이터와
           Redo log file의 내용을 비교해서 변경사항이 있다면 Database File의 내용을 변경한다.
        ====> 아카이브 로그에 다시 백업 복사
    ==> undo segment에 원래 내용 저장[취소하기 위해서]
    ==> Database buffer Cache에 원하는 정보로 변경 [commit 전에 변경 완료]