스키마 객체

데이터베이스는 여러 객체로 구성됩니다. 각 객체는 '데이터베이스 > 사용자 > 스키마 > 스키마 객체'의 순 으로 포함 관계를 갖고 하나의 데이터베이스는 여러 사용자가 공유하고 있습니다. 또한 사용자 중에는 데이터베이스를 관리하기 위해 특별한 권한을 부여 받고 있는 DBA가 있습니다.

스키마는 사용자가 소유한 객체의 모임(Collection)입니다. Tibero에서는 한 사용자가 하나의 스키마만을 정의할 수 있고, 스키마의 이름은 항상 사용자의 이름과 동일합니다. 이러한 스키마에 포함된 객체를 스키마 객체라 합니다. SQL 표준에서 정의한 스키마 객체 외에, 데이터베이스 시스템에 따라 추가적인 스키마 객체 를 제공하고 있습니다.

Tibero에서 제공하는 스키마 객체

• 테이블(Table)

• 인덱스(Index)

• 뷰(View)

• 시퀀스(Sequence)

• 동의어(Synonym)

테이블

테이블은 관계형 데이터베이스의 기본 저장 단위입니다. 다른 모든 스키마 객체는 테이블을 중심으로 정의됩니다. 테이블은 2차원 행렬(Matrix)의 형태를 갖습니다. 테이블은 하나 이상의 컬럼으로 구성되며 각 컬럼은 고유의 데이터 타입을 갖습니다. 하나의 테이블은 0개 이상의 로우를 포함힙니다. 각 로우는 각 컬럼에 해당하는 값을 갖습니다.

테이블을 생성하고 변경, 제거하기 위한 SQL 문장은 각각 CREATE TABLE, ALTER TABLE, DROP TABLE입니다. 이러한 문장은 DDL에 포함되며, 자세한 설명은 “데이터 정의어”를 참고합니다.

아래는 회사 직원의 정보를 저장하고 있는 EMP 테이블의 예시입니다.

EMP 테이블

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
      7566 JONES      MANAGER         7839 1981/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
      7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 1987/04/19       3000                    20
      7839 KING       PRESIDENT            1981/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
      7900 JAMES      CLERK           7698 1981/12/03        950                    30
      7902 FORD       ANALYST         7566 1981/12/03       3000                    20
      7934 MILLER     CLERK           7782 1982/01/23       1300                    10

위의 테이블은 'EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO'의 8개의 컬럼으로 구성되며, 14개의 로우를 포함하고 있습니다. 테이블을 구성하는 컬럼 정보는 거의 변경되지 않으나, 테이블에 포함된 로우의 개수는 수시로 변경될 수 있습니다.

컬럼의 데이터 타입 중에는 문자형과 같이 최대 길이를 미리 정해야 하는 것도 있고, NUMBER 타입과 같이 정밀도와 스케일을 정해야 하는 것도 있습니다. 일부 컬럼에 대해서는 기본값을 선언할 수도 있습니다.

데이터 타입에 대한 상세한 내용은 “데이터 타입”에서 확인할 수 있습니다.

테이블 전체 또는 일부 컬럼에 무결성 제약조건(Integrity Constraints)을 선언할 수 있습니다. 무결성 제약조건은 테이블에 어떠한 로우가 삽입되든 항상 만족되어야 합니다. 예를 들어 테이블 EMP의 한 컬럼 SAL 에 대하여 다음과 같은 조건을 선언할 수 있습니다.

SAL >= 0

직원의 연봉이 0보다 작을 수 없으므로, 이러한 조건을 사용해 잘못된 데이터의 입력을 미리 막을 수 있습니다.

무결성 제약조건을 표현하기 위한 조건식(Condition Expression)은 “조건식”에서 설명합니다.

하나의 테이블에 무결성 제약조건을 선언할 수도 있고, 두 개의 테이블에 참조 무결성(Referential Integrity) 제약조건을 선언할 수도 있습니다.

아래는 회사의 부서 정보를 저장하는 DEPT 테이블의 예시입니다.

