본 절에서는 테이블을 생성, 변경, 제거하는 방법을 설명한다.

테이블 생성

테이블은 다음 같은 경우에 따라 생성 요건이 다르다.

테이블을 생성하기 위해서는 CREATE TABLE 문을 사용해야 한다.

다음은 테이블을 생성할 때 포함되는 구성요소이다.

다음은 테이블을 생성하는 예이다.

CREATE TABLE DEPT
  (
      DEPTNO    NUMBER PRIMARY KEY,
      DEPTNAME  VARCHAR(20),
      PDEPTNO   NUMBER
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;

CREATE TABLE EMP
  (
      EMPNO     NUMBER PRIMARY KEY,
      ENAME     VARCHAR(16) NOT NULL,
      ADDR      VARCHAR(24),
      SALARY    NUMBER,
      DEPTNO    NUMBER,
      CHECK (SALARY >= 10000),
      FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;  

위의 예에서 보듯이 테이블 EMP는 다섯 개의 컬럼(EMPNO, ENAME, ADDR, SALARY, DEPNO)으로 구성되어 있으며, 4개의 제약조건으로 선언되어 있다. 또한 테이블 스페이스 my_space에 저장되며 디스크 블록 파라미터의 세부 항목(PCTFREE, INITRANS)을 모두 설정하였다.

테이블 변경

테이블은 다음과 같은 경우에 따라 변경 요건이 다르다.

테이블을 변경하기 위해서는 ALTER TABLE 문을 사용해야 한다.

다음은 테이블을 변경할 때 포함되는 구성요소이다.

다음은 [예 4.1]에서 생성한 EMP 테이블의 속성을 변경하는 예이다.

테이블 제거

테이블은 다음 같은 경우에 따라 제거 요건이 다르다.

테이블을 제거하기 위해서는 DROP TABLE 문을 사용해야 한다.

다음은 테이블을 제거하는 예이다.

DROP TABLE EMP;

다른 사용자가 소유한 테이블을 제거하려면, 반드시 다른 사용자의 이름을 명시한 후 DROP TABLE 문을 실행해야 한다.

예를 들면 다음과 같다.

DROP TABLE John.EMP;

제거하려는 테이블의 기본 키가 다른 테이블의 참조 무결성 제약조건으로 정의된 경우 참조된 테이블은 바로 제거할 수 없다. 이러한 경우에는 참조하는 테이블을 먼저 제거하거나 참조하는 테이블에 정의된 참조 무결성 제약조건을 제거해야 한다.

참조하는 테이블에 정의된 참조 무결성 제약조건을 제거하기 위해서는 DROP TABLE 문에 CASCADE CONSTRAINTS 절을 삽입해야 한다.

예를 들면 다음과 같다.

DROP TABLE EMP CASCADE CONSTRAINTS; 

테이블을 효율적으로 관리하기 위해서는 각각의 경우에 맞는 적절한 조치 방법을 수행해야 한다.

예를 들면 다음과 같은 경우이다.

Tibero에서는 테이블의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

Tibero는 테이블에 대해 중복된 컬럼 값을 압축하여 저장공간을 절약하는 압축 기능을 제공한다. 값 블록에 존재하는 중복된 컬럼 값을 한번만 저장함으로써 압축을 수행하게 된다. 이런 중복 컬럼 값들이 저장되는 공간을 심볼 테이블이라고 한다. 심볼 테이블은 해당 블록 안에 저장되기 때문에 압축된 컬럼의 원래 값을 참조하기 위해서는 해당 블록만을 참조하면 된다.

압축된 테이블에 대한 DML 지원은 일반 테이블과 동일하다. 즉, insert, update, delete 등의 DML을 지원한다. bulk가 아닌 일반적인 insert 문으로 추가된 로우는 압축이 되지 않으므로 비 압축 테이블에 대한 insert와 동일한 성능을 가진다. delete 또한 비 압축 테이블에 대한 delete와 동일한 성능을 가진다. 하지만 update는 압축을 해제해야 하는 경우가 있으므로 비 압축 테이블에 대한 update 보다 성능이 좋지 않을 수 있다.

압축을 수행하면 디스크 공간을 절약 할 수 있지만 압축을 위해 CPU를 더 많이 소모한다. 테이블에 DML이 많은 경우 점점 더 압축 효율이 낮아지게 된다. 따라서 OLTP 환경 보다는 OLAP 환경에서 더 유리하다.

테이블 압축 대상

압축은 테이블과 파티션, 서브 파티션에 대해 가능하다. 파티션과 서브 파티션 각각에 대해 압축 유무를 지정할 수 있다. 즉, 한 파티션은 압축하고 한 파티션은 압축하지 않은 상태로 테이블을 생성할 수 있다.

CREATE TABLE EMP (
       EMPNO DECIMAL(4),
       ENAME VARCHAR(10),
       JOB VARCHAR(9),
       MGR DECIMAL(4),
       HIREDATE VARCHAR(14),
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2))
       COMPRESS; 

CREATE TABLE EMP (
       EMPNO DECIMAL(4),
       ENAME VARCHAR(10),
       JOB VARCHAR(9),
       MGR DECIMAL(4),
       HIREDATE VARCHAR(14),
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2))
       COMPRESS
   PARTITION BY RANGE(EMPNO)
   ( PARTITION EMP_PART1 VALUES LESS THAN(500),
     PARTITION EMP_PART2 VALUES LESS THAN(1000) NOCOMPRESS,
     PARTITION EMP_PART3 VALUES LESS THAN(1500),
     PARTITION EMP_PART4 VALUES LESS THAN(2000) NOCOMPRESS,
     PARTITION EMP_PART5 VALUES LESS THAN(MAXVALUE)); 

