테이블스페이스란?

 

- 논리적인 데이터 저장구조(=DB Cache Buffer 내에서 데이터를 작업하는 공간)

  (오라클은 데이터를 저장하고 작업할 때

     * 메모리에 논리적으로는 Tablespace 공간을 만들어서 작업, 실제 작업이 일어나는 공간.(작업속도가 빠름)

     * 물리적으로는 디스크렝 Data File을 만들어서 저장(속도 느림)

 

- Database Buffer Cache에 Tablespace를 생성함(=SQL을 수행하면 해당 데이터는 반드시 Tablespace에 존재해야 함)

- Tablespace가 사용하는 메모리 공간이 클수록 일반적으로 수행 속도가 빨라짐

- dba_data_files 조회

 

 

 

Tablespace 의 종류 및 특징

 

(1) SYSTEM Tablespace

- 데이터 딕셔너리 정보들이 저장되어 있음, 이 Tablespace 가 손상될 경우 Oracle 서버가 시작이 안됨

- SYS계정 소유이지만 조회만 가능!

 

- 데이터 딕셔너리 : 오라클 서버의 모든 정보를 저장하고 있는 아주 중요한 테이블이나 뷰들

   ┌ Base Table : 데이터베이스 생성시(dbca, create database등) 생성됨, 사람 접근 불가(DBA 조차)

   └ Data Dictionary View : Base Table을 조회할 수 있도록 하는 뷰

        ┌ Static Dictionary : 내용이 실시간으로 변경 안됨

        │                           (USER_XXX, ALL_XXX, DBA_XXX)  = Instance가 Open일 경우에만 조회 가능

        └ Dynamic Performance View : 실시간으로 변경되는 내용을 볼 수 있음

                                                      조회 시점에 Control File/메모리로 가서 정보를 가져옴

                                                      Instance가 Nomount 상태부터 조회 가능

 

- 데이터 딕셔너리에 들어있는 주요 정보

* 데이터베이스의 논리적인 구조와 물리적인 구조 정보들

* 객체의 정의와 공간 사용 정보들

* 제약조건에 관련된 정보들

* 사용자에 관련된 정보들

* Role, Privilege 등에 관련된 정보들

* 감사 및 보안등에 관련된 정보들

 

(2) SYSAUX Tablespace

      : 10g 버전부터 등장, Oracle 서버의 성능 튜닝을 위한 데이터들이 저장되어 있음

 

 

(3) 일반 Tablespace

      : 가장 일반적으로 많이 사용되는 Tablespace로 관리자가 필요에 의해 만드는 Tablespace.

        DBA 에 의해 얼마든지 생성하고 삭제할 수 있음 

 

문제) Tablespace 용량 부족으로 에러가 발생되면?

해결방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)

해결방법 2) Data file을 하나 더 추가

 

Tablespace Offline

- 사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미

- 데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용

 

- Tablespace를 Offline하는 방법 3가지

1) Normal Mode  

 SQL> alter tablespace haksa offline ;

2) Temporary Mode

 Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법

3) Immediate Mode

- 반드시 Archive Log Mode일 경우에만 사용해야 한다.

- Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 Tablespace를 offline해야 할 경우 사용

 

 

- Tablespace 이동하기

1) Offline되는 Tablespace의 Data file 이동하기

1. 해당 Tablespace Offline 하기

2. Data file을 대상 위치로 복사

3. Control file 내의 해당 Data file 위치 변경

4. 해당 Tablespace Online

 

2) Offline 안 되는 Tablespace의 Data file 이동하기

1. DB 종료

2. Mount 상태로 시작

3. Data file 복사

4. Control file의 내용 변경

5. DB Open

 

 

(4) Undo Tablespace

     : Undo Segment를 저장하고 있는 Tablespace, 관리자가 생성/관리 가능

  Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만

         자동 관리 기법(AUM, Automatic Undo Management)

 

- Undo Data : 사용자가 DML을 수행할 경우 발생하는 원본데이터, Oracle Server Process가 직접 관리

                     ex) 홍길동→일지매로 업데이트 시 홍길동이 Undo Data

- Undo Segment : Undo Data만을 저장하는 Segment

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

- 데이터베이스의 데이터들 중 순서가 필요할 경우가 있음 (EX. 주문번호, 학번, 게시글 번호 등)

 

- SEQUENCE : 연속적인 번호의 생성이 필요할 경우를 위해서 오라클에서도 연속적인 번호를

   자동으로 만들어 주는 기능

- SYNONYM : 테이블에 별명을 만들어서 부르는 기능

 

1. SEQUENCE (시퀀스)

 - ​SEQUENCE 는 마치 은행의 번호표처럼 연속적인 일련 번호를 만들어 주는 기능

- 문 법:

 

  

 - 사용 예)  아래의 조건으로 제품 주문번호를 생성하기 위해 사용할 SEQUENCE 를 만드세요.
       * SEQUENCE 명: seq_jumun_no
       * 시작 번호: 1000       
       * 끝 번 호: 1010
       * 증 가 값: 1
       * 반복 되고 캐싱은 2개씩 되도록 하세요.

 

 

 

- 위에서 생성한 SEQUENCE 는 CURRVAL과 NEXTVAL을 사용하여 조회 및 사용할 수 있음

 

- CURRVAL은 현재까지 생성된 번호, 즉 가장 마지막 번호를 보여줌

- NEXTVAL은 다음에 나올번호를 보여줌

 

- SEQUENCE 에서 중요한 부분은 6번 라인에 있는 CACHE기능입니다.
 

ex) 평소 판매가격이 100만원인 제품을 선착순 1 천 명에게만 10만원에 판매하는 이벤트를 한다고

     미리 공지를 하고 주문을 받을 경우 1초에도 아주 많은 주문이 들어 올 것입니다.
     이때 주문 테이블의 주문 번호를 SEQUENCE 로 생성 할 경우 SEQUENCE에서 번호를 생성하는

    시간이 걸리므로  SEQUENCE 번호를 기다리는 대기(WAIT) 현상이 발생하게 될 것이고

    이로 인해 성능이 저하

   

     이런 경우를 조금이라도 개선하기 위해 SEQUENCE 번호를 미리 메모리상에 만들어두고(CACHE)

    SEQUENCE 번호 요청이 들어오면 즉시 번호를 할당하게 된 것

 

 

1) SEQUENCE 생성 및 옵션 확인하기

 

Step 1: 예제로 사용할 jumun 테이블을 아래와 같이 생성하고 데이터를 입력하겠습니다.  ​SCOTT> CREATE TABLE jumun      2      ( j_no number(4) ,      3        j_name varchar2(10)) ; ​ ​Step 2. 데이터를 입력합니다. ​SCOTT> INSERT INTO jumun      2      VALUES(seq_jumun_no.NEXTVAL,'AAAA'); 1 row created. SCOTT> SELECT * FROM jumun ;  J_NO  J_NAME ----- -------   1000    AAAA SCOTT> INSERT INTO jumun      2      VALUES(seq_jumun_no.NEXTVAL,'BBBB') ; 1 row created. SCOTT>SELECT * FROM jumun ;  J_NO  J_NAME ----- -------   1000    AAAA   1001    BBBB SCOTT>SELECT seq_jumun_no.CURRVAL FROM dual;  CURRVAL ---------        1001                               ← 현재 SEQUENCE 에서 발생한 마지막 값  ​ ​Step 3. MAXVALUE / MINVALUE 항목과 CYCLE 값을 테스트 합니다. ​SCOTT> BEGIN      2      FOR i in 1..11 LOOP      3           INSERT INTO jumun VALUES(seq_jumun_no.NEXTVAL ,      4                                                       DBMS_RANDOM.STRING('A',4) );      5           END LOOP;      6      COMMIT ;      7      END ;      8      / PL/SQL procedure successfully completed. SCOTT> SELECT * FROM jumun ;  J_NO  J_NAME ----- --------------   1000    AAAA   1001    BBBB   1002    ldXB   1003    UEJk   1004    ywWy   1005    hHAy   1006    wvhJ   1007    pWvQ   1008    ueod   1009    Iqeg   1010    UwuX    11 rows selected.   ※ 11건의 데이터가 모두 입력되고 SEQUENCE 번호도 1000-1010번 까지 모두 소진 됨   SCOTT> INSERT INTO jumun      2      VALUES(seq_jumun_no.NEXTVAL,'DDDD') ; 1 row created. ​ SCOTT> SELECT * FROM jumun ;  J_NO  J_NAME ----- ---------   1000    AAAA   1001    BBBB   1002    ldXB   1003    UEJk   1004    ywWy   1005    hHAy   1006    wvhJ   1007    pWvQ   1008    ueod   1009    Iqeg   1010    UwuX  J_NO  J_NAME ----- ----------     990   DDDD               ←  SEQUENCE 생성 시 MINVALUE로 지정한 값 12 rows selected.​​   ※ MAXVALUE 값을 다 사용한 상태에서 CYCLE 로 설정이 되면 다시 돌아가는 데            그 시작번호는 MINVALUE로 설정된 값임을 알 수 있습니다. ​

 

 

