[SQL] 오라클 데이터베이스 공부 기록(6)-인덱스,뷰,시퀀스

데이터 사전이란?

오라클 데이터 베이스 테이블은 사용자 테이블(user table), 데이터 사전(data dictionary)으로 나뉜다.

사용자 테이블은 여러 SQL문에서 활용한 EMP, DEPT, SALGRADE 테이블,

데이터 사전은 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블로

->데이터베이스가 생성되는 시점에 자동으로 만들어진다.

 

오라클 데이터베이스는 사용자가 데이터 사전 정보에 직접 접근하거나 작업하는 것을 허용x.

대신 데이터 사전 뷰(data dictionarty view)를 제공하여 SELECT문으로 정보 열람을 할 수 있게 해두었다.

데이터 사전 접두어

총 네 가지 있지만 두 가지가 제일 중요하다고 하셨다.

 

USER_XXXX : 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보

ALL_XXXX : 현재 데이터베이스에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를

받은 객체 -> 사용 가능한 모든 객체 정보.

인덱스

인덱스(index)는 오라클 데이터베이스에서 데이터 검색 성능 향상을 위해 테이블 열에 사용하는 객체를 뜻한다.

테이블에 보관된 특정 행 데이터의 주소->위치 정보를 책 페이지처럼 목록으로 만들어 놓은 것.

계정이 소유한 인덱스 정보 알아보기 / 인덱스 컬럼 정보 알아보기

인덱스 생성 & 삭제

-인덱스 생성

CREATE INDEX 인덱스 이름

ON 테이블 이름 (열 이름1 ASC or DESC,

열 이름2 ASC or DESC,

...);

 

-인덱스 삭제

DROP INDEX 인덱스 이름;

뷰란?

흔히 가상 테이블(virtual table)로 부르는 뷰(view)는 하나 이상의 테이블을 조회하는 SELECT문을

저장한 객체를 뜻한다.

-> 뷰를 SELECT문의 FROM절에 사용하면 특정 테이블을 조회하는 것과 같은 효과.

뷰의 사용 목적★★★

1.편리성 : SELECT문의 복잡도를 완화하기 위해

많은 분량의 SELECT문 여러 개의 결과 값을 조인하고 서브쿼리로 WHERE 조건식에도 사용한다면

전체 SELECT문은 훨씬 더 커질 수 있는데 이후 수정이 필요하거나 다른 개발자가 처음부터 코드를 파악해야

하는 경우 시간과 노력이 들 수 있다. 이럴 때 여러 SQL문에서 자주 활용하는 SELECT문을

뷰로 저장해 놓은 후 다른 SQL문에서 활용하면 복잡도를 완화할 수 있다.

 

2.보안성 : 테이블의 특정 열을 노출하고 싶지 않은 경우

EMP 테이블에는 급여(SAL)나 추가 수당(COMM)과 같이 아무에게나 노출하기 예민한 데이터가

존재한다. -> 테이블의 일부 데이터 또는 조인이나 여러 함수 등으로 가공을 거친

데이터만 SELECT하는 뷰 열람 권한을 제공하면 불필요한 데이터 노출을 막을 수 있다.

뷰 생성&삭제

-뷰 생성

CREATE [ON REPLACE ]VIEW 뷰 이름(열 이름1, 열 이름2...)

->같은 이름의 뷰가 이미 존재할 경우 현재 생성할 뷰로 대체하여 생성

(뷰는 수정 명령이 없다.)

AS (저장할 SELECT문)

 

-뷰 삭제

DROP VIEW 뷰 이름;

 

+VIEW에 DML(insert, update, delete)사용 가능?

->가능할 수도 있고 불가능할 수도 있다.

절대 view에 DML 사용하면 안 된다.

시퀀스

시퀀스(sequence)는 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체.

단순히 번호 생성을 위한 객체이지만 지속적이고 효율적인 번호 생성이 가능하므로

여러모로 자주 사용하는 객체이다.

시퀀스 생성

CREATE SEQUENCE 시퀀스 이름

[INCREMENT BY n] - 몇 씩 증가?

[START WITH n] - 시작값?

[MAXVALUE n, NOMAXVALUE] - 최대값

[MINVALUE n], NOMINVALUE - 최소값

[CYCLE , NOCYCLE] - MAX까지 출력됐을 때 다시 돌아갈 거냐? 멈출 거냐?

[CACHE, NOCACHE] - 번호표 기계 안에 종이를 몇 개 만들어 놓을 거냐?

시퀀스 사용★★★

(보통 실무에서 신입/주니어 개발자에게 시퀀스 생성 업무를 맡기는 경우는 흔하지 않다.

->시퀀스는 이미 있는 상태에서 그 시퀀스를 사용해서 번호를 뽑아내는 문법을 알고 있어야 한다.)

 

EX)

INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC)

VALUES (SEO_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');

 

생성된 시퀀스를 사용할 때는

[시퀀스 이름.CURRVAL], [시퀀스 이름.NEXTVAL]을 사용할 수 있다.

CURRVAL은 시퀀스에서 마지막으로 생성한 번호를 반환하며

NEXTVAL은 다음 번호를 생성한다.

->CURRVAL은 시퀀스 생성 후 바로 사용하면 번호가 만들어진 적이 없으므로 오류가 난다.

시퀀스 수정&삭제

ALTER 명령어로 시퀀스를 수정하고 DROP 명령어로 시퀀스를 삭제할 수 있는데

START WITH 값은 변경할 수 없다.

 

-시퀀스 수정

ALTER SEQUENCE 시퀀스 이름

[INCREMENT BY n]

[MAXVALUE n, NOMAXVALUE]

[MINVALUE n, NOMINVALUE]

[CYCLE , NOCYCLE]

[CACHE, NOCACHE]

 

-시퀀스 삭제

DROP SEQUENCE 시퀀스 이름;