테이블 압축 상태 확인

*_TABLES, *_TBL_PARTITIONS 정적 뷰를 쿼리해 보면 테이블의 압축 상태를 알 수 있다. compression 컬럼의 값이 'YES'인 경우 추가적인 DML에 대해 압축을 수행하게 된다.

select table_name, compression from user_tables;

TABLE_NAME      COMPRESSION
--------------- ------- 
EMP             YES

테이블 압축 방법

다음의 경우 테이블에 데이터가 압축된다.

위 경우 처럼 대량 insert하는 경우에는 테이블에 EXCLUSIVE LOCK을 잡기 때문에 다른 DML을 수행할 수 없다. 압축된 후 위 경우 이외 수행되는 일반적인 insert, update 문의 로우 값은 압축되지 않는다.

기존 테이블 압축 및 압축 해제

ALTER TABLE MOVE 문을 이용하면 기존 테이블에 대한 압축 상태 변경을 할 수 있다. 즉, 압축된 테이블을 압축 해제하거나 압축 해제된 테이블을 압축할 수 있다. 단, ALTER TABLE MOVE 문이 수행되는 동안에는 테이블에 EXCLUSIVE LOCK을 잡게 되므로 다른 DML을 수행할 수 없다. 파티션을 가진 테이블의 경우 테이블 자체가 아닌 파티션 단위로 MOVE를 수행해야 한다.

만약, DML을 수행하는 도중에 압축 상태를 바꾸려는 경우 Exchange DDL 기능을 이용하면 Online 중에 테이블을 압축 또는 압축 해제할 수 있다.

ALTER TABLE TBL_COMP MOVE COMPRESS;
ALTER TABLE EMP MOVE PARTITION EMP_PART1 NOCOMPRESS; 

ALTER TABLE 문을 이용하면 추가적인 DML에 대해 압축을 수행할지 여부를 설정할 수 있다. 압축이 지정된 테이블에 ALTER TABLE 문으로 압축을 하지 않기로 지정하면 이후 수행되는 Direct Path Loader, Parallel Insert 등에 대해 압축을 수행하지 않게 된다. 하지만 기존의 데이터의 상태는 바뀌지 않는다.

ALTER TABLE EMP COMPRESS; 

테이블을 압축할 때 제약 사항

한 번이라도 압축이 지정된 테이블에는 기본 값이 지정된 컬럼 추가, 컬럼 삭제 DDL을 수행할 수 없으며, Long 타입 컬럼을 가지고 있는 압축된 테이블에는 컬럼 추가 DDL이 허용되지 않는다.

INDEX ORGANIZED TABLE은 인덱스의 B-TREE 구조를 이용해 데이터를 저장하는 형태의 테이블을 말한다. 일반적인 테이블에서는 데이터가 로우 단위로 무작위로 블록에 저장되지만, INDEX ORGANIZED TABLE은 인덱스와 유사한 형태로 기본 키를 기준으로 로우가 정렬되어 인덱스 리프 블록에 저장된다.

로우가 너무 크거나 지역 효율성을 위해 특정 컬럼부터는 데이터 영역에 저장할 수도 있다. 이를 오버플로우 데이터 영역이라고 한다.

INDEX ORGANIZED TABLE은 다음과 같은 장점을 가진다.

단, 잦은 수정이 발생할 경우 인덱스를 재구조화하는 부담이 생기므로 DML이 자주 발생하는 환경에서는 적합하지 않을수 있다. INDEX ORGANIZED TABLE은 기본 키로 전체 로우가 정렬되어 저장되므로 다른 키로 인덱스를 만들고자 하는 경우 SECONDARY INDEX를 만들 수 있다.

INDEX ORGANIZED TABLE 생성

INDEX ORGANIZED TABLE은 CREATE TABLE 문 뒤에 ORGANIZATION INDEX 구문을 덧붙여 생성할 수 있다. 이때 반드시 기본 키(primary key) 선언을 해주어야 한다.

추가로 줄수 있는 파라미터는 다음과 같다.

CREATE TABLE TBL_IOT
  (
      COL1 NUMBER,
      COL2 VARCHAR2(20),
      COL3 VARCHAR2(10),
      COL4 VARCHAR2(10),
      PRIMARY KEY (COL1, COL2)
  )
  ORGANIZATION INDEX
  PCTTHRESHOLD 40
  OVERFLOW; 

INDEX ORGANIZED TABLE을 생성할 때 다음의 제약조건을 유의한다.

INDEX ORGANIZED TABLE 삭제

DROP TABLE 구문으로 삭제할 수 있다.

DROP TABLE TBL_IOT;

본 절에서는 제약조건을 선언하고 변경, 제거하는 방법을 설명한다.

테이블을 생성할 때 제약조건을 선언하는 방법은 다음과 같다.

제약조건 선언

제약조건은 삭제 가능성, 제약조건에 포함되는 컬럼의 개수 등에 따라 선택적으로 선언할 수 있다. 제약조건을 어떻게 선언하든 테이블 내에서 미치는 영향은 동일하다. 제약조건을 선언한 후 정의 또는 상태를 변경하기 위해서는 제약조건을 선언할 때 제약조건의 이름을 설정해야 한다. 제약조건의 이름을 찾아 변경한다.

제약조건에 이름을 설정하기 위해서는 제약조건을 선언할 때 예약어 CONSTRAINT와 제약조건의 이름을 추가로 정의해야 한다.

다음은 [예 4.1]에서 선언한 제약조건에 이름을 설정하는 예이다.