Step 4. NOCYCLE 설정과 CACHE 설정을 테스트 합니다.            (이 실습을 하기 위해 SEQUENCE 와 테이블을 하나씩 더 생성) SCOTT> CREATE TABLE jumun2      2      ( j_no number(4) ,      3        j_name varchar2(4) ); Table created. SCOTT> CREATE SEQUENCE seq_jumun2_no      2      INCREMENT BY 1      3      START WITH 100      4      MAXVALUE 105      5      CACHE 2 ;   ​  > 데이터를 1건 만 입력한 후 데이터베이스를 강제 종료 ​SCOTT> INSERT INTO jumun2      2      VALUES(seq_jumun2_no.NEXTVAL,'AAAA') ; 1 row created. ​ SCOTT> COMMIT; Commit complete. SCOTT> CONN / AS SYSDBA ; Connected. SYS> shutdown abort; ORACLE instance shut down. SYS> STARTUP ORACLE instance started. … Database opened.​ ​ ​SYS> CONN scott/tiger Connected. SCOTT> SELECT * FROM jumun2; J_NO J_NAME ---------- ---------- 100 AAAA SCOTT> INSERT INTO jumun2       2      VALUES(seq_jumun2_no.NEXTVAL,'BBBB') ; 1 row created. SCOTT>SELECT * FROM jumun2;  J_NO  J_NAME ----- -------   100    AAAA   102    BBBB ​  원래 101 번이 입력되어야 하지만 시퀀스 생성할때 CACHE 값을 2로 주어서 100, 101 번이 캐싱되 어있다가 서버가 비정상 종료되어서 캐싱되었던 101 번이 사라지고 102 번이 입력이 된 것입니다.​​​

  

- Seq_jumun2_no 시퀀스는 생성될 때 CYCLE 라는 옵션을 주지 않았습니다.

   이 옵션을 주지 않고 생성하면 기본값은 NOCYCLE 입니다.

아래와 같이 MAXVALUE 를 초과하여 값을 요청하면 에러를 발생하게 됩니다

 

SCOTT> INSERT INTO jumun2      2      VALUES(seq_jumun2_no.NEXTVAL , 'FFFF') ; VALUES(seq_jumun2_no.NEXTVAL , 'FFFF')              * ERROR at line 2: ORA-08004: sequence SEQ_JUMUN2_NO.NEXTVAL exceeds MAXVALUE and cannot be instantiated

 

  

2) 값이 감소하는 SEQUENCE 생성 및 사용하기

 

SCOTT>CREATE SEQUENCE s_rev          2     INCREMENT BY -1          3     MINVALUE 0          4     MAXVALUE 10          5    START WITH 5 ;    Sequence created.    SCOTT> INSERT INTO s_test1 VALUES (s_rev.NEXTVAL);    1 row created.    SCOTT> /    1 row created.    SCOTT> /    1 row created.    SCOTT> /    1 row created.    SCOTT> /    1 row created.    SCOTT> /    1 row created.    SCOTT> /    insert into s_test1 values (s_rev.nextval)    *    ERROR at line 1:    ORA-08004: sequence S_REV.NEXTVAL goes below MINVALUE and cannot be instantiated    SCOTT> SELECT * FROM s_test1;        NO    ------          5          4          3          2          1          0    6 rows selected.

 
  

 

3) SEQUENCE 조회 및 수정하기
    위에서 생성한 seq_jumun2_no 시퀀스를 최대값을 110 번으로 하고 CACHE 값을 5로 수정하세요.

 

- 현재 생성되어 있는 SEQUENCE 내용을 조회 SCOTT> COL 이름 FOR a15 ; SCOTT> SELECT sequence_name "이름" ,      2                     MIN_VALUE "MIN 값" ,      3                     MAX_VALUE "최대값" ,      4                     INCREMENT_BY "증가값" ,      5                     CYCLE_FLAG "CYCLE?" ,      6                     ORDER_FLAG "정렬" ,      7                     CACHE_SIZE "캐싱값",      8                     LAST_NUMBER "마지막생성값"      9      FROM user_sequences     10     WHERE sequence_name='SEQ_JUMUN2_NO' ;  이름                     MIN 값   최대값  증가값  C        캐싱값   마지막생성값 ------------- ------ ----- ----- -- -- ----- ----------- SEQ_JUMUN2_NO       1        105         1     N  N         2             106 SCOTT> ALTER SEQUENCE seq_jumun2_no      2      MAXVALUE 110      3      CACHE 5 ; Sequence altered.   ※ SEQUENCE 를 변경할 때 START WITH 값은 변경이 안 됩니다.

 

 

 

4) SEQUENCE 삭제하기
    위에서 생성한 seq_jumun2_no 시퀀스를 최대값을 110 번으로 하고 CACHE 값을 5로 수정하세요.

 

SCOTT> DROP SEQUENCE seq_jumun2_no ;

 

 - SEQUENCE를 사용하는 DM이 ROLLBACK되어도 SEQUENCE는 다시 ROLLBACK 되지 않기 때문에

    만약 이럴 경우 입력되는 번호의 차이가 생길 수 있다는 점도 꼭 기억!!

 

 

 

2. SYNONYM(동의어)

 

- 동의어란 테이블에 붙이는 별명입니다.
- 이유 : 보안상 목적이나 사용자의 편의성 때문

 

1) 문 법:

CREATE [PUBLIC] SYNONYM synonym이름  FOR [schema.] 대상객체 ;

 

 

- SYNONYM 도 스키마 오브젝트이므로 만든 사람만 사용할 수 있습니다.
   (SCOTT 이 어떤 SYNONYM 을 생성하게 되면 SCOTT만 사용할 수 있다는 뜻)

 

- 다른 사용자도 다 쓸 수 있게 생성하려면 PUBLIC 이란 키워드를 사용해서 생성

 

- 동의어를 생성하려면 CREATE SYNONYM 권한 필요

SCOTT> CONN / AS SYSDBA  Connected.  SYS> GRANT create synonym TO scott ;  SYS> GRANT create public synonym TO scott ;  SYS> CONN scott/tiger  Connected.

 

 

 2) 생성 예 :

 

1. Scott 사용자의 emp2 테이블의 동의어를 e2로 생성

 

SCOTT> CREATE SYNONYM e2 FOR emp2 ;

 

 

2. Scott 사용자의 department 테이블의 동의어를 d2 로 생성하되 모든 사용자들이 사용 할 수있도록 생성

 

SCOTT> CREATE PUBLIC SYNONYM d2 FOR department ;

 

 

3) SYNONYM 조회하기

 

SCOTT> SELECT synonym_name, table_owner , table_name       2      FROM user_synonyms      3      WHERE table_name='EMP2' ;  SYNONYM_NAME   TABLE_OWNER   TABLE_NAME -------------- ------------ -----------   E2                         SCOTT               EMP2

 

 

4) SYNONYM 삭제하기

 

SCOTT> DROP SYNONYM e2 ;

 

 

 

 

 

- View(뷰) : 가상의 테이블

 

- 원래 테이블에는 데이터가 들어있지만 View에는 데이터가 없고 SQL만 저장되어 있다.

- 사용자가 해당 View에 접근하면 그 때 View에 들어있던 SQL이 수행되어 결과를 가져오는 것

 

- View를 사용하는 이유 : 보안, 사용자의 편의성

   : 어떤 테이블에 다른 사용자가 봐서는 안되는 컬럼이 있을 경우 해당 테이블에 직접 접근하게 해 주면

     그 컬럼의 내용까지 다 보여짐 ㅜ.ㅜ

 

 

 

 

 - CTAS라는 방법으로 복사해서 새로운 테이블을 만들어 줄 수 있겠지만, 이럴 경우 원본 테이블의 데이터가 변경되면

    복사 테이블에서는 반영이 안되는 문제점이 발생.

 

 - View는 사용자가 View에 접근하는 순간 원본 테이블에 가서 데이터를 가져오기 때문에 언제나 정확한 데이터를

    가져 올 수 있음 

 - View의 사용목적은 사용자의 편의성
    ex) 어떤 결과를 조회하기 위해 5개의 테이블을 조인해야 할 경우 매번 복잡한 쿼리를 일일이 치는 것이 아니라

          5개의 테이블을 Join해서 데이터를 가져오는 View라는 것을 만들어 놓고 해당 View를 조회

 

1. 단순 View (Simple View)

 - 단순 View : View를 생성할 서브쿼리에 조인 조건이 안 들어가고 1개의 테이블로 만들어지는 간단한 View를 의미

 

 - View 를 생성하기 위해서는 CREATE VIEW 권한이 필요함

 

 

 

* OR REPLACE : 같은 이름의 View가 있을 경우 삭제 후 다시 생성
* FORCE : 기본 테이블의 존재 여부에 상관없이 View 생성
* NOFORCE : 기본 테이블이 존재할 경우에만 View 생성, 기본 값
* ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 View의 칼럼 이름 지정
* WITH CHECK OPTION : 주어진 제약조건에 맞는 데이터만 입력 및 수정을 허용
* WITH READ ONLY : SELECT 만 가능한 읽기 전용 뷰를 생성

 

 

- View를 생성 한 후 해당 View에 SQL을 수행하면 그때 View내부의 서브쿼리를 수행하게 됨

- 위와 같이 View를 생성하면 오라클은 해당 View 정보를 딕셔너리에 저장해 놓고

   사용자가 이 View를 사용하게 되면 3번 라인부터 있는 서브 쿼리가 수행되어

   원본 테이블 professor에 가서 SELECT 절에 있는 컬럼을 가져오게 되는 것

- 평소에 이 v_prof View에는 아무런 데이터가 없다는 뜻

 

- View에는 제약조건이나 인덱스 등을 생성 할 수가 없음

 

 

2. 복합 View(Complex View)

 

- 복합 View :  Sub Query 부분에 여러 개의 테이블이 조인되어 생성되는 View

 

 

  - SCOTT> SELECT * FROM v_prof_dept ;        ← 이렇게 조회하면 View 의 서브쿼리 부분이 실행됨

 

 - 뷰를 잘못사용할 경우 성능 저하의 주 원인이 되는 경우도 많음

 

 - 가급적 View안에는 INTERSECT, MINUTS, UNION같은 집합 연산자는 사용하지 말아야 성능 향상에 도움이 됨

 

 

 

3. INLINE View (인라인 뷰)

 - View는 필요할 때 생성한 후 계속 여러 번 반복해서 재사용할 수 있음

 - 그러나 여러 번 사용할 필요 없이 1회만 필요한 View일 경우 SQL 문장의 FROM 절에 View의 서브쿼리 부분을

    바로 적어주면 되며 이런 1회용 View를 INLINE View라고 함

 

 

 

 

