[DATABASE] Oracle SQL 개념 정리 - 프로시저, 뷰, 트리거 ,인덱스, 시퀀스, 클러스터

1. 프로시저 

프로시저의 개요

- 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어

- 호출을 통해 실행되어 미리 저장해놓은 SQL 작업 수행

- 여러 프로그램에서 호출하여 사용 가능

- 데이터베이스에 저장되어 수행되기 때문에 스토어드 프로시저라고도 함

- 시스템의 일일 마감 작업, 일괄 작업 등에 사용

 

프로시저의 구성

- DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부

- BEGIN / END : 프로시저의 시작과 종료를 의미

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML, DCL이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCPETION : BEGIN ~ END 안의 구문 실행 시 예외 처리

- TRANSACTION : 수행된 데이터 작업들을 DB에 저장할지 취소할지 결정

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역번수 선언]

BEGIN

프로시저 BODY;

END;

- OR REPLACE : 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저를 대체

- 파라미터 

    -> IN : 호출 프로그램이 프로시저에게 값을 전달할 때 사용

    -> OUT : 프로시저가 호출 프로그램에게 값을 전달할 때 사용

    -> INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에게 값을 반환할 때 지정

    -> 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

    -> 자료형 : 변수의 자료형을 지정

- 프로시저 BODY

    -> 프로시저의 코드를 기록

    -> BEGIN과 END 사이에 적어도 하나의 SQL 문이 존재해야 함

- 사원번호를 입력받아 해당 사원의 지급방식을 S로 변경하는 프로시저

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)

IS

BEGIN

UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;

EXCEPTION

WHEN PROGRAM_ERROR THEN

ROLLBACK;

COMMIT;

END;

 

프로시저 실행

EXECUTE 프로시저명;

EXEC 프로시저명;

CALL 프로시저명;

- 위 3가지 명령어 중 하나를 사용

- 위에 정의한 emp_change_s 프로시저를 사원번호 32를 인수로 하여 실행

EXECUTE emp_change_s(32);

EXEC emp_change_s(32);

CALL emp_change_s(32);

 

프로시저 제거

DROP PROCEDURE 프로시저명;

- 위에 정의한 emp_change_s 프로시저를 제거

DROP PROCEDURE emp_change_s;

 

 

2.뷰

뷰의 개요

- 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블

- 물리적으로 존재하지는 않지만 사용자에게는 있는 것처럼 간주됨

- 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용

- 조인문의 최소화로 사용자 편의성을 최대화함

 

뷰의 특징

- 기본 테이블과 같은 형태의 구조를 사용하고 조작도 기본 테이블과 거의 같음

- 가상테이블이기 때문에 물리적으로 구현되어 있지 않음

- 데이터의 논리적 독립성을 제공할 수 있음

- 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해짐

- 뷰를 통해서만 데이터에 접근하게 되면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있음

- 뷰가 정의된 기본 테이블이나 뷰를 삭제 시 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동으로 삭제

 

뷰의 장단점

- 장점

    -> 논리적 데이터 독립성 제공

    -> 동일 데이터에 대해 동시에 여러 사용자의 상이한 요구를 지원

    -> 사용자의 데이터 관리가 용이

    -> 접근 제어를 통한 자동 보안 제공

- 단점

    -> 독립적인 인덱스를 가질 수 없음

    -> 뷰의 정의 변경 불가

    -> 뷰로 구성된 내용에 대해 INSERT, DELETE, UPDATE 연산에 제약이 따름

뷰는 변경이 안되기 때문에 삭제후 drop->create f로 다시 재 생성 필요

 

 

뷰 설계 순서

- 대상 테이블 선정 → 대상 칼럼 선정 → 정의서 작성

 

뷰 설계 시 고려사항

- 테이블 구조가 단순화될 수 있도록 반복적으로 조인을 설정하여 사용하거나 동일한 조건절을 사용하는 테이블을 뷰로 생성

- 동일한 테이블이라도 업무에 따라 테이블을 이용하는 부분이 달라질 수 있으므로 사용할 데이터를 다양한 관점에서 제시

- 데이터의 보안을 유지하며 설계

 

 

 

 

3. 트리거 

트리거

트리거의 개요

- 데이터베이스 시스템에서 데이터의 삽입 갱신 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 데이터베이스에 저장

- 데이터 변경 및 무결성 유지 로그 메시지 출력 등의 목적으로 사용

- DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미침

 

트리거의 구성

 

- DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의

- EVENT : 트리거가 실행되는 조건

- BEGIN / END : 트리거의 시작과 끝

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML문이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCEPTION : BEGIN ~ END 안의 구문에서 예외가 발생 시 처리

 

트리거의 생성

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명

REFERENCING [NEW | OLD] AS 테이블명

FOR EACH ROW

[WHEN 조건식]

BEGIN

트리거 BODY;

END;

- 동작 시기 옵션 : 트리거가 실행될 때를 지정

    -> AFTER : 테이블이 변경된 후

    -> BEFORE : 테이블이 변경되기 전

- 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정

    -> INSERT : 새로운 튜플을 삽입할 때

    -> DELETE : 튜플을 삭제할 때

    -> UPDATE : 튜플을 수정할 때

- NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정

    -> NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

    -> OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

- FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

- WHEN 조건식 : 트리거를 적용할 튜플의 조건을 지정

- 학생 테이블에 새로운 튜플이 삽입될 때 튜플에 학년 정보가 누락되었으면 학년 필드에 신입생을 치환하는 트리거를 학년 정보_tri라는 이름으로 정의