CREATE TABLE DEPT
  (
      DEPTNO    NUMBER PRIMARY KEY,
      DEPTNAME  VARCHAR(20),
      PDEPTNO   NUMBER
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3;

CREATE TABLE EMP
  (
      EMPNO       NUMBER        PRIMARY KEY,
      ENAME       VARCHAR(16)   NOT NULL,
      ADDR        VARCHAR(24),
      SALARY      NUMBER,
      DEPTNO      NUMBER,
      CONSTRAINT  SALARY_MIN CHECK (SALARY >= 10000),
      CONSTRAINT  DEPTNO_REF FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
  )
  TABLESPACE my_space
  PCTFREE 5 INITRANS 3; 

제약조건은 다음과 같은 경우에 따라 사용하는 문법이 다르다.

제약조건 변경

Tibero에서는 이미 선언된 제약조건을 변경할 수 있다. 제약조건은 ALTER TABLE 문에서 변경할 수 있다. 단, 변경할 수 있는 테이블에 한해서만 제약조건을 변경할 수 있다.

다음은 제약조건을 변경하는 예이다.

제약조건 제거

제약조건을 제거하기 위해서는 ALTER TABLE 문에 DROP 절을 삽입해야 한다.

기본 키, 유일 키 제약조건을 제외하고는 반드시 제약조건의 이름이 있어야 한다. 제약조건을 선언할 때 제약조건의 이름을 명시하지 않으면 Tibero가 임의의 이름을 자동으로 생성한다.

제약조건의 이름을 설정하지 않은 경우 USER_CONSTRAINTS 뷰에서 해당 제약조건의 이름을 확인하여 이를 변경 또는 제거할 수 있다.

다음은 제약조건을 제거하는 예이다.

ALTER TABLE EMP
        DROP PRIMARY KEY;
  ... 기본 키가 설정된 제약조건을 제거한다. ...

ALTER TABLE EMP
        DROP CONSTRAINT SALARY_MAX;
  ... 제약조건의 이름이 SALARY_MAX인 제약조건을 제거한다. ...

제약조건의 상태는 다음과 같이 두 가지로 나뉜다.

제약조건 상태 변경

제약조건의 상태를 변경하기 위해서는 ALTER TABLE 문을 사용해야 한다.

다음은 제약조건의 상태를 변경하는 예이다.

Tibero에서는 제약조건의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

PCTFREE는 디스크 블록에 저장된 스키마 객체의 갱신에 대비하여 얼마만큼의 여유 공간을 남길 것인가를 설정하는 파라미터이다.

퍼센트 값으로 표현하며 1에서 99 사이의 임의 정수로 설정할 수 있다. 디스크 블록의 여유 공간이 PCTFREE 파라미터에 설정한 값 이하로 떨어질 때까지 계속 새로운 로우를 삽입한다. PCTFREE 파라미터에 설정한 값 이하인 경우에는 더 이상 새로운 로우를 삽입하지 않으며, 남은 공간은 기존 로우의 갱신에 대비하게 된다.

디스크 블록 내의 빈 공간이 PCTFREE 파라미터의 값보다 작아지면, 디스크 블록 내의 객체를 삭제한다. 빈 공간이 PCTFREE 파라미터의 값보다 커지더라도 바로 새로운 객체를 삽입하지는 않는다. 이후에 충분한 공간이 생겼을 때 디스크 블록에 삽입된다.

현재 디스크 블록에 저장된 객체가 갱신되어 크기가 증가할 가능성이 있는 경우 PCTFREE 파라미터의 값을 크게 설정해야 한다. 이때 PCTFREE 값이 작은 경우와 비교하여 하나의 디스크 블록에 저장되는 객체의 수가 상대적으로 적어지므로, 같은 수의 객체를 저장하는 데에 더 많은 디스크 블록을 필요로 하게 된다. 이러한 점은 데이터베이스 성능 저하의 원인이 될 수 있다.

반면에 하나의 객체를 여러 디스크 블록에 저장해야 하는 가능성이 적어지므로 성능 향상에 도움이 될 수도 있다. 따라서 PCTFREE 파라미터의 값은 데이터베이스를 운용하며 적절하게 설정해야 하며, 객체의 갱신이 많이 발생하는 경우에는 PCTFREE 파라미터의 값을 크게 잡는 것이 좋다. 디폴트로 설정될 PCTFREE 파라미터의 값은 10%이다.

디스크 블록을 액세스하는 트랜잭션 내의 갱신 연산이 객체의 크기를 증가시키지 않거나 트랜잭션이 읽기 연산만으로 이루어져 있다면, PCTFREE 파라미터의 값을 매우 작게 설정할 수 있다. 예를 들어 PCTFREE 값을 5로 설정할 수 있다.

하나의 디스크 블록은 동시에 여러 트랜잭션에 접근할 수 있으며 디스크 블록에 포함되어 있는 각 로우는 자신을 마지막으로 생성, 갱신, 삭제한 트랜잭션의 정보를 가지고 있다. 이러한 트랜잭션의 데이터를 디스크 블록 내의 한 곳에 모아두는 것을 트랜잭션 엔트리 리스트(transaction entry list)라고 한다.

트랜잭션 엔트리 리스트는 디스크 블록의 헤더에 포함되며 디스크 블록이 생성될 때 최초 크기는 INITRANS 파라미터에 설정된 값이 된다. 리스트 내의 트랜잭션 엔트리는 트랜잭션이 커밋되면 다른 트랜잭션에 의해 다시 사용할 수 있다.

예를 들어 더 많은 트랜잭션 엔트리가 필요한 경우에는 전체 리스트의 크기(트랜잭션 엔트리의 개수)를 하나씩 증가시킨다. 이때 디스크 블록의 크기에 따라 트랜잭션 엔트리의 최대 개수가 정해진다. 즉, 디스크 블록의 크기가 커질수록 트랜잭션 엔트리의 개수가 증가하게 된다. 단, 최대 255개를 초과하지 않아야 한다.

트랜잭션 엔트리 개수가 최대 개수에 도달하면 더 이상 리스트의 크기를 증가시키지 않으며, 트랜잭션의 실행을 중지하고 대기한다. 트랜잭션은 다른 트랜잭션이 끝나고 기존의 트랜잭션 엔트리를 다시 사용할 수 있게 되면 실행을 계속한다.

트랜잭션 엔트리 리스트를 증가시키는 작업은 처리 비용이 필요하므로 자주 하지 않는 것이 좋다. 따라서 하나의 디스크 블록을 여러 트랜잭션에서 액세스할 가능성이 높은 경우에는 INITRANS 파라미터의 값을 처음부터 높게 설정해 주는 것이 중요하다. INITRANS 파라미터를 설정하지 않으면 기본값은 2이다.

PCTFREE와 INITRANS 파라미터는 스키마 객체 단위로 설정할 수 있다. 스키마 객체는 항상 같은 파라미터의 값을 갖는다. 파라미터는 스키마 객체를 생성하거나 변경할 때 설정할 수 있다.

다음은 테이블 EMP를 생성할 때 파라미터를 설정하는 예이다.

CREATE TABLE EMP (
                   ENAME    VARCHAR(16) NOT NULL,
                   ADDR     VARCHAR(24),
                   SALARY   INT,
                   DEPTNO   INT
                 )
                 PCTFREE  5
                 INITRANS 5;

디스크 블록의 파라미터의 값을 변경하더라도 디스크 블록에는 바로 반영되지는 않는다. 파라미터별로 디스크 블록에 반영되는 경우는 다음과 같다.

파라미터는 ALTER TABLE 문을 이용하여 변경할 수 있다. 예를 들면 다음과 같다.

ALTER TABLE EMP PCTFREE 10; 

인덱스를 생성할 때에는 INITRANS 파라미터의 값만 설정할 수 있다. 다음의 SQL 문장은 테이블 EMP의 인덱스를 생성할 때 파라미터를 설정하는 예이다.

CREATE INDEX EMP_DEPTNO_IDX ON EMP (DEPTNO) INITRANS 5;

Tibero는 기본 키, 유일 키 그리고 외래 키에 제약조건이 설정된 컬럼을 제외하고는 임의의 컬럼에 인덱스를 생성하거나 제거할 수 있다. 인덱스의 이름은 사용자가 별도로 지정하지 않으면 디폴트로 지정된 제약조건의 이름과 동일하게 생성된다.

인덱스 생성

인덱스는 다음 같은 경우에 따라 생성, 제거 요건이 다르다.

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용해야 한다.

다음은 인덱스를 생성할 때 포함되는 구성요소이다.

다음은 테이블 EMP의 컬럼 DEPTNO에 인덱스를 생성하는 예이다. 인덱스의 이름은 EMP_FK로 설정한다.

CREATE INDEX EMP_FK ON EMP (DEPTNO);

인덱스 제거

인덱스를 제거하기 위해서는 DROP INDEX 문을 사용해야 한다.

인덱스는 테이블처럼 독립적인 스키마 객체이므로 인덱스를 제거하더라도 테이블의 데이터에는 영향을 미치지 않는다. 단, 인덱스를 제거하게 되면 해당 컬럼의 데이터를 조회할 때 이전과 다르게 조회 속도가 느려질 수도 있다. 인덱스가 더 이상 필요하지 않는 경우에는 제거하는 것이 데이터베이스 성능 향상에 도움이 된다.

다음은 [예 4.22]에서 생성한 인덱스 EMP_FK를 제거하는 예이다.

DROP INDEX EMP_FK;

Tibero에서는 인덱스의 기본 구조로 B-TREE를 제공한다. 이를 이용하여 단일 키 검색(single key search), 범위 검색(range search), 복합 키 검색(composite key search)을 수행할 수 있다.

인덱스 검색 유형

인덱스를 이용하여 테이블의 로우를 검색하는 방법은 다음과 같다.

인덱스의 효율적인 관리 지침

인덱스를 효율적으로 관리하기 위한 지침은 다음과 같다.

Tibero에서는 인덱스의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

Tibero에서는 인덱스의 사용 여부를 모니터링할 수 있는 기능을 제공하고 있다. 인덱스 모니터링의 결과는 V$OBJECT_USAGE를 조회하여 알 수 있다.

다음은 인덱스 사용 여부를 모니터링하는 예이다.

SQL> CREATE TABLE T (A NUMBER);
Table 'T' created.

SQL> CREATE INDEX I ON T(A);
Index 'I' created.

SQL> ALTER INDEX I MONITORING USAGE;
Index 'I' altered.

SQL> SELECT /*+ index(t i) */ * from t where a > 0;
0 rows selected.

SQL> SELECT USED FROM V$OBJECT_USAGE;
   USED
-------
      Y

1 row selected.

본 절에서는 뷰를 생성, 변경, 제거하는 방법을 설명한다.

뷰 생성

뷰는 다음 같은 경우에 따라 생성 요건이 다르다.

뷰를 생성하기 위해서는 CREATE VIEW 문을 사용해야 한다. 모든 기반 객체(base objects)의 액세스 권한을 갖고 있어야 하며, 기반 테이블의 수에 상관 없이 액세스 권한이 있어야 한다.

예를 들면 다음과 같다.

CREATE VIEW MANAGER AS
       SELECT * FROM EMP
       WHERE DEPTNO = 1;


CREATE VIEW EMP_DEPT AS
       SELECT E.EMPNO, E.ENAME, E.SALARY, D.DEPTNO, D.LOC
       FROM EMP E, DEPT D
       WHERE E.DEPTNO = D.DEPTNO;

뷰를 이용하여 수행할 수 있는 연산은 기반 테이블에 대한 뷰 정의자가 수행할 수 있는 연산의 교집합이다. 예를 들어 뷰 EMP_DEPT를 정의한 사용자가 테이블 EMP에 대하여 삽입, 제거 연산이 가능하고 테이블 DEPT에 대하여 삽입, 갱신 연산이 가능하다면 뷰 EMP_DEPT에 대해서는 삽입 연산만 할 수 있다.

뷰는 테이블과 같이 액세스 권한을 다른 사용자에게 부여할 수 있다. 단, 뷰를 정의한 기반 객체에 대한 GRANT OPTION 또는 ADMIN OPTION과 함께 액세스 권한을 부여 받아야 한다.

뷰에 대한 액세스 권한을 부여 받은 사용자는 그 뷰를 정의한 기반 객체에 직접 액세스할 수 있는 권한이 없어도 그 뷰를 통하여 액세스할 수 있다. 단, 수행할 연산에 대한 권한은 뷰의 정의자가 가지고 있어야 한다.

다음은 뷰를 생성하는 예이다.

CREATE VIEW V_PROD AS
       SELECT PROD_ID, PROD_NAME,PROD_COST
       FROM PRODUCT
       WHERE PROD_ID= 100001;

뷰 변경

뷰 또는 기반 객체의 변경으로 인해 사용할 수 없게 된 뷰를 다시 사용하기 위해서는 CREATE OR REPLACE VIEW 문을 사용해야 한다. 단, 뷰를 생성하고 제거할 수 있는 권한이 있어야 한다.

다음은 뷰를 변경하는 예이다.

CREATE OR REPLACE VIEW MANAGER AS
       SELECT * FROM EMP
       WHERE DEPTNO = 2;

위와 같은 SQL 문장을 실행하면 다른 사용자에게 부여한 뷰 MANAGER의 권한이 그대로 남아 있게 된다. 반면에 DROP VIEW와 CREATE VIEW 문을 연속으로 사용하면 뷰 MANAGER의 권한은 없어진다.

뷰 제거

뷰는 다음 같은 경우에 따라 제거 요건이 다르다.

뷰를 제거하기 위해서는 DROP VIEW 문을 사용해야 한다.

다음은 뷰를 제거하는 예이다.

DROP VIEW EMP_DEPT;

모든 뷰는 SQL 질의가 가능하나 삽입, 갱신, 제거가 불가능한 뷰가 존재한다. 또한 대상 컬럼에 따라 삽입, 갱신이 가능하거나 불가능한 뷰도 존재한다.

삽입, 갱신, 제거를 수행할 수 있는 뷰를 갱신 가능한 뷰(updatable view)라고 한다.

다음의 뷰는 갱신 가능한 뷰가 아니다.

조인 뷰

두 개 이상의 테이블을 조인하여 생성한 뷰를 조인 뷰라고 한다.

조인 뷰를 갱신하는 경우 INSERT, UPDATE, DELETE 연산은 뷰를 구성하는 기반 테이블 중 키 보존 테이블(key-preserved table)의 특성을 만족하는 테이블에 한해서만 수행한다.

키 보존 테이블이란 기본 키를 가지고 있는 테이블이다. 하나의 뷰에서 키 보존 테이블이 여러 개가 존재하는 경우 하나의 DML 문장은 이 중 하나의 테이블에 대해서만 연산을 수행한다. 다시 말해 삽입, 갱신, 제거가 일어나는 컬럼이 이 키 보존 테이블의 컬럼이어야 한다는 뜻이다.

DELETE 연산을 하는 경우 컬럼을 명시적으로 지정하지 않으므로 키 보존 테이블이 여러 개가 존재하는 경우에 제거 연산이 일어나는 테이블은(FROM 절에서 명시된 순서) 맨 처음이 키 보존 테이블이 된다.

[예 4.25]에서 생성한 뷰 EMP_DEPT는 테이블 EMP의 외래 키 컬럼 DEPTNO와 테이블 DEPT의 기본 키 컬럼 DEPTNO를 조인하여 얻어진 뷰이다. 테이블 EMP 내에 같은 DEPTNO 값을 갖는 로우가 여러 개일 수 있으므로 뷰 EMP_DEPT의 질의 결과의 로우 중에는 같은 DEPTNO 값이 여러 로우에 걸쳐 나타날 수 있다.

하지만 컬럼 EMPNO는 테이블 EMP의 기본 키 컬럼이므로 뷰 EMP_DEPT의 질의 결과의 로우 중 하나의 EMPNO 값이 여러 로우에 중복되어 나타날 수 없다. 따라서 뷰 EMP_DEPT의 키 컬럼은 EMPNO이며 같은 컬럼을 키 컬럼으로 갖는 테이블 EMP가 키 보존 테이블이다.

일반적으로 기본 키와 외래 키의 컬럼처럼 일:다(one-to-many) 관계를 갖는 조인 컬럼에 의해 생성된 조인 뷰에서 다(many) 측의 테이블, 외래 키 조인 컬럼을 갖는 테이블이 키 보존 테이블이 된다. 셋 이상의 기반 테이블을 정의한 뷰에서도 마찬가지이다.

다음은 뷰 EMP_DEPT에 INSERT, UPDATE, DELETE 연산을 수행하는 예이다. 세 문장 모두 테이블 EMP의 컬럼에 삽입, 갱신, 제거 연산을 수행한다.

INSERT INTO EMP_DEPT (EMPNO, ENAME, SALARY)
        VALUES (1562, 'Brown', 35000);

UPDATE EMP_DEPT SET SALARY = SALARY * 1.1
        WHERE DEPTNO = 5;

DELETE FROM EMP_DEPT
        WHERE ENAME = 'Scott'; 

INSERT 문에서 값이 지정되지 않은 컬럼은 NULL 값이 삽입되거나 기본 키의 값으로 채워진다. 단, NOT NULL 제약조건이 설정된 경우 에러를 반환한다.

뷰를 생성할 때 WITH CHECK OPTION 제약을 사용할 수도 있는데, 이때 갱신, 삽입, 제거가 가능한 조건이 약간 변경된다. WITH CHECK OPTION 제약이 명시된 뷰를 갱신할 때는 갱신되는 로우가 갱신 후에도 뷰에 의해 SELECT 되는 경우에만 그 로우의 갱신이 허용된다. 조인 뷰의 경우는 조인된 컬럼 또는 반복되는 컬럼은 무조건 갱신이 불가능하다는 제약조건이 추가된다.

삽입의 경우도 갱신과 마찬가지로 삽입하는 로우가 뷰에 의해 SELECT되어야 한다. 단, 기반 테이블이 하나일 경우에만 가능하며 조인 뷰의 경우에는 삽입할 수 없다.

제거의 경우는 제거의 대상이 되는 키 보존 테이블이 반복되지 않으면 가능하다.

Tibero에서는 뷰의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

본 절에서는 시퀀스를 생성, 변경, 제거하는 방법을 설명한다.

시퀀스는 여러 개의 트랜잭션이 서로 겹치지 않는 고유번호를 만들어낼 때 사용된다.

시퀀스를 사용하지 않으면 고유번호를 만들어내기 위해서 마지막으로 사용된 번호를 기억하는 테이블을 만들고, 각 트랜잭션이 해당 값을 읽어서 하나씩 증가시켜야 한다. 이러한 방법을 사용하면 고유번호를 생성하는 모든 트랜잭션 사이에 잠금(Lock)으로 인한 데이터 충돌이 발생하게 된다. 이로 인해 데이터베이스 성능이 저하되는 원인이 될 수 있다.

시퀀스 생성

시퀀스는 다음 같은 경우에 따라 생성 요건이 다르다.

시퀀스를 생성하기 위해서는 CREATE SEQUENCE 문을 사용해야 한다.

다음은 시퀀스를 생성할 때 포함되는 구성요소이다.

시퀀스는 데이터베이스 성능 향상을 위해 내부적으로 메모리에 값을 캐시한다. MAX_SEQ_BUFFER 파라미터에 캐시의 크기를 지정하며 기본값은 20이다.

시스템이 정상적으로 종료될 경우 캐시에 존재하지만 아직 실제로 쓰이지 않은 값은 디스크에 저장되어 다음번 Tibero가 기동할 때 사용할 수 있다. 하지만 시스템이 비정상적으로 종료될 경우 캐시에 존재하던 값은 모두 사용된 것으로 간주되며 캐시의 최대 크기만큼 시퀀스의 값을 건너뛸 수 있다.

이러한 경우가 발생하지 않으려면 시퀀스를 NOCACHE로 선언해야 한다. 하지만 시퀀스를 사용할 때마다 디스크 액세스가 일어나므로 데이터베이스 성능이 저하된다. 특수한 상황이 아니면 NOCACHE를 사용하지 않기를 권장한다.

다음은 시퀀스를 생성하는 예이다.

CREATE SEQUENCE NEW_ID
       MINVALUE 1000
       MAXVALUE 9999
       INCREMENT BY 10
       CACHE 100
       NOCYCLE;

다음은 시퀀스의 값을 사용하는 예이다.

CREATE TABLE EMP_ID (ID NUMBER, NAME VARCHAR(30));

INSERT INTO EMP_ID VALUES(NEW_ID.NEXTVAL, 'Peter'); 

시퀀스에 일단 사용된 값은 해당 트랜잭션이 롤백되거나 시스템이 비정상적으로 종료되어도 재사용되지 않는다.

시퀀스 변경

시퀀스는 다음 같은 경우에 따라 변경 요건이 다르다.

시퀀스를 변경하기 위해서는 ALTER SEQUENCE 문을 사용해야 한다.

다음은 시퀀스를 변경하는 예이다.

ALTER SEQUENCE NEW_ID
      MAXVALUE 99999
      INCREMENT BY 1
      CACHE 200;

Tibero는 시퀀스를 사용할 때 사용자가 마지막으로 NEXTVAL로 적용한 값이 아니라 다음 사용자가 NEXTVAL을 수행할 때 가져갈 값을 저장한다. 따라서 ALTER SEQUENCE 문을 실행했을 때의 결과가 다른 DBMS와 다를 수 있다.

다음은 내부적으로 '다음번에 받아갈 값 = 10'을 기억하고 있으며, 마지막 결과가 10이 되는 시퀀스의 예이다.

SQL> CREATE SEQUENCE S1 START WITH 10 INCREMENT BY 1;
created

SQL> ALTER SEQUENCE S1 INCREMENT BY 5;
altered

SQL> SELECT S1.NEXTVAL FROM DUAL;
   NEXTVAL
----------
        10

1 selected

시퀀스 제거

시퀀스는 다음 같은 경우에 따라 제거 요건이 다르다.

시퀀스를 제거하기 위해서는 DROP SEQUENCE 문을 사용해야 한다.

다음은 시퀀스를 제거하는 예이다.

DROP SEQUENCE NEW_ID;

Tibero에서는 시퀀스의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

본 절에서는 동의어를 생성, 제거하는 방법을 설명한다.

동의어 생성

동의어는 다음 같은 경우에 따라 생성 요건이 다르다.

동의어를 생성하기 위해서는 CREATE SYNONYM 문을 사용해야 한다.

다음은 동의어를 생성할 때 포함되는 구성요소이다.

다음은 동의어를 생성하는 예이다.

CREATE SYNONYM T1 FOR U1.EMP; 

동의어 제거

정의된 동의어를 변경하기 위해서는 먼저 동의어를 제거하고 다시 생성해야 한다.

동의어는 다음 같은 경우에 따라 제거 요건이 다르다.

동의를 제거하기 위해서는 DROP SYNONYM 문을 사용해야 한다.

다음은 동의어를 제거하는 예이다.

DROP SYNONYM T1;

본 절에서는 공용 동의어를 생성, 제거하는 방법을 설명한다.

공용 동의어 생성

동의어를 모든 사용자가 액세스할 수 있도록 생성할 수 있다. 이를 공용 동의어(PUBLIC SYNONYM)라고 한다. 공용 동의어는 Tibero에서 정의하고 있는 PUBLIC이라는 특수한 사용자가 소유하는 동의어이다.

공용 동의어를 생성하기 위해서는 CREATE PUBLIC SYNONYM 문을 사용해야 한다.

다음은 공용 동의어를 생성할 때 포함되는 구성요소이다. 각 구성요소는 공용 동의어를 생성할 때 반드시 포함되어야 한다.

다음은 공용 동의어를 생성하는 예이다.

CREATE PUBLIC SYNONYM PUB_T1 FOR U1.EMP;

동의어는 객체 참조 방법의 편의성과 투명성(transparency)을 제공한다. 예를 들어 현재 사용자가 아닌 다른 사용자가 U1이 소유한 테이블 EMP를 접근하려고 하면 매번 U1.EMP라고 입력해야 한다. 하지만 공용 동의어를 정의하면 PUB_T1만 입력하면 된다.

예를 들어 다음의 두 SQL 문장은 같은 결과를 반환한다.

SELECT EMPNO, ENAME, ADDR FROM T1;

SELECT EMPNO, ENAME, ADDR FROM U1.EMP;

하나의 테이블을 액세스하는 애플리케이션 프로그램에서 다른 테이블을 액세스하는 동의어를 사용하는 경우 프로그램 내에서 액세스하는 테이블의 이름을 모두 변경하는 대신 정의된 동의어를 변경하는 것으로도 충분하다.

공용 동의어 제거

공용 동의어를 제거하려면 DROP PUBLIC SYNONYM 문을 사용해야 한다. 단, DROP PUBLIC SYNONYM 시스템 특권이 있어야 한다.

다음은 공용 동의어를 제거하는 예이다.

DROP PUBLIC SYNONYM PUB_T1;

Tibero에서는 동의어의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.

본 절에서는 트리거의 생성, 제거하는 방법을 설명한다.

트리거 생성

트리거는 다음 같은 경우에 따라 생성 요건이 다르다.

트리거를 생성하기 위해서는 CREATE TRIGGER 문을 사용해야 한다. 트리거는 삽입, 변경, 삭제 연산을 수행하기 직전이나 직후에 수행할 수 있다.

다음은 EMP 테이블에 새로운 로우를 삽입한 직후에 트리거를 수행하는 예이다.

CREATE TRIGGER TRG1
        AFTER INSERT ON EMP
        FOR EACH ROW
        WHEN (TRUE)
        BEGIN
                --- PSM BLOCK ---
        END; 

생성된 트리거는 트리거를 생성한 사용자의 권한을 가지고 동작한다.

트리거 제거

트리거를 제거하기 위해서는 DROP TRIGGER 문을 사용해야 한다.

다음은 트리거를 제거하는 예이다.

DROP TRIGGER TRG1; 

파티션을 생성하기 위해서는 CREATE TABLE 문을 사용할 때 파티션의 정보를 정의함으로써 파티션된 테이블을 만들 수 있다. 테이블을 만들 수 있는 권한만 있다면 특별한 권한은 필요하지 않다.

다음은 파티션으로 구성한 테이블을 생성하는 예이다.

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION PART3 VALUES LESS THAN (60, 70)
             ); 

