[SQL] OBJECT

Object의 종류 및 기능

관계형 데이터베이스에서 특정한 기능을 가진 Object에는 다음과 같은 것들이 있다.

오브젝트	설명
테이블(table)	행과 열의 조합으로 구성된 기본 저장 단위
시퀀스(sequence)	자동으로 고유한 숫자값을 생성해주며 주로 기본키 값을 생성하기 위해 사용
인덱스(index)	쿼리(query) 속도를 향상시키기 위해 사용
뷰(view)	하나 또는 그 이상의 테이블로부터 논리적으로 데이터를 추출한 부분집합으로 논리적이고 가상적인 테이블
시노님(synonym)	OBJECT에 대한 또다른 이름으로 ALIAS 역할
프로그램 유닛(program unit)	SQL, PL/SQL문으로 작성한 Procedure, Function, Trigger, Package

 

1. SEQUENCE

• 자동으로 Unique number를 생성 (중간에 GAP 발생 가능)
• 공유 가능한 Object
• 일반적으로 PK값 생성을 위해서 사용

 

1-1 SEQUENCE 생성하기

CREATE SEQUENCE 이름
    [INCREMENT BY n]            
    [START WITH n]             
    [{MAXVALUE n | NOMAXVALUE}] 
    [{MINVALUE n | NOMINVALUE}]            
    [{CYCLE | NOCYCLE}]
    [{CACHE | NOCACHE}];

• INCREMENT BY n : SEQUENCE 번호 간의 간격 (생략시 1씩 증가)
• START WITH n : 생성되는 첫번째 SEQUENCE 번호 (생략시 1부터 시작)
• MAXVALUE n : 생성 가능한 SEQUENCE 최대값
• NOMAXVALUE : 기본 옵션으로 최대값은 10²⁷
• MINVALUE n : 생성 가능한 SEQUENCE의 최소값
• NOMINVALUE : 최소값을 1로 지정
• CYCLE | NOCYCLE : 최대값이나 최소값까지 값이 생성된 경우에도 값을 생성시키는 것(기본은 NOCYCLE)
• CACHE n | NOCACHE : 메모리에 유지할 값의 수로, 기본값은 20. NOCACHE일때만, 테이블에서 조회 가능

1-2 SEQUENCE 사용법

의사 컬럼(Pseudo columns) NEXTVAL, CURRVAL의 사용

• NEXTVAL은 사용 가능한 다음 SEQUENCE 값을 반환한다.
• NEXTVAL은 다른 사용자에 의해 참조되더라도 매번 고유한 값을 반환한다.
• CURRVAL은 현재 SEQUENCE 값을 포함한다.
• CURRVAL은 반드시 NEXTVAL 사용 후에 참조되어야 한다.

1-3 SEQUENCE의 변경과 삭제

ALTER SEQUENCE 시퀀스이름
    [ INCREMENT BY n ]
    [ { MAXVALUE n | NOMAXVALUE } ]
    [ { MINVALUE n | NOMINVALUE } ]
    [ { CYCLE | NOCYCLE } ]
    [ { CACHE n | NOCACHE } ]

DROP SEQUENCE 시퀀스이름;

• 자신의 SEQUENCE이거나 그 SEQUENCE에 대한 ALTER 권한을 가져야 SEQUENCE를 수정할 수 있다.
• 변경 후의 SEQUENCE값만이 ALTER SEQUENCE 명령에 영향을 받는다.
• 유효성 검사를 하게된다.
• START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없다.
  다른 번호에서 시작하려면 이전 SEQUENCE를 삭제하고 다시 생성해야 한다.
• SEQUENCE를 제거할 DROP ANY SEQUENCE 권한을 가지고 있거나 그 SEQUENCE의 소유자이어야 한다.

 

2. INDEX

 

2-1 인덱스(Index)의 개념

