[SQL 요약 정리] 1-13. OBJECT

13. OBJECT

1) SEQUENCE란?

- 공유 가능한 Object(일반적으로 Primary Key값 생성을 위해 사용)

- 자동으로 Unique number 생성(GAP 발생 가능)

- 예시

CREATE SEQUENCE c_emp_id

INCREMENT BY 1

START WITH 26

MAXVALUE 99999999

NOCACHE

NOCYCLE;

2) SEQUENCE 생성

- 문형

CREATE SEQUENCE 이름

[ INCREMENT BY n ]  // SEQUENCE 번호 간격(기본값 1)

[ START WITH n ] // 생성되는 첫 번째 SEQUENCE 번호(기본값 1)

[ { MAXVALUE n | NOMAXVALUE } ]  // SEQUENCE 최대값(기본값 10^27)

[ { CACHE n | NOCACHE } ] // 메모리에 유지할 값의 수(기본값 20)

*NO CACHE 일때만 테이블에서 조회 가능

[ { CYCLE | NOCYCLE } ] // 최소값 OR 최대값이 생성된 경우에도 값을 생성

- SEQUENCE 생성 확인 예시

SELECT sequence_name, min_value, max_value, increment_by, last_number

FROM user_sequences;

3) SEQUENCE 사용법

- 의사 칼럼(Pseudo columns) NEXTVAL, CURRVAL의 사용

- NEXTVAL: 사용 가능한 다음 SEQUENCE값 반환, 타 사용자 참조에 상관없이 고유값 반환

- CURRVAL: 현재 SEQUENCE 값 포함, 반드시 NEXTVAL 사용 후 참조되어야 함

4) SEQUENCE 사용 규칙

- NEXTVAL과 CURRVAL 사용 가능

Subquery가 아닌 SELECT문

INSERT문의 SELECT절, VALUE절

UPDATE문의 SET절

- NEXTVAL과 CURRVAL 사용 불가

VIEW의 SELECT절

DISTINCT 키워드가 있는 SELECT문

GROUP BY, HAVING, ORDER BY절이 있는 SELECT문

SELECT, DELETE, UPDATE문의 Subquery

CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

5) SEQUENCE의 변경과 삭제

- 해당 SEQUENCE 혹은 권한을 가진 SEQUENCE만 변경, 삭제 가능

- 조건에 따른 유효성 검사 진행

- START WITH 옵션은 변경 불가

- SEQUENCE 변경 문형

ALTER SEQUENCE 시퀀스명

[ INCREMENT BY n ]

[ { MAXVALUE n | NOMAXVALUE } ]

[ { MINVALUE n | NOMINVALUE } ]

[ { CYLCE | NOCYLCE } ]

[ { CACHE n | NOCACHE } ]

- SEQUENCE 삭제 문형

DROP SEQUENCE 시퀀스명

6) 인덱스(INDEX)

- 테이블의 데이터 검색을 돕기위한 DB Object

- 테이블의 각 행에 대응하는 주소(ROWID)와 인덱스 컬럼값으로 구성

- 인덱스는 논리적, 물리적으로 테이블에 독립적

- B*Tree 검색방법으로 디스크 입출력 횟수 감소

- 종류

Unique Index: PK, Unique 제약조건에 의해 자동 생성, 각 인덱스값이 유일함 보장

Non-Unique Index: 사용자에 의해 생성, 각 인덱스값이 유일하지 않음

*이외에 단일 인덱스, 결합 인덱스로 구분하기도 함

7) Oracle에서 제공하는 최적화 방법

- ORACLE Server가 최적화 방법에 따라 인덱스 사용여부 결정 및 유지보수

- 규칙 기준(Rule-based) 최적화: SQL 문장 실행 시 내부 규칙에 근거하여 선정

- 비용 기준(Cost-based) 최적화: SQL 문장 실행 시 통계정보 분석으로 최저비용 경로 선정

8) B*트리의 특징