CREATE TRIGGER 학생정보_tri BEFORE INSERT ON 학생

REFERENCING NEW AS new_table

FOR EACH ROW

WHEN (new_table.학년 IS NULL)

BEGIN

:new_table.학년 := '신입생';

END;

 

트리거의 제거

DROP TRIGGER 트리거명;

- 위에 정의한 학생정보_tri를 제거

DROP TRIGGER 학생정보_tri;

 

 

4. 인덱스

인덱스의 개념

- 데이터 레코드를 빠르게 접근하기 위해 키값, 포인터 쌍으로 구성되는 데이터 구조

- 책의 목차와 유사

- 데이터가 저장된 물리적 구조와 밀접한 관계가 있음

- 파일의 레코드에 대한 액세스를 빠르게 수행할 수 있음

- 인덱스가 없으면 특정한 값을 찾기 위해 모든 데이터 페이지를 확인하는 TABLE SCAN이 발생

    -> TABLE SCAN : 데이터가 나올 때까지 모든 레코드를 순차적으로 읽는 것

- 레코드의 삽입과 삭제가 수시로 일어나는 경우에는 인덱스의 개수를 최소로 하는 것이 효율적

- 클러스터드 인덱스 : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식

- 넌클러스터드 인덱스 : 인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식

 

트리 기반 인덱스

- 인덱스를 저장하는 블록들이 트리 구조를 이루고 있는 것으로 상용 DBMS에서는 트리 구조 기반의 B+ 트리 인덱스를 주로 활용

- B 트리 인덱스

    -> 일반적으로 사용하는 인덱스 방식

    -> 루트 노드에서 하위 노드로 키값의 크기를 비교하면서 데이터를 검색

    -> 모든 리프 노드의 레벨은 같음

- B+ 트리 인덱스

    -> 단말 노드가 아닌 노드로 구성된 인덱스 세트와 단말 노드로만 구성된 순차 세트로 구분

    -> 인덱스 세트에 있는 노드들은 단말 노드에 있는 키 값을 찾아갈 수 있는 경로로만 제공

    -> 순차 세트에 있는 단말 노드가 해당 데이터 레코드의 주소를 가리킴

    -> 인덱스 세트에 있는 모든 키 값이 단말 노드에 다시 나타나므로 단말 노드 만을 이용한 순차 처리 가능

 

비트맵 인덱스

- 인덱스 칼럼의 데이터를 Bit 값인 0 또는 1로 변환하여 인덱스 키로 사용하는 방법

- 키 값을 포함하는 로우(Row)의 주소를 제공

- 데이터가 Bit로 구성되어 있어 효율적인 논리 연산이 가능하고 저장공간이 작음

 

함수 기반 인덱스

- 칼럼의 값 대신 칼럼의 특정 함수나 수식을 적용하여 산출된 값을 사용

- B+ 트리 인덱스 또는 비트맵 인덱스를 생성하여 사용

- 데이터를 입력하거나 수정할 때 함수를 적용하기 때문에 부하가 발생할 수 있음

- 사용자 정의 함수를 사용했을 경우 시스템 함수보다 부하가 더 크다

- 대소문자, 띄어쓰기 등에 상관없이 조회할 때 유용하게 사용

 

비트맵 조인 인덱스

- 다수의 조인된 객체로 구성된 인덱스

 

도메인 인덱스

- 개발자가 필요한 인덱스를 직접 만들어 사용하는 것으로 확장형 인덱스라고도 함

 

인덱스 설계 순서

- 인덱스의 대상 테이블이나 칼럼 등을 선정 → 인덱스의 효율성을 검토하여 인덱스 최적화 수행 →  인덱스 정의서 작성

 

인덱스 테이블 선정 기준

- MULTI BLOCK READ 수에 따라 판단

    -> MULTI BLOCK READ : 테이블 액세스 시 메모리에 한 번에 읽어 들일 수 있는 블록의 수

- 랜덤 액세스가 빈번한 테이블

- 특정 범위나 특정 순서로 데이터 조회가 필요한 테이블

- 다른 테이블과 순차적 조인이 발생되는 테이블

 

인덱스 서계 시 고려사항

- 새로 추가되는 인덱스는 기존 액세스 경로에 영향을 미칠 수 있음

- 인덱스를 지나치게 만들면 오버헤드 발생

- 넓은 범위를 인덱스로 처리하면 많은 오버헤드 발생

- 인덱스를 만들면 추가적인 저장공간 필요

- 인덱스와 테이블 데이터의 저장 공간이 분리되도록 설계

 

5. 시퀀스

- 순차적으로 정수 값을 자동으로 생성해주는 객체

 

 

 

6. 클러스터 

클러스터의 개요

- 데이터 저장 시 데이터 액세스 효율을 향상시키기 위해 동일한 성격의 데이터를 데이터 블록에 저장하는 물리적 저장 방법

- 클러스터링키로 지정된 칼럼 값의 순서대로 저장되고 여러 개의 테이블이 하나의 클러스터에 저장

 

클러스터의 특징

- 데이터 조회 속도는 향상시키지만 데이터 입력 수정 삭제에 대한 성능은 저하시킴

- 데이터의 분포도가 넓을수록 유리

- 대용량을 처리하는 트랜잭션은 전체 테이블을 스캔하는 일이 자주 발생하므로 클러스터링을 지양

- 파티셔닝 된 테이블에는 적용할 수 없음