DEPT 테이블

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

회사의 모든 직원이 하나의 특정 부서에 반드시 소속되어야 합니다면, EMP 테이블 예시에서 테이블 EMP의 컬럼DEPTNO의 값은 반드시 위 예시에서의 DEPT 테이블의 DEPT의 컬럼 DEPTNO에 존재하는 값이어야 합니다.

무결성 제약조건에 대한 자세한 내용은 "Tibero 관리자 안내서"를 참고합니다.

인덱스 (Index)

인덱스(Index)는 테이블과 별도의 저장공간을 이용하여 그 테이블의 특정 컬럼을 빠르게 검색 할 수 있게 하는 데이터 구조입니다. 테이블의 소유자는 어떤 컬럼에 대해 하나 이상의 인덱스를 생성할 수 있습니다.

인덱스를 생성하고 변경, 제거하기 위한 SQL 문장은 각각 CREATE INDEX, ALTER INDEX, DROP INDEX입니다. 이러한 문장은 DDL에 포함되며, 자세한 설명은 "데이터 정의어”를 참고합니다.

아래는 인덱스 (Index)에 대한 설명입니다.

자동 인덱싱

• Tibero에서는 모든 테이블의 기본 키(Primary Key) 컬럼에 대해 자동으로 인덱스를 생성합니다.

• 기본 키 컬럼이란 테이블 내의 특정 로우를 유일하게 식별할 수 있는 값을 갖는 컬럼을 의미합니다.

• 하나의 테이블 내에서 어떤 로우도 다른 로우와 동일한 기본 키 컬럼 값을 가질 수 없습니다. 위의 EMP 테이블과 DEPT 테이블 예시에서는 각각 컬럼 EMPNO와 DEPTNO가 기본 키 컬럼이 될 수 있습니다.

컬럼의 중복 허용

• 인덱스는 컬럼 값의 중복 유무에 관계 없이 생성할 수 있습니다.

• 예를 들어 테이블 DEPT의 컬럼 DEPTNO 와 같이 중복이 없는 컬럼이나 테이블 EMP의 컬럼 DEPTNO와 같이 중복이 있는 컬럼에 대해서도 인덱스를 생성할 수 있습니다.

복수 컬럼 허용

• 인덱스는 하나의 컬럼뿐만 아니라 둘 이상의 컬럼 값을 하나로 접합하여 생성할 수도 있습니다.

• 예를 들어 테이블 EMP의 컬럼 ENAME과 ADDR 값을 합쳐서 인덱스를 만들 수 있습니다. 이러한 인덱스는 둘 이상의 컬럼이 동시에 검색 대상이 될 확률이 높은 경우에 유용합니다.

인덱스의 적용

• 사용자가 인덱스를 생성하더라도 바로 사용되지는 않습니다.

• 인덱스가 생성되면 SQL 질의를 수행할 때, 질의 최적화기가 인덱스를 사용할 때와 사용하지 않을 때의 실행 효율성을 비교하고, 더 효율적인 방향 으로 실제 질의를 실행하므로 질의대상 테이블에 대해서 생성된 인덱스를 이용할 것인지, 어떤 컬럼에 대해서 생성된 인덱스를 이용할 것인지는 데이터베이스 시스템에 의하여 자동적으로 결정됩니다.

인덱스의 관리

• 인덱스의 관리도 데이터베이스 시스템에서 자동적으로 이뤄집니다.

• 인덱스가 생성된 후에 테이블에 하나의 로우가 삽입되거나 갱신, 삭제되면 그 테이블에 대하여 생성된 모든 인덱스에서 그 로우에 대한 삽입, 갱신, 삭제가 이뤄집니다.

인덱스의 제거

• 인덱스가 더 이상 필요하지 않으면 언제라도 그 인덱스를 제거할 수 있습니다.

• 인덱스를 너무 많이 생성해 놓으면 테이블에 대한 갱신이 이루어질 때마다 인덱스에도 함께 반영해 주어야 하므로 성능 저하의 원인이 될 수 있으므로, 불필요한 인덱스는 제거하는 것이 바람직합니다.