4. Materialized View (MVIEW) 

1) MVIEW란?

  : 구체화된 VIEW, 실체화된 VIEW, 데이터를 가지고 있는 뷰

 

 

 

- 일반 뷰일 경우

   * 사용자 A가 View에 SELECT를 수행하면 그때 원본 테이블에 가서 1억건 데이터를 가져온 다음

     사용자에게 반환하고 삭제

   * 다시 사용자 B가 View에 SELECT를 수행하면 다시 원본 테이블에가서 1억 건을 가져와서

   * 사용자 B에게 반환한 후 뷰에서 데이터를 삭제함

 

   * 이런 방식은 원본 테이블에도 많은 부하를 주고 사용자도 오래 기다려야 함

 

- 이러한 문제를 해결하기 위한 뷰가 MVIEW

- 구체화된 View, 실체화 된 View라고도 하며, 데이터를 가지고 있다.

 

- MVIEW일 경우

  * 사용자 A가 최초로 Mview에 SELECT를 수행하면 원본 테이블에서 1억 건을 가져와서 사용자 A에게 반환 후

      Mview 내부에 데이터 저장

  * 사용자 B가 MVIEW에 SELECT를 할 경우 원본 테이블에 가지 않고 MVIEW에 있는 데이터를 바로 반환 함

 

 - 사용자가 많고 데이터가 많을 수록 이 방식이 기본 방식에 비해 아주 효율적이고 성능도 좋다

 - 문제점 : 원본 테이블과 MVIEW간의 데이터 동기화

 

 

 

2) Mview 생성하기

 

 - Mview 를 생성하기 위해서는 QUERY REWRITE 권한과 CREATE MATERIALIZED VIEW  권한이 있어야 함

 

 

 

-생성 문법 설명 :
* 2행 : Mview 를 생성하면서 서브쿼리 부분을 수행해서 데이터를 가져 오라는 뜻
* 3행, 4행 : 원본 테이블에 데이터가 변경 되었을 경우 MView 와 언제 어떻게 동기화를 시킬 건지에 대한 옵션

                 ON DEMAND 옵션은 사용자가 수동으로 동기화 명령을 수행해서 동기화 시키는 것이고

                 ON COMMIT 옵션도 쓸 수 있는데 이것은 원본테이블에 데이터 변경 후 Commit 이 발생하면

                 자동으로 동기화 시키라는 의미

* 5행 : REFRESH 하는 방법 4가지

   - COMPLETE : MVIEW 내의 데이터 전체가 원본 테이블과 동기화 되는 방법

                        이 옵션을 사용하려면 ATOMIC_REFRESH=TRUE 와 COMPLETE 로 설정이 되어야합니다. 

   - FAST : 원본 테이블에 새로운 데이터가 입력될 경우 그 부분만 Mview 로 동기화 하는 방법

                이 방법은 Direct Path 나 Mview log 파일 을 사용하여 동기화 하게 됩니다.
   - FORCE : FAST 방법이 가능한지 살펴보고 불가능하면 COMPLETE 방법을 사용하여 동기화 하게 됩니다.
   - NEVER : 동기화를 하지 않습니다

 

 

 - Mview 에는 데이터가 존재하기 때문에 Index 등도 생성할 수 있습니다.

 

 

 

 

 

3) MView 관리하기

 

- 수동으로 원본 테이블과 Mview 데이터 동기화 하기
   이 작업을 하기 위해 원본 테이블 professor 에 아래 데이터를 먼저 추가하세요.

 

 

- 동기화 전에 원본테이블 (professor)과 Mview 의 데이터 건수를 조회합니다.

 

 

 - DBMS_MVIEW 패키지로 동기화를 수행 합니다.

 

 

- 다른 동기화 명령어들

 

 

- 이 명령어는 ABC라는 테이블을 사용하는 모든 MVIEW를 찾아서 한꺼번에 동기화 하라는 의미

  마지막의 C는 Refresh수준으로 Complete를 의미하며 Force인 f를 쓸 수도 있으며 대소문자 구분은 하지 않음

 

 

- 이 명령어는 해당 사용자가 만든 모든 MVIEW를 동기화 하라는 의

 

 

- Mview 조회하기 / 삭제하기
   : 현재 사용자가 생성한 Mview 를 조회하고 싶으면 USER_MVIEWS 딕셔너리 조회

    데이터터베이스 내의 모든 Mview 를 조회하려면 DBA_MVIEWS 조회

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.334-345

 

 

 

1. 인덱스란 무엇인가?

 

 : '어떤 데이터가 어디에 있다'라는 위치 정보를 가진 주소록 같은 개념

 

 

 

 

 

 

(사용자가 홍길동 정보를 조회하기 위해 SELECT 구문을 수행했다고 가정)

 - 오라클 서버 프로세스가 해당 쿼리를 수행해서 사용자가 원하는 데이터를 출력하는데 서버 프로세스는

    가장 먼저 메모리의 데이터베이스 버퍼 캐시를 살펴봄

 - 데이터베이스 버퍼캐시에는 많이 사용되는 테이블들이 캐싱되어 저장되어 있는데 여기에 데이터가 있을 경우

    빨리 결과를 볼 수 있음

 - 만약 데이터베이스 버퍼캐시에 조회하는 데이터가 없을 경우 서버 프로세스는 3번 과정으로

    하드 디스크에 있는 데이터 파일에서 홍길동 정보가 들어 있는 블록을 찾아서 데이터베이스 버퍼 캐시로

    복사해 온 후 사용자에게 값을 돌려줌(4번 과정)

 

- 메모리(데이터베이스 버퍼 캐시)에서 원하는 데이터를 찾을 경우 아주 빠르게 결과를 조회 할 수 있지만

   메모리의 용량이 한계가 있기에 모든 데이터를 데이터베이스 버퍼캐시에 보관 할 수 없음

- 그래서 많이 사용하는 데이터는 메모리의 데이터베이스 버퍼캐시에 캐싱 해 두고 많이 사용되지 않는 데이터는

   디스크에 저장 했다가 필요할 때 메모리로 복사해 와서 조회나 변경작업 등을 하게 됨.

 

 

 - 하드디스크에 데이터가 너무 많아 사용중인 블록이 너무 많을 경우 해당 데이터가 어떤 블록에 들어 있는지

    모른다면 모든 블록을 다 읽어 봐야 한다(=Full Table Scan)  ⇒ 시간이 아주 많이 걸림

 -  그래서 '어떤 데이터의 주소는 어디다' 라는 식으로 주소록을 만들어서 데이터를 관리하는 기법이 등장

     이렇게 데이터의 주소값을 가지고 있는 것 : 인덱스

 

 - 인덱스가 있으면 모든 블록을 다 읽지 않고 원하는 데이터가 있는 블록 주소를 찾아내서 그 블록만 메모리로

   복사해오면 빠른 작업 가능

 

 - 오라클에서는 데이터의 주소를 주소라고 표현하지 않고 ROWID라 부름

 - ROWID는 두 가지가 있는데 7버전까지 사용하던 제한적인 ROWID 가 있고

                                          8버전부터 현재까지 사용중인 확장된 ROWID가 있음

 - 확장된 ROWID는 총 10 bytes 로 그 구조는 아래와 같습니다.

 

  

 

 

 

< ROWID 구조 >

 

 - 어떤 데이터의 ROWID를 알고 있다는 것은 해당 데이터의 저장된 위치를 정확하게 알고 있다는 의미

 

 - 데이터들의 ROWID 정보를 별도의 세그먼트(대표적 세그먼트가 테이블입니다)에 넣어 저장/관리하는데

   이 세그먼트를 인덱스라고 함

 - 인덱스가 있으면 어떤 데이터가 어디에 저장되어 있다는 것을 다 알기 때문에 데이터를 조회나 변경을 위해

   호출 해야 할 때 아주 빠르게 작업이 가능

2. 인덱스의 생성 원리 

 - 인덱스의 종류가 여러 가지로 많이 있지만 생성되는 원리는 거의 동일

 

 - 인덱스를 생성하라고 쿼리를 수행하면

    ① 제일 먼저 해당 테이블을 전부 다 읽음(내용 파악)

    ② 인덱스 만드는 동안 데이터가 변경되면 문제가 되므로 해당 데이터들이 변경되지 못하도록 조치

    ③ 메모리(PGA 의 Sort Area 라는 곳입니다)에서 정렬

    ④ 만약 메모리가 부족하게 되면 임시 테이블스페이스(Temporary tablespace)를 사용해서 정렬(시간이 많이 걸림)

    ⑤ 메모리에서 정렬과정이 모두 끝난 데이터들은 파일의 블록에 순서대로 기록 함

 

 - 전체 테이블 스캔(Table Full Scan) → 정렬 (Sort) → Block 기록 이라는 과정을 거침

 - 데이터가 정렬이 되어서 들어간다

 

 

 