• 테이블의 데이터를 좀 더 빠르게 검색하기 위해 사용하는 데이터베이스 Object이다.
• Oracle Server가 최적화 방법에 따라 어떤 Index를 사용할 것인지, 혹은 사용하지 않을 것인지 결정한다.
• B*Tree의 검색 방법으로 디스크 입출력(I/O) 횟수를 줄인다.
• Oracle Server가 Index를 자동적으로 사용하고 유지보수한다.
• Index는 논리적, 물리적으로 테이블과는 독립적이다.
• Index는 자동으로 생성되기도 하고, 사용자가 필요에 의해 만들기도 한다.

2-2 인덱스의 구조

• 인덱스는 테이블의 각 행에 대응하는 주소(ROWID)와 인덱스 컬럼값으로 구성된다.
• Oracle Server는 인덱스에 대해 B*Tree 구조를 이용한다.
• Oracle Server는 해당 ROWID를 갖는 컬럼값이 있는 노드에 이를 때까지 트리를 탐색한다.

2-3 인덱스의 생성 및 삭제

CREATE INDEX 인덱스명
ON 테이블명(컬럼);

DROP INDEX 인덱스명;

2-4 인덱스를 만드는 때

• 조건절(WHERE)이나 조인(Join) 조건에서 컬럼을 자주 이용할 때
• 컬럼이 넓은 범위의 값을 가질 때
• 많은 NULL 값을 갖는 컬럼일 때
• 테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15% 정도일 때

2-5 인덱스를 만들지 않아야 할 때

• 테이블이 작을 때
• 컬럼이 조회의 조건으로 사용되는 경우가 별로 없을 때
• 대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때
• 테이블이 자주 변경될 때

인덱스가 검색속도를 증가시키지만 항상 빠른 것은 아니다.
인덱스를 많이 만든다고 해서 항상 좋은 것도 아니다.
인덱스가 유지되기 위해서는 그만큼 저장공간이 필요하고, 테이블의 수정시에 인덱스도 변경되어야 하므로 서버의 성능을 저하시킬 수 있다.

인덱스에 대해 자세한 내용은 SQL 튜닝에서 다루도록 하겠다.

 

 

3. VIEW

3-1 View란?

• View란 테이블이나 다른 View를 기초로 한 가상의 테이블
• View는 자체 데이터는 없지만 테이블의 데이터를 보고나 변경할 수 있는 창과 같다.
• View는 실제적으로는 SQL문으로 저장된다.
• View의 장점
  • 뷰는 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다. (보안상의 이유)
  • 복잡한 SQL문을 통해 얻을 수 있는 결과를 간단한 SQL문을 써서 구할 수 있게 한다.
  • 한 개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있다. (Join)

3-2 View의 생성

CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW 이름 [ (alias) ]
AS subQuery
[ WITH CHECK OPTION [ CONSTRAINT 제약조건 ] ]
[ WITH READ ONLY ];

-- FORCE : 기본 테이블 유무에 관계없이 뷰를 만듦
-- Alias : SubQuery로 선택된 값에 대한 컬럼명
-- WITH CHECK OPTION : 뷰에 의해 액세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지적
-- WITH READ ONLY : 뷰에 DML이 수행될 수 없음

• 뷰를 정의하는 SQL문은 조인, 그룹, SubQuery를 포함하는 복잡한 SELECT 문장으로 구성될 수 있다.
• 뷰를 정의하는 SQL문에는 ORDER BY 절을 쓸 수 없다.
• 제약조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.
• 뷰를 삭제하거나 재생성하지 않고 뷰의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있다.

3-3 View의 확인 및 삭제

SELECT view_name, text
FROM user_views;

DROP VIEW 뷰이름;

 

 

4. SYNONYM

• Synonym이란 특정 Object에 부여하는 또 다른 이름이다.
• Synonym은 사용자의 편의나 참조를 빠르게 하기 위해서 사용한다.

CREATE [ PUBLIC ] SYNONYM 시노님명
FOR 오브젝트명;

-- PUBLIC : 모든 사용자가 접근 가능한 동의어 생성 (DBA만 PUBLIC SYNONYM 생성 가능)