뷰 (View)

뷰는 SQL 문장에 이름을 붙인 것으로, 빈번히 수행되는 질의의 결과를 테이블 형태로 이용할 수 있도록 정의한 것입니다. 뷰는 테이블 또는 다른 뷰를 이용하여 정의할 수 있으며, SQL 문장 내에서 일반 테이블과 동일하게 이용할 수 있습니다. 뷰가 정의된 테이블을 기반 테이블(base table)이라 합니다.

뷰를 정의하는 이유는, 하나의 테이블을 여러 사용자가 함께 접근할 수 있지만 사용자에 따라 테이블 내용의 일부를 숨김으로써 정보 보안을 유지하고자 하는 것입니다.

뷰를 생성하고 제거하기 위한 SQL 문장은 CREATE VIEW, DROP VIEW 입니다. 이러한 문장은 DDL에 포함되며, 자세한 설명은 “데이터 정의어”를 참고합니다.

아래는 뷰에 대한 설명입니다.

뷰 병합

• 데이터베이스 시스템 내에서 뷰는 질의 문장을 문자열 형태로 관리합니다.

• 만약 뷰를 포함한 SQL 문장이 입력되면, 데이터베이스 시스템은 그 문장을 뷰를 포함하지 않는 기반 테이블에 대한 SQL 문장으로 변환합니다. 이러한 과정을 뷰 병합이라고 합니다.

뷰 활용 및 권한

• 뷰가 이용되는 경우는 위에서와 같이 빈번한 질의를 단순화하고자 하는 경우 이외에, 뷰를 정의한 기반 테이블의 일부만을 드러내고자 하는 경우에도 사용됩니다.

• 예를 들어 테이블 EMP의 내용 중에서 컬럼 SAL의 내용을 관리직이 아닌 일반 직원에게 드러내고 싶지 않은 경우에, 일반 직원이 테이블 EMP 를 직접 접근하지 못하고 뷰를 통해서만 접근하도록 할 수 있습니다.

• 이러한 경우 스키마 객체를 접근할 수 있는 권한(Authority)과 연관됩니다.

• 즉, 뷰를 정의한 사용자가 일 반 직원에게 그 뷰를 접근할 권한만을 부여하고 뷰가 정의된 기반 테이블 EMP를 접근할 권한을 부여하지 않으면, 일반 직원에게는 테이블 EMP 내의 데이터 중에서 그 뷰에 의해 볼 수 있는 일부만을 드러냅니다.

시퀀스 (Database Sequence)

데이터베이스 시퀀스(Database Sequence)는 유일한 연속적인 값을 생성해 낼 수 있는 스키마 객체입니다. 이 값은 보통 기본 키 또는 유일 키에 값을 채워 넣을 때 사용되며 항상 시퀀스의 이름에는 의사 컬럼을 붙여서 사용합니다.

SQL 문에서는 아래와 같은 의사 컬럼을 통해 데이터베이스 시퀀스(Database Sequence)의 값을 읽어들입니다.

컬럼	설명
CURRVAL	현재 세션에서 마지막으로 조회한 NEXTVAL 값을 반환
NEXTVAL	시퀀스의 현재 값을 증가시키고 증가된 그 값을 반환

의사 컬럼과 함께 시퀀스(Database Sequence)를 사용하는 예시

seq1.currval 
seq2.nextval

위치에 따른 시퀀스 의사 컬럼의 사용 여부

사용 여부	위치
사용 가능	SELECT 리스트 부질의 또는 뷰의 SELECT 리스트에서는 사용 불가능 INSERT 문의 부질의의 SELECT 리스트 INSERT 문의 VALUES 절 UPDATE 문의 SET 절
사용 불가능	SELECT, DELETE, UPDATE 문의 부질의의 내부 뷰의 내부 DISTINCT가 있는 SELECT 문 GROUP BY 절이나 ORDER BY 절이 있는 SELECT 문 집합 연산자(UNION, INTERSECT, MINUS)로 다른 SELECT 문과 연결된 SELECT 문 SELECT 문의 WHERE 절 CREATE TABLE 또는 ALTER TABLE을 실행할 때 컬럼의 DEFAULT 값 CHECK 제약 조건