3. 인덱스 구조와 작동 원리(B-TREE 인덱스 기준)

 

 - 테이블은 여러 칼럼이 있고, 데이터도 기본적인 방법으로는 들어오는 순서대로 입력됨

 - 즉, 어떤 기준으로 정렬되는 것이 아니라 입력되는 순서대로 그냥 들어가서 정렬없이 저장된다(IOT 제외)

 - 인덱스는 컬럼이 두개뿐!!

 

 

 

 

 

 - 테이블은 컬럼이 여러개이고 데이터도 정렬없이 입력된 순서대로 들어가 있지만,

    인덱스는 컬럼이 Key 컬럼과 ROWID 컬럼 두개로 이루어져 있음

 

 - Key 컬럼이란 인덱스를 생성하라고 지정한 컬럼 값이 됨

 

 

 

 

 

 - 이름 컬럼과 주소 컬럼에 인덱스를 생성한 것

 

 

 

 EX) 사원 테이블에 사원이 100만 명 있고, 하드 디스크에 사원테이블이 지정한 파일의 블록이 10만개가 된다고 가정

 

 

 - 사용자가 홍길동 사원의 정보를 가져오는 SQL을 작성했다면,

 

  ① SQL을 수행하면 서버 프로세스가 파싱이란 과정을 마친 후

      메모리에(데이터베이스 버퍼캐시) 이름이 홍길동인 사람의 정보가 있는지 살펴봄

  ② 만약 메모리에 홍길동 정보가 없으면 하드 디스크의 파일에서 홍길동 정보를 가진 블록을 복사해서 메모리

     (데이터베이스 버퍼캐시)로 가져온 후 홍길동 정보만 골라내서 사용자에게 보여줌

     (이 때 하드 디스크의 데이터 파일의 블록이 아까 10만개라고 했으므로 서버 프로세스는 홍길동 정보가

      어느 블록에 들어 있는지 모를 경우 10만개 블록 전부다 메모리로 복사를 해야 함 ㅜ.ㅜ)

  ③ 그래서 이렇게 하기 전에 WHERE 절에 있는 이름 컬럼에 인덱스가 만들어져 있는지 확인

      (우리 예 에서는 인덱스가 만들어져 있으므로 IDX_사원_이름 인덱스에 먼저 가서 홍길동 정보가 어떤 ROWID를

       가지고 있는지 확인 한 후 해당 ROWID 에 해당되는 블록만 찾아가서 복사를 해 옴)

 

 

 

4. 인덱스의 종류

 

 - 일반적으로 많이 사용되는 인덱스는 크게 B-TREE 인덱스와 BITMAP 인덱스로 나뉨

 - B-TREE 인덱스 안에 세부적으로 여러 가지 인덱스로 또 나뉘게 됩니다.

 - 인덱스(주소록)가 많다는 뜻은 그 용도가 다 달라서 정확한 용도에 맞게 사용하고 관리해야 한다는 뜻

 - 데이터를 처리하는 방법 중에

   OLTP(OnLine Transaction Processing – 실시간 트랜잭션 처리용) 시스템인 경우가 있고

   OLAP (OnLine Analytical Processing – 온라인 분석 처리용) 시스템일 경우가 있음

 

 - OLTP 환경에서는 주로 B-TREE 인덱스들이 많이 사용되며

    OLAP 환경에서는 BITMAP 인덱스가 많이 사용된다.

 

 

(1) B-TREE 인덱스

 

 

 

 - 실제 테이블에 데이터들의 주소는 Leaf Block들에 전부 들어있으며

    해당 데이터들에 대한 데이터들이 Branch Block과 Root Block에 들어있음

 

 - 특정 데이터를 찾아야 할 경우 Root Block에서 Branch Block 정보를 찾고 Branch Block에서 Leaf Block 정보를

    찾아가서 해당 데이터의 ROWID를 찾은 후 데이터가 들어 있는 블록을 메모리로 복사해 옴

 

 - B-TREE에서 B란 Binary/Balance란 의미

 - Root Block을 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때 성능이 가장 좋다는 것이

   이 인덱스의 가장 큰 특징

 

 

 ① UNIQUE INDEX

   : 인덱스 안에 있는 Key 값에 중복되는 데이터가 없다는  뜻

  - 해당 테이블의 컬럼에 중복된 값이 없다는 뜻, 앞으로도 중복된 값이 들어 올 수 없다.

 

 

 

  - 인덱스 생성시에 설정하는 인덱스 이름은 회사마다 규칙이 있으니 그 규칙에 맞게 임의로 설정하면 되고

     일반적으로 인덱스를 뜻하는 접두어를 사용하고 테이블 이름과 컬럼 이름을 사용합니다.

  - 정렬방식은 인덱스 생성시에 이를 지정할 수 있습니다.

      위 생성문법에 2번 줄에 컬럼이름 뒤에 ASC 를 쓰면 오름차순정렬(기본값)이고 DESC 를 쓰면 내림차순 정렬

  - dept2 테이블의 dname 컬럼에 UNIQUE INDEX가 설정이 되면 데이터가 중복으로 들어갈 수 가 없음

 

 

- 첫 번째 dname의 값 '임시매장'은 중복이 되지 않아서 들어갔지만 두 번째로 입력하는 건

   이미 들어간 dname 이라서 에러가 발생함을 알 수 있습니다.

 

 

 ② Non UNIQUE INDEX

   : 중복되는 데이터가 들어가야만 하는 컬럼일 경우는 UNIQUE INDEX를 생성할 수 없기 때문에

     이럴 경우 Non UNIQUE INDEX를 생성

 

 

 

 ③ Function Based INDEX (FBI-함수기반 인덱스)

 

 - 인덱스는 WHERE 절에 오는 조건 컬럼 이나 조인 컬럼 등에 만들어야 한다
    ex) WHERE PAY = 100 이란 조건일 경우 SAL 컬럼에 인덱스를 만들어야 함

 

 - SAL 컬럼으로 인덱스를 생성했는데 정작 SQL 문장에서는 WHERE PAY + 100 = 200 이라는 조건으로 조회 하면

    SAL 컬럼의 인덱스는 사용할 수 없음 ⇒ 이런 현상을 INDEX Suppressing Error 라고 부름

 

 - 인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안 됨

 

 - 꼭 SQL 에서 WHERE PAY+100 = 200 이라는 형태로 써야 한다면?
     : 인덱스를 생성할 때 PAY+100) 형태로 인덱스를 생성하면 되고 이런 형태의 인덱스를 함수기반 인덱스라고 부름

 

 

 

 ④ DESCENDING INDEX

   : 큰 값이 먼저 오도록(즉, 내림차순) 인덱스를 생성하는 것

 

 - 주로 큰 값을 많이 조회하는 SQL 에 생성하는 것이 좋음

    ex) 계좌 조회 같은 메뉴는 주로 최근 날짜부터 먼저 나오게 하는 경우가 많습니다.

          날짜일 경우 최근 날짜가 큰 날짜이고

          예전 날짜가 더 작은 날짜이니 큰 날짜부터 먼저 조회하는 경우입니다.

 

 

 

 

⑤ 결합 인덱스(Compisite INDEX)

  : 인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것

//* 수정중  http://cafe.naver.com/dbian/251 *//

 

(2) BITMAP INDEX

 

 - 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용하는 인덱스

 - B-Tree 인덱스는 데이터의 값의 종류가 많고 데이터가 적을 경우에 사용하는 인덱스

 

 - 학생이 2천명이라 하더라도 성별 컬럼의 종류는 남,여 2가지뿐이므로 이곳은 BITMAP INDEX가 적당하다

 - 학번 컬럼의 경우에는 B-TREE INDEX로 생성하는 것이 유리

 

 - BITMAP INDEX를 생성하려면 우선 데이터의 변경량이 적어야 하며 종류 또한 적은 곳이 좋다 (=OLAP 환경)

 

 

 - BITMAP INDEX는 어떤 데이터가 어디에 있다라는 지도정보(MAP)을 BIT로 표시한다.

 - 어떠한 데이터가 존재하는 곳은 참인 1로 표시, 데이터가 없는 곳은 0으로 표시

 

 

 

 

 

- 해당 데이터가 있는 자리만 1로 표시, 나머지 행은 0으로 표시

 

- 이미 생성되어 있던 BITMAP INDEX에 새로운 데이터가 추가되면?!

   : 기존 BITMAP INDEX를 전부 수정해야 한다.

     즉, BITMAP INDEX는 데이터가 변경되는 테이블이나 컬럼에 만들게 되면 인덱스를 자주 수정하게 된다.

 

 

 

5. 인덱스의 주의사항 

 - 인덱스를 생성하면 'SQL이 빨라진다'라는 말은 맞을 수도, 틀릴 수 도 있다.

 

 - SELECT를 하는 경우에도 데이터 블록 수와 DB_FILE_MULTIBLOCK_READ_COUNT값과 분포도 등에 따라서

    인덱스가 빠를 수도 있고, Full Table Scan보다 늦어지는 경우도 종종 있다.

 

 1) DML에 취약하다.

  (1) Insert
  - Insert 작업일 경우 INDEX Spilt 현상이 발생 할 수 있고 이 현상으로 인해 Insert 작업의 부하가 심해 질 수 있음

  - INDEX Split 현상 : 인덱스의 Block들이 하나에서 두 개로 나누어지는 현상

      이유 : 테이블과 인덱스의 저장방식의 차이 때문

    - 테이블은 데이터가 입력이 될 때 정렬되지 않고 그냥 차례대로 입력, 인덱스는 데이터가 순서대로 정렬이 되어야

      하는데 문제는 기존 블록에 여유 공간이 없는 상황에서 그 블록에 새로운 데이터가 입력이 되어야 할 경우

 

   - 이 경우 기존 블록의 내용 중 일부를 새 블록에다가 기록한 후 기존 블록에 빈공간을 만들어 새로운 데이터를 추가

      즉 하나의 블록에 있던 내용이 두개의 블록으로 나누어진다는 의미로 INDEX Split 라고 부르는 것

  (2) Delete
  - 테이블은 데이터가 delete 되면 지워집니다. 그리고 그 자리에 다른 데이터가 들어와서 그 공간을 사용할 수 있다.

     그러나 INDEX 의 경우는 delete 가 되지 않고 해당 데이터가 사용 안 된다는 표시만 해 둠

     (테이블에서는 데이터가 지워지지만 인덱스에서는 데이터가 안 지워 진다는 의미)

     

  - 테이블에는 데이터가 1만 건이 있더라도 인덱스에는 2만 건이 있을 수 있다는 뜻입니다.

     물론 1만 건은 예전에 delete 된 데이터들 입니다.

     이런 상태의 인덱스를 사용하게 되면 인덱스를 사용함에도 불구하고 쿼리의 수행 속도가 아주 느려지게 됩니다.

 

 

  (3) Update
  - 테이블에서 데이터가 업데이트 될 경우 인덱스에서는 어떻게 될까?
     인덱스도 업데이트 될 거 같지만 인덱스에는 Update라는 개념이 없다

 - 테이블에 update가 발생 할 경우 인덱스에서는 delete가 먼저 발생 한 후 새로운 데이터의 Insert 작업이 발생하게 됨

   즉 update 는 두 가지의 작업이 인덱스에 동시에 일어나게되어 다른 DML 문장들 보다 더 큰 부하를 주게 된다