테이블을 파티션으로 나누는 방법은 범위를 통해 가능하다. 위의 예에서는 각 파티션에 범위를 지정하여 해당 파티션에 데이터를 삽입한다. 이를 통해 데이터가 어느 파티션에 속해 있는지를 알 수 있다. 파티션은 최대 10,000개까지 생성할 수 있다.

파티션의 또 다른 장점은 관리의 편의성이다. 각 연도별로 파티션을 나눈 테이블이 있다고 했을 때 필요치 않은 10년 전의 정보를 저장하고 있는 해당 파티션을 제거함으로써 쉽게 삭제(DROP)할 수 있다. 또한 다음 해에 해당하는 파티션이 필요하다면 새로운 파티션을 추가(ADD)할 수 있다.

파티션의 제거와 추가는 ALTER TABLE 문에서 파티션과 관련된 옵션을 사용함으로써 가능하다.

ALTER TABLE PARTITIONED_TABLE1 ADD PARTITION PART3
        VALUES LESS THAN (70, 80);

ALTER TABLE PARTITIONED_TABLE1 DROP PARTITION PART1; 

파티션을 정의할 때 다음과 같은 주의 사항이 있다.

복합 파티션은 한 개의 키로 우선 파티션을 한 뒤 각 파티션을 같은 키 또는 다른 키로 다시 파티션을 하는 테이블 파티션의 한 방식이다.