데이터베이스시퀀스(Database Sequence)를 생성할 때 초기 값과 증감치가 결정됩니다.

NEXTVAL 의사 컬럼을 통해 최초로 시퀀스에 접근하면, 시퀀스는 초기값을 반환합니다. 이후 NEXTVAL을 사용할 때마다 시퀀스의 값은 증감치 만큼 증가하고, 새로 증가된 값을 반환합니다. CURRVAL 의사 컬럼은 항상 시퀀스의 현재 값을 반환하며, 이것은 가장 마지 막에 사용된 NEXTVAL이 반환한 값과 같습니다.

CURRVAL 의사 컬럼을 사용하기 전에 적어도 한 번 이상은 시퀀스에 NEXTVAL을 사용해서 초기화를 시켜야 합니다. SQL 문장에 사용된 NEXTVAL 의사 컬럼은 SQL 문장이 처리하는 행의 단위별로 시퀀스의 값 을 증가시킵니다.

데이터베이스시퀀스(Database Sequence) 값을 증가시키는 행

• 최상위 레벨의 SELECT 문이 출력하는 행

• INSERT ... SELECT 문에서 SELECT 문이 선택하는 행

• CREATE TABLE ... AS SELECT 문에서 SELECT 문이 선택하는 행

• UPDATE 문이 갱신하는 각각의 행

• VALUES 절을 포함한 INSERT 문이 삽입하는 행

한 행에 두 번 이상의 NEXTVAL이 나왔을 경우 시퀀스 값은 처음 한 번만 증가되며, 증가된 그 값이 다음 위치에 동일하게 사용됩니다. NEXTVAL과 CURRVAL이 한 행에 동시에 나왔을 경우 시퀀스 값은 역시 한 번만 증가되며, NEXTVAL에 사용된 값이 CURRVAL에 동일하게 사용됩니다.

동의어 (Synonym)

동의어(Synonym)는 특정 스키마 객체에 정의하는 일종의 별칭(Alias) 입니다. 대개 긴 이름을 갖는 스키마 객체에 대하여 짧은 이름의 동의어를 정의하거나, 특별한 용도의 스키마 객체에 대하여 동의어를 정의합니다.

아래는 동의어(Synonym)에 대한 설명입니다.

동의어(Synonym)와 뷰(View)의 차이점

동의어는 긴 이름 대신 사용합니다는 점에서 뷰와 같습니다. 그러나 뷰는 하나의 완전한 SQL 문장에 대한 것이고, 동의어는 하나의 스키마 객체에 한정된다는 차이점이 있습니다. 또한 동의어는 뷰와 다르게 접근 권한이 별도로 설정되지 않습니다. 만약 특정 스키마 객체에 대한 접근 권한이 있습니다면 그에 대한 동의어에 대해서도 접근 권한을 갖게 됩니다.

동의어의 사용 설정

동의어는 기본적으로 동의어를 정의한 사용자만이 사용할 수 있으나, 다른 모든 사용자도 함께 사용하도록 공용으로 정의할 수도 있습니다. 이렇게 모든 사용자가 사용할 수 있는 동의어를 공유 동의어라고 합니다. Tibero에서는 DBA와 일반 사용자가 데이터 사전(Data Dictionary)의 정보를 쉽게 접근할 수 있도록, 여러가지 시스템 뷰를 정의하고 각각에 대한 동의어를 정의하고 있습니다.

동의어를 생성하고 삭제하기 위한 SQL 문장은 CREATE SYNONYM과 DROP SYNONYM 입니다.

동의어를 변경하기 위한 별도의 SQL 문장은 없습니다. 이러한 문장은 DDL에 포함되며, 자세한 설명은 “데이터 정의어”를 참고합니다.

스키마 객체의 이름