2) 타 SQL 실행에 악영향을 줄 수 있습니다.
  - 만약 어떤 테이블에 A 라는 인덱스만 있고 쿼리 수행시간이 1초 였는데

     그 테이블에 B 라는 새로운 인덱스를 생성 할 경우, 갑자기 쿼리 수행시간이 아주 느려지는 경우가 종종 발생

     즉 잘 수행되고 있던 SQL 문장이 새로 만든 인덱스 때문에 갑자기 아주 느려지는 것

     why?

    옵티마이져가 실행 계획을 세우게 되는데 갑자기 없었던 인덱스가 테이블에 생기면 더 최근에 만들어진 인덱스가

    더 좋을 것이라고 생각해서 잘 되고 있던 실행계획을 바꾸기때문

 

 

6. 인덱스 관리 방법

 

1) 인덱스 조회하기

 

 - 생성된 인덱스를 조회 :  USER_INDEXS 와 USER_IND_COLUMS 딕셔너리를 조회

 - 데이터베이스 전체에 생성된 내역을 조회 : DBA_INDEXES와 DBA_IND_COLUMNS 조회

 

 

 

2) 사용여부 모니터링 하기

 - 사용하지 않는 인덱스는 삭제해야 성능향상에 도움이 됨

 - 오라클 9i부터는 사용유무를 파악하는 기능을 제공함

 

 

 

 

3) INDEX Rebuild하기

 - 대량의 DML 작업등을 수행 한 후에는 일반적으로 인덱스의 밸런싱 상태를 조사해서 문제가 있을 경우

   수정을 해 주는 작업들을 수행

   즉, 인덱스는 한번 만들어 놓으면 영구적으로 잘 작동하는 것이 아니라 생성 후에도 꾸준하게 관리를 해 주어야

    좋은 성능을 기대 할 수 있다는 뜻

 

 

 

 - REBUILD 옵션 중에 ONLINE이라는 옵션도 있습니다. (Rebuild ONLINE)
    이 옵션은 REBUILD 작업 중에 데이터를 사용 가능하게 해 주지만 전체적인 성능이 많이 떨어지기 때문에

    잘 선택해서 사용해야 함

 

 

 

7. 인덱스 활용 예제

 

(1) 인덱스를 활용하여 정렬한 효과를 내는 방법

 - ORDER BY 구문을 사용하지 않고 어떻게 동일한 효과를 낼 수 있느냐가 성능상 아주 중요한 관건

   : 그 해답이 바로 인덱스!

 

 - 인덱스는 정렬이 되어 있기 때문에 테이블에서 데이터를 가져올 때 인덱스를 활용해서 가져온다면

   정렬이 된 상태로 출력 됨

 

 

(실습) 인덱스를 활용한 정렬하기

 

 

 

  2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법

 

 

 

- 왼쪽 SQL는 정렬이 발생해서 데이터가 많을 경우 속도가 아주 느림

- 오른쪽 SQL은 인덱스를 활용하여 1건의 데이터만 읽어 오도록 함(ROWNUM=1),

   이 부분을 STOPKEY라고도 한다

 

 

 

 

 

- 위 그림은 최대값을 구하는 것이므로 인덱스를 아래쪽에서부터 1건만 읽으면 간단히 해결됨

- 인덱스를 아래쪽부터 읽는 것은 오라클 Hint라는 기능을 이용하면 됨

- Hint : 실행계획을 세워주는 옵티마이저에게 사람의 의도를 알려주는 기능

 

 

- 위의 방법이 예전부터 사용되던 인덱스를 사용하여 최대/최소값을 구하는 방식입니다.

- 이 방식은 인덱스가 문제가 없을 경우에는 아무런 문제가 없지만 만약의 경우 인덱스가 삭제가 되거나

   인덱스 컬럼이 변경 된다면 잘못된 결과를 만들어 낼 수 있다.

 

 

- 위와 같은 방법을 FIRST_ROW (MAX/MIN) 방법이라고 하며 만약 인덱스가 삭제나 변경이 된다 하더라도

   MAX(name)로 인해 정상적인 결과가 나오게 됨

 

 

 

8. Invisible Index (인비저블 인덱스) - 11g New Feature

 

 - 앞에서 살펴 본 대로 인덱스가 많을 경우 DML 문장에 나쁜 영향을 주기 때문에

   사용하지 않는 인덱스는 삭제를 해 주는 것 좋음

 

 - 문제는 해당 인덱스를 삭제 하려고 했을 때 정말 사용하는지 사용하지 않는 것인지를 정확하게 알아야 한다는 것이고

    9i 버전부터 이 부분을 도와주는 기능으로 인덱스 사용 유무를 모니터링 하는 기능이 등장했지만

    만약 모니터링 기간이 잘못되었다든지 해서 인덱스를 삭제했는데 나중에 생각지도 못했던 부분에서

    문제가 발생할 수 있음

 

 - 인비저블 인덱스 : 그래서 11g 에서는 인덱스를 실제 삭제하기 전에 "사용안함" 상태로 만들어서

                              테스트를 해 볼수 있는 기능을 제공

 

 

 

- 인덱스를 INVISIBLE 한다는 의미 : 옵티마이져가 실행계획을 세울 때 해당 인덱스를 보여주지 않는다는 의미

  즉 옵티마이져는 인비저블로 설정한 인덱스는 없다고 생각하고 실행계획을 세우게 됨

 

- 그러나 DML 작업시 인덱스 내용은 계속 반영이되므로 인덱스가 지워 진 것은 아닙니다.

   따라서 인비저블로 설정한 후 점검하여 다른 SQL 문장에 영향을 주는 것이 없는 것으로 확인되면

   해당 인덱스를 지우시면 됩니다.

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.302-331

 

 

 

- 제약조건(Constraint) : 컬럼마다 정하는 규칙

                                     테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 함

 

 

 

1. 제약조건의 종류

 

 

 - 위의 제약 조건은 컬럼별로 설정

 - Primary Key를 제외한 나머지 제약조건들은 하나의 제약 조건이 여러 컬럼에 중복으로 설정 할 수 있고

    하나의 컬럼에 여러 개의 제약조건들이 중복으로 설정 할 수 있음

 

 -  어떤 컬럼에 UNIQUE 제약조건이나 Primary Key 제약조건을 설정하게 되면 해당 컬럼에 데이터는

    서로 중복이 되지 않는 다는 뜻

 

- Foreign Key를 제외한 나머지 4개는 한 개의 테이블에 설정되지만 Foreign Key는 두 개의 테이블에 동시에 설정 됨

 

 

 

 

 - 사원테이블에 입력이 될 때 부서 번호를 부서 테이블의 부서 번호 컬럼을 확인 한 후 존재하면 입력을 허용하고,

    존재하지 않는다면 에러를 발생하고 입력을 거부하게 설정

 

 - 정보를 제공해 주는 쪽 : 부서테이블(부모 테이블, Parent Table)

 - 정보를 받는 쪽 : 사원테이블(자식테이블, Child Table)

 

 - 부모 테이블쪽에 참조되는 컬럼에 설정되는 제약조건을 Reference Key(참조키)

 - 자식 테이블쪽에 참조되는 컬럼에 설정되는 제약조건을 Foreign Key(외래키)라고 부름

 

 - 참조키 제약조건은 자식 테이블에 데이터가 입력되기 위해서 부모 테이블의 특정 컬럼을 조사한 후

    해당 데이터가 있을 경우 입력 허가.

 - 부모 테이블의 데이터를 변경하려면 해당 부모 데이터를 참조하는 자식 테이블의 데이터가 없어야 함

 

 - 참조키 제약조건을 설정할 때는 자식 테이블과 부모 테이블의 해당 컬럼에 반드시 적절한 인덱스를 생성하길 권장함

 

 

 

2. 각 제약 조건의 설정 방법

 

1) 테이블 생성시에 동시에 설정하기

 

 

 

 

- 두가지 방법은 동일한 효과를 냄, 차이점은 각 제약 조건의 이름을 직접 지정하느냐 안 하느냐의 차이

- 뒤에서 제약조건을 비활성화 / 활성화 / 삭제하는 등의 관리 작업을 하려면 해당 제약조건의 이름을 알고 있어야 한다.
   그래서 제약 조건을 생성 할 때 위의 방법처럼 제약 조건의 이름을 직접 지정하는 것을 권장

   아래처럼 제약조건의 종류만 적고 이름을 적지 않게 되면 오라클이 자동으로 이름을 결정하게 되는데

   찾기가 불편한 부분이 많음

   이렇게 설정된 제약조건들은 딕셔너리에 저장되어 있으며 데이터가 입력되기 전에 참조

 

 

 

2) 테이블 생성 후 추가하기

 

 

 

 - 위 예에서 보듯이 NULL ↔ NOT NULL 로 변경하는 것은

    ADD CONSTRAINT 명령어로 하는 것이 아니라 기본값으로 허용되어 있는 NULL을 NOT NULL로 변경하는 것이기

    때문에 MODIFY 키워드를 사용해서 변경

 

 

 

- 이렇게 참조키 제약조건을 설정하실 때 주의 사항
   : 부모 테이블 쪽에 설정되는 컬럼이 Primary Key 이거나 Unique Key가 설정되어 있어야 한다

 

 

- FOREIGN KEY를 설정 후 부모 테이블 의 데이터를 지우고 싶은데 만약 자식테이블에서 부모테이블의 해당 데이터를

   참조하고 있을 경우 지울 수가 없음

