[Oracle/기본]13. 다른 데이터베이스 객체

● 데이터베이스 객체

 

- Table : 행과 열로 구성된 기본적인 저장매체의 단위

- View : 하나 이상의 테이블로부터 데이터의 부분집합을 논리적으로 표현

- Sequence : 기본 키 값을 발생

- Index : 어떤 질의의 성능을 향상

- Synonym : 객체에 대체 이름을 부여

 

● 시퀀스

 

- 자동적으로 유일번호를 생성한다.

- 공유가능한 객체(다중 테이블에 대해 사용될 수 있다.)

- 주로 기본 키 값을 생성하기 위해 사용된다.

 

CREATE SEQUENCE sequence

             [INCREMENT BY n]

             [START WITH n]

             [{MAXVALUE n / NONMAXVALUE}]

             [{MINVALUE n / NONMINVALUE}]

             [{CYCLE / NONCYCLE}]

             [{CACHE n / NOCACHE}];

 

sequence 시퀀스의 이름이다.

INCREMENT BY         n 정수 값인 n으로 시퀀스 번호 사이의 간격을 지정한다.

START WITH n          생성하기 위해 첫번째 시퀀스를 지정한다. 이 절이 생략되면, 시퀀스는 1로 시작된다.

MAXVALUE n            시퀀스를 생성할 수 있는 최대값을 지정한다.

NONMAXVALUE        오름차순용 10^27 최대값과 내림차순용 -1의 최소값을 지정한다.

MINVALUE n             최소 시퀀스 값을 지정한다.

NONMINVALUE         오름차순용 1과 내림차순용 -(10^26)의 최소값을 지정한다.

CYCLE / NOCYCLE      최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를 지정한다. NOCYCLE이 디폴트 옵션이다.

CACHE n / NOCACHE 얼마나 많은 값을 메모리에 오라클 서버가 미리 할당하고 유지하는가를 지정한다. 디폴트로 오라클 서버는

                              20을 캐쉬한다.

 

Ex. CREATE SEQUENCE 시퀀스명

          INCREMENT BY 1

             START WITH 91

               MAXVALUE 100

                NOCACHE

                NOCYCLE;

시퀀스 사이클보다 더 빨리 오래된 행을 지우는 신뢰할만한 메커니즘을 가지고 있지 않은 경우에, 기본 키 값을 생성하기 위해 시퀀스가

사용된다면 CYCLE 옵션을 사용해서는 안된다.

 

● NEXTVAL과 CURRVAL

 

- NEXTVAL : 다음 사용 가능한  시퀀스 값을 반환

- CURRVAL : 현재 시퀀스 값을 얻는다.

 

INSERT INTO dept(deptno, dname, loc)

       VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO');

 

SELECT dept_deptno.CURRVAL

 FROM dual;

 

● 시퀀스 사용

 

- 시퀀스 값에서 간격(gap)은 아래 상황에서 발생할 수 있다.

☞ rollback 발생

☞ system실패(crash)

☞ 시퀀스가 다른 테이블에서 사용될 때

 

- USER_SEQUENCES 테이블을 질의하여 NOCACHE로 시퀀스가 생성된 경우에만 증가 없이 다음 사용 가능 값을 볼 수 있다

 

● 시퀀스 수정

 

시퀀스에 대해 MAXVALUE한계에 도달한다면, 더이상 시퀀스 값을 할당 받을 수 없다. 시퀀스를 계속 사용하기 위해, ALTER SEQUENCE

문장을 사용하여 수정할 수 있다.

 

ALTER SEQUENCE sequence

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n / NONMAXVALUE}]

[{MINVALUE n / NONMINVALUE}]

[{CYCLE / NOCYCLE}]

[{CACHE n / NOCACHE}];

 

- START WITH옵션은 ALTER SEQUENCE를 사용하여 변경될 수 없다. 다른 번호에서 시퀀스를 다시 시작하기 위해서는 제거하고 다시

  생성되어야 한다.

- 유효성 검사를 수행한다. 예를 들면, 새 MAXVALUE는 현재 시퀀스 번호보다 작게 될 수 없다.

 

● 시퀀스 제거

 

DROP SEQUENCE dept_deptno;

 

● 인덱스

 

- 포인터를 사용하여 행의 검색을 촉진할 수 있는 스키마 객체이다.

- 열에서 인덱스를 가지고 있지 않다면, 이 때는 전체 테이블 스캔이 발생한다.

 

● 인덱스를 생성하는 방법

1. 자동으로

 

   유일 인덱스는 테이블 정의의 PRIMARY KEY 또는 UNIQUE KEY 제약조건을 정의할 때 자동으로 생성된다.

 

2. 수동으로

 

   사용자는 행에 대한액세스 시간을 향상 시키기 위해 열에서 유일하지 않은 인덱스를 생성할 수 있다.

 

- 인덱스 생성

 

CREATE INDEX index

ON table (col1, col2, ...);

 

Ex. CREATE INDEX emp_ename_idx

     ON               emp(ename);

 

● 언제 인덱스를 생성하는가?

 

1. 열은 WHERE절 또는 조인 조건에서 자주 사용된다.

2. 열은 광범위한 값을 포함한다.

3. 열은 많은수의 NULL값을 포함한다.

4. 둘 또는 이상의 열은 WHERE절 또는 조인 조건에서 자주 함께 사용된다.

5. 테이블은 대형이고 대부분의 질의들은 행의 2 ~ 4% 보다 적게 읽어들일 것으로 예상된다.

 

● 언제 인덱스를 생성해서는 안되는가?

 

1. 테이블이 작다.

2. 열이 질의의 조건으로 자주 사용되지 않는다.

3. 대부분의 질의들은 행의 2 ~ 4%이상을 읽어들일 것으로 예상된다.

4. 테이블은 자주 갱신된다. 테이블에 하나 이상의 인덱스를 가지고 있다면 테이블을 액세스하는 DML 문장은 인덱스의 유지

   때문에 상대적으로 더 많은 시간이 걸리게 된다.

 

● 인덱스 제거

 

DROP INDEX index;

 

인덱스는 수정할 수 없다.

인덱스를 변경하기 위해서는, 그것을 제거하고 다시 작성해야한다.

 

● 동의어

 

동의어(객체의 다른이름)를 생성하여 객체에대한 액세스를 단순화한다.

- 다른 사용자가 소유한 테이블을 참조한다.

- 개체이름의 길이를 단축한다.

 

CREATE [PUBLIC] SYNONYM synonym

FOR object;

 

PUBLIC   모든 사용자에 대해 액세스 가능한 동의어를 생성한다.

synonym 생성되어야 할 동의어의 이름이다.

object    생성된 동의어에 대한 객체를 식별한다.

 

Ex. DEPT_SUM_VU 뷰에 대해 단축명을 생성한다.

 

    CREATE SYNONYM dm_sum

    FOR                     alice.dept_sum_vu;

 

    위와같이 시노님을 작성했을 경우, 아래의 결과는 같다.

    SELECT * FROM dm_sum;                   <-동의어 사용

    SELECT * FROM alice.dept_sum_vu;

 

    동의어를 제거한다.

 

    DROP SYNONYM d_sum;

 

   

    [PUBLIC] 이 들어간 공용동의어의 경우 DBA만 제거할 수 있다.