아래 예는 sold_date 컬럼을 이용해 월별로 RANGE 파티셔닝을 우선 한 뒤 각 파티션들을 다시 product_id로 HASH 파티셔닝한 예이다.

CREATE TABLE years_sales
(
    product_id          NUMBER,
    product_name        VARCHAR(20),
    price               NUMBER,
    sold_date           DATE
)
PARTITION BY RANGE (sold_date)
  SUBPARTITION BY HASH (product_id)
  (
    PARTITION jan_sales VALUES LESS THAN
                         (TO_DATE('31-01-2006', 'DD-MM-YYYY')),
    PARTITION feb_sales VALUES LESS THAN
                         (TO_DATE('28-02-2006', 'DD-MM-YYYY')),
    PARTITION mar_sales VALUES LESS THAN
                         (TO_DATE('31-03-2006', 'DD-MM-YYYY')),
    PARTITION apr_sales VALUES LESS THAN
                         (TO_DATE('30-04-2006', 'DD-MM-YYYY')),
    PARTITION may_sales VALUES LESS THAN
                         (TO_DATE('31-05-2006', 'DD-MM-YYYY')),
    PARTITION jun_sales VALUES LESS THAN
                         (TO_DATE('30-06-2006', 'DD-MM-YYYY')),
    PARTITION jul_sales VALUES LESS THAN
                         (TO_DATE('31-07-2006', 'DD-MM-YYYY')),
    PARTITION aug_sales VALUES LESS THAN
                         (TO_DATE('31-08-2006', 'DD-MM-YYYY')),
    PARTITION sep_sales VALUES LESS THAN
                         (TO_DATE('30-09-2006', 'DD-MM-YYYY')),
    PARTITION oct_sales VALUES LESS THAN
                         (TO_DATE('31-10-2006', 'DD-MM-YYYY'))
    SUBPARTITIONS 2,
    PARTITION nov_sales VALUES LESS THAN
                         (TO_DATE('30-11-2006', 'DD-MM-YYYY'))
    SUBPARTITIONS 4,
    PARTITION dec_sales VALUES LESS THAN
                         (TO_DATE('31-12-2006', 'DD-MM-YYYY'))
    (SUBPARTITION dec_1, SUBPARTITION dec_2,
     SUBPARTITION dec_3, SUBPARTITION dec_4)
  );