스키마 객체에 따라 사용자가 각각의 부분 또는 전체에 이름을 부여할 수 있거나 꼭 부여해야만 하는 경우가 있습니다. 테이블, 테이블의 컬럼, 인덱스, 무결성 제약조건, 테이블 파티션, 테이블 서브 파티션, 인덱스 파 티션, 인덱스 서브 파티션, 패키지와 패키지 내의 함수와 프러시저 등이 이에 해당합니다.

SQL 문장에서 스키마 객체의 이름을 명시할 때는 '따옴표 있는 식별자' 또는 '따옴표 없는 식별자'를 사용합니다.

• 따옴표 있는 식별자는 큰따옴표(" ")로 시작해서 큰따옴표로 종결됩니다.

• 따옴표 없는 식별자는 큰따옴표로 여닫지 않습니다.

스키마 객체에 이름을 부여할 때는 위 두개 중 어떤 것을 사용해도 무방합니다.

따옴표 없는 식별자는 대소 문자를 구별하지 않으며, 모두 대문자로 간주되어 처리되며, 따옴표 있는 식별자는 대소문자를 구분합니다.

예를 들어 아래의 경우는 모두 서로 다른 식별자입니다.

department 
"department" 
"Department"

아래의 경우는 모두 서로 깉은 식별자입니다.

department 
DEPARTMENT 
"DEPARTMENT"

식별자를 기술할 때 지켜야 할 규칙은 아래와 같습니다.

• 길이는 30byte를 넘지 않습니다.

• 예약어는 따옴표 없는 식별자가 될 수 없습니다. 따옴표를 적용하여 예약어를 식별자로 만들 수 있으나, 권장하지는 않습니다.

예약어는 “Appendix A. 예약어”를 참고합니다.

• 따옴표 없는 식별자는 알파벳, 한글, 숫자, 언더바( _ ), $, #만 사용할 수 있습니다. 다만, 숫자, '$', '#'는 첫 글 자로 올 수 없습니다.

• 따옴표 있는 식별자는 공백을 포함한 어떤 문자라도 쓸 수 있습니다. 다만 큰따옴표(" ")는 사용할 수 없습니다.

• 하나의 네임스페이스 안에서 서로 다른 두 객체가 동일한 이름을 가질 수 없습니다.

구분	네임스페이스	종류
스키마 객체	하나의 네임스페이스	테이블, 뷰, 실체화 뷰, 시퀀스, 동의어, 패키지, 패키지에 포함되지 않은 함수 및 프러시저
스키마 객체	독립적인 네임스페이스	인덱스, 트리거, 대용량 객체형 데이터 타입 (서로 다른 스키마의 네임스페이스는 공유되지 않으므로, 서로 다른 스키마에 있는 두 개의 테이블은 같은 이름을 가질 수 있음)
일반 객체	독립적인 네임스페이스	사용자 역할, 공유 동의어, 테이블 스페이

• 하나의 테이블 내의 서로 다른 컬럼은 동일한 이름을 가질 수 없습니다.

• 서로 다른 테이블에 속해 있는 컬럼은 동일한 이름을 가질 수 있습니다.

• 한 패키지 내의 서로 다른 프러시저나 함수는 인자의 개수나 데이터 타입이 다른 경우에 한해서 동일한 이름을 가질 수 있습니다.

스키마 객체 관련 문법

SQL 문장에서 스키마 객체와 객체의 구성 요소를 명시하는 방법을 설명합니다.

• 문법

• 구성 요소