- 이럴 경우를 대비해서 FOREIGN KEY를 생성할 때 ON DELETE CASCADE 옵션을 줄 수 있음

   이 옵션을 주면 부모테이블의 데이터가 지워지면 자식 테이블의 데이터도 함께 지우라는 의미가 됨
- ON DELETE SET NULL이란 옵션도 줄 수 있는 데 이 옵션은 부모테이블의 데이터가 지워질 경우 자식테이블의 값을

   NULL로 설정

 

 

 

 

 

 

자식 테이블의 deptno 컬럼에 not Null 속성이 설정되어 있는 상태에서 Foreign Key가 on delete set null 로 생성되면?!

 

 

 

 

 

 

3. 제약조건 관리하기

 

 - 테이블의 컬럼에 설정되는 각 제약조건들은 어떤 필요에 의해서 일시적으로 DISABLE / ENABLE할 수 있음

    ex) 예를 들어 이미 검증된 대량의 데이터를 테이블에 입력 할 경우는 데이터를 입력할 때 제약조건을 다시 검사 할 필요가

          없다. 이럴 경우 제약조건을 임시로 DISABLE 해 둔 후 빠르게 데이터를 입력하고 입력이 완료되면

          다시 ENABLE 하는 등의 작업을 많이 함

 

1) 제약 조건 DISABLE하기

  : DISABLE 옵션은 NOVALIDATE와 VALIDATE 두 가지가 있음

  

 - NOVALIDATE 옵션은 해당 제약조건이 없어서 데이터가 전부 들어온다는 뜻

 

 

 

 

- DISABLE NOVALIDATE로 제약조건을 DISABLE하게 되면 CONSTRAINT가 없는 것과 동일하게 작동

 

 

 

 

 - 위 테스트 결과 DISABLE VALIDATE 옵션은 결과적으로 테이블의 내용을 변경 할 수 없도록 함

 - 위 테스트는 INSERT만 했지만 UPDATE나 DELETE 또한 동일한 결과를 볼 수 있음

 

 - DISABLE 의 기본 옵션 :  NOVALIDATE

 - PRIMARY KEY나 UNIQUE 제약조건을 DISABLE 할 경우 생성되어 있던 UNIQUE INDEX 가 자동으로 삭제됨

 

 

 

 

2) 제약 조건 ENABLE하기

 

 - DISABLE되어 있던 제약조건을 ENABLE하는 방법도 NOVALIDATE / VALIDATE 두 가지가 있음

 - ENABLE NOVALIDATE는 제약조건을 ENABLE하는 시점까지 테이블에 들어 있는 데이터는 검사하지 않고

    ENABLE 한 시점 이후부터 입력되는 데이터만 제약조건을 적용하여 검사하는 옵션

 

 - ENABLE VALIDATE는 제약조건을 ENABLE하는 시점까지 테이블에 입력되어 있던 모든 데이터를 전부 검사하며

    신규로 입력되는 데이터도 전부 검사하는 옵션

 - 해당 제약조건을 ENABLE VALIDATE하게 되면 오라클이 해당 테이블에 데이터가 변경되지 못하도록(기존 데이터를

    검사해야 하므로) LOCK을 설정

 - 만약 검사도중에 제약조건을 위반하는 값이 발견되면 에러를 발생하면서 제약조건 ENABLE 작업을 취소

    그렇게 되면 DBA나 사용자가 그 위반되는 데이터를 찾아서 적절하게 조치 후 다시 ENABLE 작업을 반복해서 시도해야 함

    문제는 그 위반되는 데이터를 일일이 찾아서 조치를 해줘야 한다는 것

    데이터가 많은 테이블은 데이터 이전 후 검증하는 데 시간이 아주 오래 걸릴 수 있습니다. 기본값이 ENABLE VALIDATE 

 

 - 이런 문제를 조금이라도 해결하기 위해 ENABLE VALIDATE 일 경우 사용할 수 있는 EXCEPTIONS라는 테이블이 존재

    이 테이블에 위반 사항을 저장하게 설정하면 한결 쉽게 ENABLE 작업을 하실 수 있음!!

 

테스트용 테이블 test_enable 에 데이터를 입력

 

 

 

  해당 제약조건을 DISABLE로 변경 후 다시 입력해 보겠습니다

 

 

 

위 테스트 결과로 지금 test_enable 의 테이블에는 잘못된 데이터가 들어가 있다.
이 상태에서 제약 조건을 ENABLE

 

 

 

 - 위 테스트 결과를 보면 test_enable 테이블의 name 컬럼에 NOT NULL 제약 조건이 설정되어 있음에도 불구하고

    3행의 NULL 값의 데이터가 입력되어 있음이 확인됨

    즉, ENABLE NOVALIDATE 옵션은 ENABLE 시점에 테이블에 입력되어 있던 기존 데이터는 검사를 하지 않기 때문에

    이런 현상이 생김 그러나 ENABLE 후 입력하는 값은 에러가 나고 입력이 안됨을 볼 수 있다

 

- 앞에서 살펴본 바와 같이 ENABLE NOVALIDATE는 신규로 입력되는 데이터만 검사함

 

 

 

 

 - 위 결과처럼 테이블에 잘못된 데이터가 들어가 있기 때문에 ENABLE VALIDATE를 할 수 없음

   이럴 때 문제가 되는 행을 찾아서 수정해야 하는데 데이터가 많을 경우 일일이 그 행을 찾기가 힘들 때가 많음

   이렇게 ENABLE VALIDATE 할 때 문제가 되는 행을 별도의 테이블에 저장해서 문제 행을 쉽게 찾을 수 있도록

   해 주는 기능이 EXCEPTIONS 테이블!!(이 기능은 사용자가 별도로 생성하고 설정해야 함)

 

 

 

 

3) EXCEPTIONS 테이블을 사용하여 ENABLE VALIDATE 하기

 

 

 

 

 

4) 제약조건 조회하기

 

 - 테이블에 제약조건을 설정하면 그 내용이 딕셔너리에 저장이 되어 있음
 - 사용하는 딕셔너리는 USER_CONSTRAINTS와 USER_CONS_COLUMNS이며

    데이터베이스 전체의 제약조건을 조회하려면 DBA_CONSTRAINTS와 DBA_CONS_COLUMNS를 사용

 

 

 

 

 - constraint_type 의 타입 값 중 P: Primary Key , U :Unique , C: CHECK , R:외래키를 의미

 

  

 

 

 

5) 제약조건 삭제하기

 

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.282-299

 

 

 

 

 

 

 

 

 

 (4) 테이블 복사하기 (CTAS 라고도 합니다.)

 

  : 새로운 테이블을 생성 할 때 기존에 만들어져 있는 테이블을 참조하여 생성하는 방법.  즉, 테이블을 복사하는 의미

 

1) 모든 컬럼 다 복사하기

 

 

 

2) 특정 컬럼만 복사하기

 

 

 

3) 테이블의 구조(컬럼)만 가져오고 데이터 안 가져오기

 

 

[설명] 이 방법은 주로 데이터는 필요 없이 테이블 구조만 가져올 때 많이 사용하는 방식

 

 

 

(5) 가상 칼럼 테이블 생성하기(11g부터 추가)

 

  : 11g 부터는 테이블을 생성 할 때 가상 컬럼이라는 것을 설정 할 수 있음

 

Step 1. 가상 컬럼을 가지는 vt001 테이블을 생성

 

 

 

Step 2. vt001 테이블에 데이터를 입력 

 

 

 

 Step 3. 입력된 데이터를 조회

 

 

 

Step 4. 기존 값을 변경 한 후 가상 컬럼에 반영되는 지 확인

 

 

 

 

 (6) 파티션 테이블 생성하기

 

 : 평소에는 데이터를 디스크의 파일에 안전하게 저장 해 두었다가 필요할 때 메모리(Database Buffer Cache)로

   복사를 해와서 작업

   데이터 파일에 데이터를 저장해 두고 필요할 때 마다 Database Buffer Cache 라는 곳으로 복사해 놓고 작업

 

  이때 오라클은 Database Buffer Cache 라는 메모리 공간을 Tablespace라는 논리적인 공간으로 나누어서 사용

 

  정리를 하면 오라클에서의 작업은 평소에는 디스크의 데이터파일에 내용을 안전하게 저장하고 있다가 필요할 경우

  메모리의 Tablespace 라는 공간으로 해당 테이블을 복사해 놓고 작업을 하게 된다는 뜻

 

 - 창고처럼 작은 공간을 여러개 만들어서 데이터를 분산 시키는 방법을 테이블 파티셔닝이라고 함

 

 

 

 

 

 

- 하나의 판매 테이블의 데이터를 여러 테이블스페이스로 분산시켜서 저장하고 관리하는 기법이 파티션 테이블

- 이 테이블은 데이터의 양이 많을 경우에 주로 사용하는 방법

 

- 오라클에서는 각 버전별로 제공하는 파티셔닝 기법이 다르다.

 

 

- Oracle 11g에서 제공하는 파티셔닝 기법

​

 

 

 

(실습은 제외ㅠㅠ)

 

  ① Range PARTITION(범위 파니셔닝)
    - 주로 특정 기준에 의해서 범위를 나눌 때 사용하는 방법, 가장 기본적이고, 많이 가용되는 파티셔닝 기법

       ex) 판매 테이블을 파티셔닝 하는데 판매 날짜를 기준으로 범위를 나누는 경우나
            포털 사이트의 카페 테이블을 파티셔닝 하는데 카페 번호로 범위를 나눌 경우 등에 사용

    - 사용하기 쉽고 관리하기가 쉽다.

    - 데이터가 균일하게 분포되지 못해서 성능저하가 있을 수 있다.

       ex) 년도 별로 판매테이블을 파티셔닝 할 경우 판매가 많이 된 년도에는 데이터가 많아서 작업 속도가 느려질 것이고

             판매량이 적은 년도는 데이터가 적어서 빨리 조회되는 등의 단점이 생김

 