두 개의 컬럼(위 예의 경우 sold_date와 product_id)에 대한 조건으로 빈번하게 조회가 일어나는 대용량 테이블에 대해 이런식으로 복합 파티셔닝을 하면 검색할 데이터의 양이 크게 줄기 때문에 성능상의 큰 이득을 볼 수 있다.

테이블뿐만 아니라 인덱스도 파티션을 지정할 수 있다. 인덱스 또한 파티션을 통해 데이터베이스 성능을 향상시킬 수 있다. 인덱스는 다음과 같이 두 가지 방법으로 파티션을 나눌 수 있다.

로컬 파티션

테이블이 파티션되었을 때 테이블 파티션에 들어가는 키로 파티션을 나누는 방법이다. 각 파티션에 아무런 정보를 입력하지 않고 단지 LOCAL이라고 선언하면 된다. 이름은 자동으로 생성되며 그 외 정보는 기본값으로 설정된다.

로컬 파티션으로 설정된 인덱스는 테이블의 한 파티션과 1:1로 대응된다. 로컬 파티션으로 설정된 인덱스의 한 파티션은 테이블의 한 파티션에 있는 로우만을 가리킨다.

다음은 로컬 파티션으로 인덱스를 생성하는 예이다.

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION DEF_PART VALUES LESS THAN (MAXVALUE, MAXVALUE)
             );