구성 요소	설명
schema	객체를 포함하고 있는 스키마의 이름 schema 부분을 명시해 사용자가 소유한 스키마가 아닌 다른 사용자의 스키마에 있는 객체 지정 가능 (다른 사용자가 소유한 스키마 객체에 접근하기 위해서는 권한부여받기필요) schema를 생략하면 자기 자신의 스키마에 있는 객체를 명시하는 것으로 인식, 스키마 객체에만 schema라는 한정어 사용 가능 공유 동의어에 대해서는 "PUBLIC"이라는 한정어를 사용 PUBLIC을 사용할 때는 큰따옴표(" ") 반드시 사용 필요
object	객체의 이름
part	객체의 구성 요소를 명시할 때 사용 (예: 테이블의 구성 요소는 컬럼, 파티션 등 존재)
dblink	분산 데이터베이스 기능을 사용 시 필요 object 부분에 명시한 객체를 포함하고 있는 데이터베이스의 이름을 명시 dblink를 사용해 자신의 데이터베이스가 아닌 원격 데이터베이스에 있는 객체를 명시 가능 (이 한정어 생략 시, 사용자의 데이터베이스에 있는 객체를 명시하는 것으로 인식)

스키마 객체와 스키마 객체가 아닌 일반 객체의 종류 및 네임스페이스에 대해서는 “스키마 객체의 이름” 에 설명되어 있습니다.

스키마 객체를 찾는 과정

SQL 문장 내에 명시된 스키마 객체를 찾는 과정은 아래와 같습니다.

1. SQL 문장의 문맥에 따라 네임스페이스가 결정됩니다.

2. 결정된 네임스페이스 내에서 객체를 찾습니다.

3. 객체의 타입이 SQL 문장 내의 객체가 명시된 위치에서 사용될 수 있는지 확인합니다.

4. 사용될 수 없는 타입이라면 에러를 반환합니다.

위의 스키마 객체를 찾는 과정을 설명하는 예시는 아래와 같습니다.

SELECT * FROM employees;

위의 SQL 문장을 입력했다고 가정하고, 위의 문장을 예로 스키마 객체를 찾는 과정 입니다. 각각의 숫자는 스키마 객체를 찾는 과정에서 각 단계를 설명할 때 사용된 숫자와 동일한 단계입니다.

1. 위의 SQL 문장은 스키마를 명시하지 않았으므로 사용자가 소유한 스키마를 네임스페이스로 결정합니다.

2. 결정된 네임스페이스에서 즉, 사용자가 소유한 스키마 내에서 employees라는 객체가 존재하는지 확인합니다. 만일 확인 못했다면 employees라는 공유 동의어가 존재하는지 확인합니다. 공유 동의어에도 employees 라는 객체가 없습니다면 오류를 반환합니다. 만약 찾은 객체가 동의어라면 동의어의 정의를 따라가서 동의어가 정의하고 있는 스키마 객체가 동의어인지 아닌지 확인합니다. 동의어라면 또 그 동의어의 정의를 따라갑니다. 동의어가 아닌 스키마 객체가 나올 때까지 이 과정을 반복합니다.

3. 해당 객체를 찾았으면 그 객체가 테이블인지 아니면 뷰인지 확인합니다. FROM 절에는 테이블 또는 뷰만 위치할 수 있습니다.

4. 만약 테이블 또는 뷰가 아닌 다른 객체라면 오류를 반환합니다.

원격 데이터베이스의 객체를 찾는 과정

원격 데이터베이스에 있는 객체를 명시하기 위해서는 객체 이름 다음에 "@" 표시와 함께 데이터베이스 링크(Database link) 이름을 명시합니다.

데이터베이스 링크는 원격 데이터베이스와의 연결을 담당하는 스키마 객체입니다.

데이터베이스 링크는 “CREATE DATABASE LINK”를 사용하여 생성합니다.

SQL 문장에서 데이터베이스 링크를 사용했을 경우 해당 객체를 찾는 과정은 아래과 같습니다.

1. 사용자의 스키마에서 해당 이름을 갖는 데이터베이스 링크가 존재하는지 확인합니다.

2. 존재하지 않으면 공유 데이터베이스 링크 중에서 해당 이름의 존재를 확인합니다.

3. 해당 데이터베이스 링크 존재를 확인했다면, 그 데이터베이스 링크를 이용해 원격 데이터베이스의 객체에 접근을 시도합니다.

4. 해당 원격 데이터베이스에서 스키마 객체를 찾는 과정은 "스키마 객체를 찾는 과정"과 동일합니다.