- 어떤 행에대해서 액세스 횟수가 동일한 이진 균형 탐색 구조

- ORACLE Server가 만드는 인덱스는 트리에 정렬된 여러개의 저장장소 페이지로 구성

- 각 페이지는 키값이 데이터의 위치를 가리킬 때까지 구조 아래를 향하는 포인터와 키값 보유

9) 인덱스(INDEX)의 생성 및 삭제

- 생성 문형

CREATE INDEX 인덱스명

ON 테이블명(컬럼 [, 컬럼] ...);

- 삭제 문형

DROP INDEX 인덱스명;

10) 인덱스 생성 지침

- 인덱스 필요

조건절(WHERE) 혹은 조인(Join) 조건에서 칼럼을 자주 사용할 때

칼럼이 넓은 범위 값을 가지거나 많은 NULL값을 가질 때

테이블의 데이터가 많고 조회되는 행의 수가 전체의 10-15%정도 일 때

- 인덱스 불필요

테이블이 작거나 칼럼이 조회 조건으로 사용되는 경우가 적을 때

대부분의 조회가 행의 10-15% 이상을 검색한다고 예상 될 때

테이블이 자주 변경될 때

11) INDEX의 효율적 사용

- INDEX가 존재하지만 사용되지 않는 경우

INDEXED 칼럼 비교 전에 변형되거나 NULL로 비교, 부정 조건 기술, Optimizer의 취사선택

- 예시

index미사용: SUBSTR(title, -2, -2) = '부장' / salary*12 = 24000 / title <> '사원'

index사용: title like '%부장' / salary = 24000/12 / NOT EXISTS (SELECT 'X' FROM s_emp WHERE e.title = '사원'

12) View

- 테이블의 다른 뷰를 기초로 한 가상 테이블

- 자체 데이터는 없지만 데이터를 보거나 변경할 수 있는 창과 같다

- SQL문으로 저장됨

- 장점

DB에 대한 액세스 제한

한 개의 뷰로 여러 테이블의 데이터 검색 가능

복잡한 SQL문을 통해 얻을 수 있는 결과를 간단하게 얻을 수 있음

13) View의 생성

- 뷰를 정의하는 SQL문은 조인, 그룹, SubQuery를 포함하는 복잡한 SELECT문장으로 구성

- 뷰를 정의하는 SQL문에는 ORDER BY절 사용 불가

- 이름 미지정시 시스템이 SYS_Cn 형태로 이름 지정

- OR REPLACE 옵션을 통해 뷰 재정의 가능

- View 생성 문형

CREATE [ OR REAPLACE] [ FORCE | NOFORCE ] 뷰이름 [ (alias[, alias] ...) ]

AS subQuery

[ WITH CHECK OPTION [ CONSTRAINT 제약조건 ] ] [ WITH READ ONLY ] ;

FORCE: 기본 테이블 유무에 관계없이 뷰 생성

Alias: SubQuery로 선택된 값에 대한 칼럼명

WITH CHECK OPTION: 뷰에 의해 액세스 될 수 있는 행만 입력 및 변경 가능 지정

WITH READ ONLY: 뷰에 DML 수행 불가

14) View에 대한 DML문 사용 규칙

- 간단한 뷰(테이블 1개)에서는 DML연산 수행 가능

- 추가, 변경, 삭제 불가: 뷰에 조인 조건, 그룹 함수, Group by절, distinct명령이 포함된 경우

- 변경 불가: 뷰에 식으로 정의된 칼럼이 포함된 경우

- 추가 불가: 뷰에 선택되지 않은 NOT NULL 칼럼이 포함된 경우

15) View의 확인 및 삭제

- 확인 예시

DESC user_views;

SELECT view_name, text

FROM user_views;

- 삭제 문형

DROP VIEW 뷰이름:

16) SYNONYM

- 특정 Object에 부여하는 또 다른 이름(사용자의 편의를 위해 사용)

- 문형

CREATE [ PUBLIC ] SYNONYM synonym명

FOR object명;

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