⇒ 이 문제는 데이터가 균등하게 분포되지 못해서 만들어지는 것이며 이러한 문제를 해결하기 위해 등장한 것이

     Hash Partition(해쉬 분할)

 

  ② HASH PARTITION
    - Hash 함수가 데이터를 각 테이블스페이스 별로 균등하게 분포시키기 때문에 데이터가 균등하게 분포되어 성능이 향상됨

    - 데이터를 사용자가 분산하지 않고 Hash 함수가 분산시키기 때문에 데이터의 관리 등이 매우 어려움

 

 

  ③ LIST PARTITION (목록 분할)
    - 파티셔닝 할 항목을 관리자가 직접 지정하는 방식

       ex) 회사 제품 중에 A 제품용 파티션, B 제품용 파티션 , C 제품용 파티션을 각각 생성하는 방식

    - 잘 설정 할 경우 빠른 성능을 보장 할 수 있지만 잘못 설정 될 경우 오히려 성능이 저하 될 수 있음

 

 

  ④ COMPOSITE PARTITION (복합 파티션)

    - 위에서 언급한 여러 가지 파티션을 복합적으로 사용하는 것

       ex) 학생이 많을 경우 학년별로 먼저 나누고 각 학년별로 다시 반으로 나누는 것처럼 티셔닝을 먼저 한 후

            다시 세부적으로 파티셔닝

 

    - 오라클 버전별로 지원되는 형태가 다름

* 8i : range - hash
* 9i : range - list 추가 지원
* 11g : range - range , list - range , list - list , list - hash 추가 지원

 

⑴ Range-Hash

  - 일자별로 먼저 파티셔닝을 하고 그 안에 또 Hash 파티셔닝을 하는 방법

  - 데이터가 어느 파티션에 속할 지 제어할 수 없음

 

⑵ Range-List

  - Range-Hash + 각 로우가 어느 파티션에 속할지 알 수 있음

 

 

  ⑤ Interval PARTITION
    - 11g부터 추가된 기능으로 Range PARTITION 의 확장 형

    - Range PARTITION에서 만약 파티션의 범위(한계)를 벗어난 데이터가 입력이 될 경우

       에러가 발생하면서 입력이 되지 않는데

       이런 문제는 interval PARTITION 을 활용하면 오라클이 필요한 파티션을 스스로 생성한 후 데이터를 입력

    - 이 때 자동으로 생성되는 파티션들은 모두 동일한 범위의 크기를 가지게 되며 파티션 이름은 오라클이 자동으로 지정

 

    - Interval PARTITION 에서 자동으로 파티션을 생성할 때 사용하는 함수

* numtoYMinterval(숫자, '단위') 이렇게 지정

  ex) 1달 단위로 만들어지게 설정하려면 numtoYMinterval(1, 'MONTH') ,
        2달 단위로 만들어지게 하려면 numtoYMinterval(2, 'MONTH'),
        1년 단위로 만들어지게 하려면 numtoYMinterval(1, 'YEAR') 등으로 설정

 

* numtoDSinterval(숫자, '단위')

  - 단위 : DAY , HOUR , MINUTE , SECOND
 ex) 예를 들어 1일 단위로 만들어 지게 하려면 Interval(numtoDSinterval(1, 'DAY')

                     1주일(7일) 단위로 만들어 지게 하려면 Interval(numtoDSinterval(7, 'DAY')

 

  ⑥ SYSTEM PARTITION
    - System PARTITION 은 PARTITION key 를 파티션 생성시에 지정하지 않고 데이터를 삽입할 때 직접 지정하는 방식

    - 검색할 때도 파티션 명을 명시해야만 해당 파티션에서 데이터를찾게 되며

       만약 명시하지 않으면 전체 파티션에서 데이터를 읽게 되어 속도가 급격히 저하 될 수 있음

 

 

 

 (7) 파티션의 인덱스 

 

  :  대량의 데이터가 들어간 파티션 테이블의 데이터를 관리할 때 해당 파티션에 인덱스를 생성 할 수 있음

 

  - 파티션을 생성할 인덱스는 크게 Local Index와 Global Index로 나뉨

 

  - Local Index : 해당 파티션에 대한 정보만 가지고 있는 인덱스

  - Global Index : 모든 파티션에 대한 정보를 전부 가지고 있는 인덱스

 

 

 

 

 

  ① Local Index

  - 파티션 하나당 인덱스 하나가 할당되어 데이터를 관리하는 방식

  - Local Index는 Local Prefixed Index 와 Local Non-Prefixed Index 로 나뉨

  - Local Prefixed Index 는 파티션을 나눌 때 기준이 되는 컬럼으로 인덱스를 생성 한 것이고
     Local Non-Prefixed Index 는 파티션 생성 기준 컬럼 이외의 컬럼으로 인덱스를 생성한 것

  - Local Prefixed Index 는 Unique / Non Unique index 모두 생성할 수 있습니다.

 

 

 

 

 

>  Local Index 생성 구문

 

SQL> CREATE INDEX idx_panmae_pdate ON panmae(pdate) LOCAL ; ​

 

 

 

 

  ② Global Index

  - 인덱스와 파티션의 컬럼이나 범위가 다르게 생성

     ex) 예를 들어 판매 일자를 기준으로 파티션 된 테이블이 있다고 가정

           이때 파티션되는 컬럼은 판매일자이지만 주로 조회되는 조건은 제품코드 일 경우 제품코드로 Local Index를

            만들 수도 있고 Global Index를 생성할 수 있습니다.

            그러나 파티션이 판매일자로 나누어져 있기 때문에 각 파티션을 제품코드로 조회 할 경우 성능이 떨어질 수 있습니다.
           그래서 Local Index를 생성할 경우보다는 Global Index를 생성하는 것이 훨씬 효과적입니다.

           즉 테이블은 판매일자를 기준으로 나누어져 있지만 인덱스는 모든 파티션의 제품코드 정보를 모아서

           새로운 파티션 인덱스를 생성

 

  - Global Index 는 하나의 인덱스에 모든 데이터를 다 넣고 관리하는 것보다

    몇 개의 별도의 인덱스 파티션으로 나누는 것이 좋음

 

>  Global Index 생성 구문

 

 

 

  - 일반적으로 Global Index보다는 Local Index를 더 많이 사용하는 이유는 운영이나 유지하는 것이 더 쉽고 효율적이기 때문

 

 

 

 

 

 

  

 (3) 컬럼의 데이터 크기를 변경하기

 

 

 

 (4) 컬럼 삭제하기

 

 

- 참조키로 설정되어 있는 부모테이블의 컬럼을 삭제하려 할 경우 에러가 발생하는데 아래와 같은 방법으로 지우면 됨

 

 

 

 

 (5) 읽기 전용 테이블로 변경하기

 

  : 어떤 경우에 특정 테이블의 내용을 모두 변경은 할 수 없게 하고 조회만 가능하도록 설정해야 한다면 트리거를 사용하거나

    제약조건을 DISABLE NOVALIDATE 해서 작업을 해야 함

 

  - 읽기 전용인 테이블이기 때문에 데이터 입력이나 테이블 구조 변경은 불가하지만 테이블 자체는 DROP 할 수 있음

 

 

 

 

 

 

 - 오라클 10g부터는 drop table로 삭제할 경우 테이블이 삭제되는 것이 아니라 마치 윈도우에서 파일 삭제시

    휴지통으로 가는 것처럼 휴지통으로 보내지고,

     삭제된 테이블의 이름은 BIN$....로 변경 됨(FLASHBACK TABLE 기능 구현을 위해)

 

 

 

 

 

 

 - 오라클은 이 딕셔너리를 Base Table과 Data Dictionary View로 나눔

 - Base Table은 DBA라 할 지라도 접근을 할 수 없음

 - 사용자(DBA 포함)들은 Data Dictionary View를 통해서만 딕셔너리를 SELECT 할 수 있음

 

 - 만약 데이터베이스에 변경 사항이 생겨 (예를 들어 새로운 테이블이 생성되는 상황) 딕셔너리 내용을 변경해야 할 경우

    사용자가 직접 수동으로 딕셔너리를 변경하지 못하고 해당 DDL문장을 수행하는 순간

    SERVER Process가 사용자를 대신해서 해당 딕셔너리 내용을 변경해 줌 

 

 

 - Base Table은 Database를 생성하는 시점에 자동으로 만들어 짐 (Create Database나 DBCA나 마찬가지)

 - Data Dictionary View 는 Catalog.sql 이란 파일이 수행되어야만 만들어 짐

   - 이 파일은 DBCA 로 Database를 생성 할 때는 자동으로 수행되지만

     CREATE DATABASE라는 명령어로 수동으로 Database를 생성할 때는 수행되지 않으므로 DBA가 수동으로 생성해야만 함

 

 - Data Dictionary View 는 크게 Static Data Dictionary View와 Dynamic Performance View로 나뉨

 

 - Static Data Dictionary view는 다시 접두어가 DBA_ , ALL_ , USER_ 로 시작되는 3가지로 나뉘게 되고

    Dynamic Performance View 는 접두어가 V$로 시작하는 것들이 대표적

 

 - USER_로 시작하는 딕셔너리는 해당 사용자가 생성한 오브젝트들만 조회 할 수 있음
   ALL_로 시작하는 딕셔너리는 해당 사용자가 생성한 오브젝트를 포함하여 해당 사용자가 접근 가능한 모든 오브젝트

   DBA_로 딕셔너리는 데이터베이스 내의 거의 모든 오브젝트들을 다 볼수 있지만 DBA 권한을 가진 사람만이 조회 가능

 

 -  Static Data Dictionary View는 내용들이 자동으로 변경되지 않고 수동으로 변경작업을 해 주어야 함

 

 

- 위 3번 과정의 ANALYZE 명령어는 실제 테이블이나 인덱스, 클러스터 등을 하나씩 조사해서

    그 결과를 딕셔너리에 반영시키는 명령어

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.214-279

 

 

 

 

- 명령어 분류

  * DML(Data Manipulation Language) : INSERT(입력) , UPDATE(변경) , DELETE(삭제) , MERGE(병합)
  * DDL(Data Definition Language) : CREATE (생성) , ALTER (수정) ,TRUNCATE (잘라내기) , DROP (삭제)
  * DCL(Data Control Language) : GRANT (권한 주기) , REVOKE (권한 뺏기)
  * TCL( Transaction Control Language): COMMIT (확정) , ROLLBACK (취소)
  * SELECT : 어떤 분류에서는 DQL (Data Query Language) 라고 하기도 함

 

 

1. INSERT

 

 (1) INSERT를 사용하여 단일 행 입력하기

 

[문법]  INSERT INTO table [(column1, column2,……)]

           VALUES (value 1 , value 2,….) ;

 

예제 1) Dept2 테이블에 아래와 같은 내용으로 새로운 부서 정보를 입력하세요.

            * 부서번호 : 9000        * 부서명 : 특판1팀        * 상위부서 : 영업부        * 지역 : 임시지역

 

 

 