CREATE INDEX PARTITIONED_INDEX1 ON PARTITIONED_TABLE1 (C1) LOCAL
             (
                PARTITION IPART1 INITRANS 3,
                PARTITION IPART2 PCTFREE 10,
                PARTITION IPART3
             );

글로벌 파티션

테이블과는 무관하게 인덱스에 따로 파티션을 설정하는 방법이다. 테이블이 파티션으로 나뉘어져 있든 아니든 글로벌 파티션 인덱스를 만들 수 있다. 글로벌 파티션 인덱스의 한 파티션은 테이블의 어느 파티션에 있는 로우라도 가리킬 수 있다.

다음은 글로벌 파티션으로 인덱스를 생성하는 예이다.

CREATE TABLE PARTITIONED_TABLE1 (C1 NUMBER, C2 CLOB, C3 NUMBER)
             PARTITION BY RANGE (C1, C3)
             (
                PARTITION PART1 VALUES LESS THAN (30, 40),
                PARTITION PART2 VALUES LESS THAN (50, 60),
                PARTITION DEF_PART VALUES LESS THAN (MAXVALUE, MAXVALUE)
             );

CREATE INDEX PARTITIONED_INDEX1 ON PARTITIONED_TABLE1 (C3, C1)
             GLOBAL PARTITION BY RANGE (C3)
             (
                PARTITION IPART1 VALUES LESS THAN (20) INITRANS 3,
                PARTITION IPART2 VALUES LESS THAN (70) PCTFREE 10,
                PARTITION IPART3 VALUES LESS THAN (MAXVALUE)
             );

Tibero에서는 파티션된 스키마의 정보를 제공하기 위해 다음 표에 나열된 정적 뷰를 제공하고 있다. DBA나 일반 사용자 모두 사용할 수 있다.