* 모든 컬럼에 데이터를 넣을 경우에는 아래와 같이 테이블 이름 뒤에 컬럼 이름 생략 가능

 

 

 

 

예제 2) 아래 정보를 professor 테이블에 입력하세요.(날짜 데이터 입력하기)

             * 교수번호 : 5001      * 교수이름 : 김설희      * ID : Love_me      * POSITION : 정교수
             * PAY : 510             * 입사일 : 2011년 11월 14일

 

 

날짜 형식이 유닉스(리눅스)용 오라클(DD-MON-YY)과 윈도용 오라클(YYYY-MM-DD)이 다릅니다.
유닉스(리눅스)용 오라클에서는 날짜 형식을 미리 변경 후 위와 같이 입력하던지 아니면 날짜 부분에 TO_DATE 함수를 사용하여 TO_DATE('2011-11-14', 'YYYY-MM-DD') 형식으로 입력해야 함

 

* 날짜 형식을 변경하는 명령어

 

 

 

 

예제 3) Null 값 입력하기

* 자동 NULL 값 입력하기 : 데이터를 입력할 때 컬럼에 값을 안 주면 자동으로 NULL 값이 들어감
* 수동 NULL 값 입력하기 : 데이터부분에 NULL 값을 적어주면 됨

 

 

 

 (2) INSERT를 사용하여 여러 행 입력하기

 

 

 

 

 

 (3) INSERT ALL을 이용한 여러 테이블에 여러 행 입력하기

 

예제 1) 다른 테이블에 한꺼번에 데이터 입력하기

 

 

 

 

예제 2) 다른 테이블의 데이터를 가져와서 입력하기

  Professor 테이블에서 교수번호가 1000번에서 1999번까지인 교수의 번화와 교수의 이름은 p_01테이블에

  입력하고 교수번호가 2000번에서 2999번까지인 교수의 번호와 이름은 p_02테이블에 입력하세요.

 

 

 

 

예제 3) 다른 테이블에 동시에 같은 데이터 입력하기

  Professor 테이블에서 교수번호가 3000번에서 3999번인 교수들의 교수 번호와 이름은 p_01테이블과

  p_02테이블에 동시에 입력하세요.

 

 

 

 

2. UPDATE

 

 : UPDATE 문장은 기존 데이터를 다른 데이터로 변경할 때 사용하는 방법

 

[문법]  UPDATE  table

           SET  column = value

           WHERE  조건 ;

 

 

예제 1) Professor 테이블에서 직급이 조교수 인 교수들의 BONUS 를 100 만원으로 인상하세요.

 

 

 

 

예제 2) Professor 테이블에서 차범철 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는

            교수들의 급여를 15% 인상하세요.

 

 

※ UPDATE 문장에서 WHERE 절을 누락시키지 않도록 각별히 주의

 

 

 

 

 

3. DELETE

 

 : DELETE 문장은 데이터를 삭제하는 구문

 

[문법]  DELETE  FROM  table

           WHERE  조건 ;

 

 

예제 1) Dept2 테이블에서 부서번호(DCODE)가 9000 번에서 9100 번 사이인 매장들을 삭제하세요.

 

 

 

-  DELETE 문은 해당 데이터가 사용하고 있던 파일의 저장공간 (익스텐트 –extent)은 반납하지 않고

    데이터만 삭제하는 구문입니다.

-  사실 데이터는 삭제되지 않고 해당 블록에 그대로 남아 있으며 특별한 툴(BBED 등)을 이용하면 DELETE 된 데이터도

    전부 복구할 수 있는 방법도 있습니다. 여기서 중요한 건 데이터가 DELETE 되더라도 저장공간을 반납 하지 않기 때문에

    용량이 줄어들 지 않는다는 뜻입니다. 예를 들어 100만 건 데이터가 있는 테이블의 용량이 100MB 였는데 1만 건 만 남기고

    DELETE 한다고 해서 용량이 1MB가 되지는 않는다는 점입니다

 

- 만약 모든 데이터가 지워졌는데 용량까지 줄이고 싶다면 REORG 작업을 별도로 해줘야 한다.

 

 

 

위 DELETE 후 테이블 용량을 줄이는 방법은 주의하실 사항이 몇 가지 있습니다.
해당 테이블에 인덱스나 뷰 등이 생성되어 있다면 부가적인 작업이 더 있으니 보다 자세한 사항 은 운영 매뉴얼을 참고

★★ 'DELETE 를 수행해도 테이블 용량은 줄어들지 않는다!'

 

 

 

4. MERGE

 

 : MERGE란 여러 테이블의 데이터를 합치는 병합을 의미, 문법이 다소 복잡 ㅜ.ㅜ

 

[문법]  MERGE INTO Table1
   2      USING Table2
   3      ON ( 병합 조건절 )
   4      WHEN MATCHED THEN
   5         UPDATE SET 업데이트 내용
   6         DELETE WHERE 조건
   7      WHEN NOT MATCHED THEN
   8         INSERT VALUES(컬럼 이름) ;

 

 

 

예제 1) 일별 판매 테이블인 pt_01과 pt_02 테이블이 있고 판매 집계 테이블인 p_total 테이블이 있습니다.

            매일 새벽에 일별 판매 테이블과 판매 집계테이블을 MERGE 한다고 가정하고  MERGE 하는 SQL

 

 

- MERGE 작업 전 테이블 조회 :

 

 

 

 

 

- MERGE 작업 QUERY 1 (pt_01 테이블과 p_total 테이블 병합)

 

 

 

 

- MERGE 작업 QUERY 2 (pt_02 테이블과 p_total 테이블 병합)

 

 

 

 

- MERGE 작업 후 테이블 조회 :

 

 

 

 

 

 

 

 

5. TRANSACTION 관리하기

 

 : 트랜잭션이란 논리적인 작업 단위를 의미, 쉽게 말하면 여러가지 DML 작업들을 하나의 단위로 묶어 둔 것을 의미

 

 - 해당 트랜잭션 내에 있는 모든 DML 이 성공해야 해당 트랜잭션이 성공하는 것이고

    만약1개의 DML 이라도 실패하면 전체가 실패

 

 - COMMIT : 트랜잭션 내의 작업의 결과를 확정하는 명령어

 - ROLLBACK : 트랜잭션 내의 모든 명령들을 취소하는 명령어

 - 즉, 사용자가 10개의 DML을 연속적으로 수행 한 후 COMMIT 을 수행하면 10개의 DML 모두내용이 더 이상 취소가 안되고

    확정. 10개의 DML 을 수행 한 후 ROLLBACK 를 수행하면 가장 마지막 DML 만 취소되는 것이 아니라

    해당 트랜잭션 내의 모든 (여기서는 10개) DML 이 취소가 됩니다.

 

 

 

6. DML ERROR LOGGING 하기

​ 

 : Oracle 10g R2 버전부터 DML 작업 시 장애가 날 경우 해당 장애 내용을 별도의 테이블에 기록해 주는 기능이 제공됩니다.
   이 기능은 DBMS_ERRLOG 패키지를 통해 구현하게 되며 이 패키지를 실행하면 DML 에러를 저장하는

   로그 테이블을 생성하게 됩니다.

  로그 테이블명은 기본값은 ERR$_SUBSTR(table_name,1,25) 로 설정되며 사용자가 해당 패키지를 실행 할 때

  다른 이름으로 지정 할 수 있습니다.

 

 

 

 

 - 위의 예와 같이 DML 문장을 수행할 때 추가적인 옵션을 주어서 DML 문장 수행 시 에러가 발생 할 경우

    해당 에러를 별도의 테이블에 기록 할 수 있습니다.

    그러나 몇 가지 제한사항이 있습니다.

1. Constraint 중에 deffered constraints를 위반한 사항은 로깅되지 않습니다. 즉 immediate로 위반 사항은

    모두 기록되지만 commit 수행시에 일괄적으로 체크되는 경우는 기록되지 않습니다.
2. Direct Path 방식으로 데이터를 입력하는 INSERT나 MERGE일 경우에 Unique 제약조건 위반이나

    Index 조건 (Unique Index) 위반일 경우 기록되지 않습니다.
3. 모든 UPDATE나 MERGE일 경우에 Unique 제약조건 위반이나 Index 조건 (Unique Index) 위반일 경우

    기록되지 않습니다

 

이 기능은 PL/SQL 에서도 동일하게 사용 가능 합니다.

그러나 이 기능은 DML 작업에 부하를 주게 되어 전체적인 DML 작업의 성능이 저하된다는 단점이 있으니 성능이 중요한 DML 작업등이나 실시간 변경 량이 많은 테이블 등에는 충분한 테스트 를 거친 후 사용 여부를 결정하시길 바랍니다. 

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.192-212