SQL 데이터베이스 OBJECT 완벽 가이드: 정의부터 활용까지

안녕하세요!

이번 포스팅은 데이터베이스를 관리하고 운영하는 데 필수적인 요소인 SQL 데이터베이스 OBJECT에 대해 상세히 알아보겠습니다. 데이터베이스 관리자(DBA)부터 개발자까지 꼭 알아야 할 내용을 깊이 있게 다루었으니 끝까지 함께해 주세요.

#1. SQL OBJECT란?

SQL OBJECT(객체)는 데이터베이스 내에서 데이터를 저장, 관리, 조작하기 위한 논리적 구조입니다. 각 객체는 고유한 목적과 특성을 가지며, 데이터베이스 시스템의 효율적인 운영을 위해 다양한 종류의 객체가 존재합니다.

SQL OBJECT의 주요 특징:

고유한 이름을 가짐
특정 스키마에 속함
생성, 수정, 삭제가 가능
권한 관리의 기본 단위로 작용
데이터베이스 관리 시스템(DBMS)에 따라 지원하는 객체 유형이 다름

#2. 주요 SQL OBJECT 종류

SQL 데이터베이스에서 가장 일반적으로 사용되는 주요 객체 유형은 다음과 같습니다:

테이블(Table): 데이터를 행과 열 형태로 저장하는 기본 객체
뷰(View): 하나 이상의 테이블에서 파생된 가상 테이블
인덱스(Index): 데이터 검색 속도를 향상시키기 위한 구조
시퀀스(Sequence): 순차적인 숫자를 생성하는 객체
시노님(Synonym): 객체에 대한 대체 이름을 제공
저장 프로시저(Stored Procedure): 특정 기능을 수행하는 SQL 문의 집합
함수(Function): 값을 반환하는 서브프로그램
트리거(Trigger): 특정 이벤트 발생 시 자동으로 실행되는 코드
제약 조건(Constraint): 데이터 무결성을 유지하기 위한 규칙
패키지(Package): 관련 객체들을 그룹화한 모듈(주로 Oracle에서 사용)

이제 각 객체 유형에 대해 자세히 살펴보겠습니다.

#3. 테이블(Table)

테이블은 관계형 데이터베이스의 가장 기본적인 객체로, 데이터를 행(Row)과 열(Column) 형태로 저장합니다.

테이블의 주요 구성 요소:

열(Column): 특정 데이터 유형의 값을 저장
행(Row): 관련 데이터의 집합, 레코드라고도 함
기본 키(Primary Key): 각 행을 고유하게 식별하는 열 또는 열의 조합
외래 키(Foreign Key): 다른 테이블의 기본 키를 참조하는 열

테이블 생성 예제:

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    department_id NUMBER(4) REFERENCES departments(department_id)
);

테이블 종류:

영구 테이블(Permanent Table): 명시적으로 삭제할 때까지 존재
임시 테이블(Temporary Table): 세션 또는 트랜잭션 종료 시 자동으로 삭제
외부 테이블(External Table): 데이터베이스 외부의 데이터 파일을 참조
파티션 테이블(Partitioned Table): 데이터가 여러 세그먼트로 분할되는 테이블
물리화된 뷰(Materialized View): 쿼리 결과를 물리적으로 저장하는 테이블

#4. 뷰(View)

뷰는 하나 이상의 테이블에서 파생된 가상 테이블입니다. 데이터를 물리적으로 저장하지 않고, 기본 테이블의 데이터를 다양한 방식으로 표현합니다.

뷰의 주요 이점:

데이터 보안 강화: 민감한 정보를 숨기고 필요한 데이터만 제공
복잡한 쿼리 단순화: 자주 사용하는 복잡한 쿼리를 간단히 접근 가능
데이터 추상화: 사용자에게 논리적 데이터 구조만 제공
데이터 일관성 유지: 동일한 데이터에 대한 일관된 접근 방식 제공

뷰 생성 예제:

CREATE VIEW employee_details AS
SELECT
    e.employee_id,
    e.first_name || ' ' || e.last_name AS full_name,
    e.email,
    d.department_name,
    l.city,
    l.country_id
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN locations l ON d.location_id = l.location_id;

뷰 종류:

단순 뷰(Simple View): 단일 테이블에 기반한 뷰
복합 뷰(Complex View): 여러 테이블에 기반한 뷰
인라인 뷰(Inline View): 쿼리 내에서 FROM 절의 서브쿼리로 사용되는 임시 뷰
물리화된 뷰(Materialized View): 쿼리 결과를 물리적으로 저장하는 뷰

#5. 인덱스(Index)

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 데이터 구조입니다. 도서관의 색인과 유사하게, 특정 데이터를 빠르게 찾을 수 있도록 도와줍니다.

인덱스의 주요 이점:

검색 성능 향상: 특히 대용량 테이블에서 효과적
정렬 비용 감소: 인덱스가 이미 정렬되어 있어 ORDER BY 연산 비용 절감
고유성 보장: UNIQUE 인덱스를 통해 중복 데이터 방지

인덱스 생성 예제:

-- 단일 열 인덱스
CREATE INDEX idx_employees_last_name
ON employees(last_name);

-- 복합 인덱스
CREATE INDEX idx_employees_dept_job
ON employees(department_id, job_id);

-- 고유 인덱스
CREATE UNIQUE INDEX idx_employees_email
ON employees(email);

인덱스 종류:

B-트리 인덱스(B-tree Index): 가장 일반적인 인덱스 유형, 균형 트리 구조
비트맵 인덱스(Bitmap Index): 카디널리티가 낮은 열에 적합
함수 기반 인덱스(Function-Based Index): 열의 함수나 표현식에 기반한 인덱스
부분 인덱스(Partial Index): 테이블의 일부 행에만 적용되는 인덱스
텍스트 인덱스(Text/Full-Text Index): 텍스트 검색을 위한 특수 인덱스

#6. 시퀀스(Sequence)

시퀀스는 순차적인 숫자(일련번호)를 자동으로 생성하는 데이터베이스 객체입니다. 주로 기본 키 값을 생성하는 데 사용됩니다.

시퀀스의 주요 특징:

자동 증가: 설정된 증분값에 따라 자동 증가
순환 가능: 최댓값에 도달한 후 최솟값부터 다시 시작 가능
비순환 가능: 최댓값에 도달한 후 오류 발생
캐시 옵션: 성능 향상을 위해 메모리에 값을 미리 할당

시퀀스 생성 예제:

CREATE SEQUENCE employee_seq
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 9999999
    NOCYCLE
    CACHE 20;

시퀀스 사용 예제:

-- Oracle
INSERT INTO employees(employee_id, first_name, last_name)
VALUES(employee_seq.NEXTVAL, 'John', 'Doe');

-- 현재 시퀀스 값 확인
SELECT employee_seq.CURRVAL FROM dual;

-- PostgreSQL
INSERT INTO employees(employee_id, first_name, last_name)
VALUES(nextval('employee_seq'), 'John', 'Doe');

-- MySQL (AUTO_INCREMENT 사용)
INSERT INTO employees(first_name, last_name)
VALUES('John', 'Doe');

#7. 시노님(Synonym)

시노님은 데이터베이스 객체에 대한 대체 이름(별칭)을 제공하는 객체입니다. 긴 객체 이름을 단순화하거나 객체의 실제 이름과 위치를 숨기는 데 사용됩니다.

시노님의 주요 이점:

위치 투명성: 객체의 실제 위치나 소유자를 숨김
단순화: 복잡한 객체 이름을 간단하게 표현
객체 이동 용이성: 객체 위치 변경 시 시노님만 수정하면 됨

시노님 생성 예제:

-- 비공개 시노님
CREATE SYNONYM emp FOR employees;

-- 공개 시노님
CREATE PUBLIC SYNONYM departments FOR hr.departments;

시노님 종류:

비공개 시노님(Private Synonym): 특정 스키마 내에서만 접근 가능
공개 시노님(Public Synonym): 모든 사용자가 접근 가능

#8. 저장 프로시저(Stored Procedure)

저장 프로시저는 이름이 부여된 PL/SQL 코드 블록으로, 특정 작업을 수행하는 SQL 문의 집합입니다. 한 번 컴파일되어 데이터베이스에 저장되며, 필요할 때마다 호출할 수 있습니다.

저장 프로시저의 주요 이점:

코드 재사용: 자주 사용하는 로직을 재사용
성능 향상: 미리 컴파일되어 실행 계획이 저장됨
네트워크 트래픽 감소: 여러 SQL문을 단일 호출로 실행
보안 강화: 직접 테이블 접근 없이 프로시저를 통한 접근만 허용 가능

저장 프로시저 생성 예제:

CREATE OR REPLACE PROCEDURE update_employee_salary(
    p_employee_id IN NUMBER,
    p_percentage IN NUMBER
)
AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percentage/100)
    WHERE employee_id = p_employee_id;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END update_employee_salary;

저장 프로시저 실행 예제:

-- Oracle
EXEC update_employee_salary(100, 10);

-- SQL Server
EXEC update_employee_salary @p_employee_id = 100, @p_percentage = 10;

#9. 함수(Function)

함수는 특정 연산을 수행하고 단일 값을 반환하는 데이터베이스 객체입니다. 저장 프로시저와 유사하지만, 반드시 하나의 값을 반환한다는 점이 다릅니다.

함수의 주요 특징:

단일 값 반환: 항상 하나의 값을 반환
SQL 문 내에서 사용 가능: SELECT, WHERE, ORDER BY 등 내에서 직접 사용 가능
읽기 전용 작업: 일반적으로 데이터 수정 없이 계산만 수행

함수 생성 예제:

CREATE OR REPLACE FUNCTION calculate_annual_salary(
    p_salary IN NUMBER,
    p_commission_pct IN NUMBER
) RETURN NUMBER
IS
    v_annual_salary NUMBER;
BEGIN
    v_annual_salary := p_salary * 12 + (p_salary * NVL(p_commission_pct, 0) * 12);
    RETURN v_annual_salary;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END calculate_annual_salary;

함수 사용 예제:

-- 쿼리 내에서 함수 사용
SELECT
    employee_id,
    first_name,
    last_name,
    calculate_annual_salary(salary, commission_pct) AS annual_salary
FROM
    employees;

#10. 트리거(Trigger)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE 등)가 발생할 때 자동으로 실행되는 PL/SQL 코드 블록입니다. 데이터 무결성을 유지하거나 관련 작업을 자동화하는 데 유용합니다.

트리거의 주요 유형:

시점에 따른 분류: BEFORE 트리거, AFTER 트리거
이벤트에 따른 분류: INSERT, UPDATE, DELETE 트리거
수준에 따른 분류: 행 레벨 트리거, 문장 레벨 트리거

트리거 생성 예제:

CREATE OR REPLACE TRIGGER audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO employee_audit(employee_id, action, change_date)
        VALUES(:NEW.employee_id, 'INSERT', SYSDATE);
    ELSIF UPDATING THEN
        INSERT INTO employee_audit(employee_id, action, change_date)
        VALUES(:OLD.employee_id, 'UPDATE', SYSDATE);
    ELSIF DELETING THEN
        INSERT INTO employee_audit(employee_id, action, change_date)
        VALUES(:OLD.employee_id, 'DELETE', SYSDATE);
    END IF;
END;

트리거 사용 시 주의사항:

무거운 로직은 트리거에 넣지 않는 것이 좋음
트리거 내에서 트랜잭션 제어문(COMMIT, ROLLBACK) 사용 불가
상호 참조 트리거(상호 실행 트리거)는 무한 루프를 발생시킬 수 있음

#11. 제약 조건(Constraint)

제약 조건은 테이블의 데이터에 대한 규칙을 정의하여 데이터 무결성을 유지하는 객체입니다. 데이터가 항상 비즈니스 규칙에 맞게 유지되도록 보장합니다.

주요 제약 조건 유형:

기본 키(PRIMARY KEY): 행을 고유하게 식별하는 열 또는 열의 조합
외래 키(FOREIGN KEY): 다른 테이블의 기본 키를 참조하는 열
고유 키(UNIQUE): 열 또는 열의 조합에 중복 값이 없음을 보장
NOT NULL: 열에 NULL 값이 허용되지 않음
CHECK: 열 값이 특정 조건을 만족하는지 확인

제약 조건 생성 예제:

-- 테이블 생성 시 제약 조건 정의
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30) NOT NULL UNIQUE,
    manager_id NUMBER(6) REFERENCES employees(employee_id),
    location_id NUMBER(4),
    budget NUMBER(10,2) CHECK (budget > 0)
);

-- 기존 테이블에 제약 조건 추가
ALTER TABLE employees
ADD CONSTRAINT emp_salary_min
CHECK (salary >= 1000);

-- 제약 조건 비활성화
ALTER TABLE employees
DISABLE CONSTRAINT emp_salary_min;

-- 제약 조건 활성화
ALTER TABLE employees
ENABLE CONSTRAINT emp_salary_min;

#12. DBMS별 특수 OBJECT

각 데이터베이스 관리 시스템(DBMS)에는 고유한 특수 객체가 있습니다. 여기서는 주요 DBMS별 특수 객체를 살펴보겠습니다.

Oracle 특수 객체:

패키지(Package): 관련 프로시저와 함수를 그룹화
CREATE OR REPLACE PACKAGE emp_mgmt AS PROCEDURE hire_employee(p_name VARCHAR2, p_job VARCHAR2); FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER; END emp_mgmt; /
타입(Type): 사용자 정의 데이터 타입
CREATE OR REPLACE TYPE address_type AS OBJECT ( street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip VARCHAR2(10) ); /
디렉토리(Directory): 외부 파일에 접근하기 위한 객체
CREATE DIRECTORY data_dir AS '/oracle/data';

SQL Server 특수 객체:

사용자 정의 데이터 타입(User-Defined Data Type):
CREATE TYPE PhoneNumber FROM VARCHAR(20) NOT NULL;
확장 저장 프로시저(Extended Stored Procedure):
EXEC sp_addextendedproc 'xp_custom_proc', 'custom_dll.dll';
어셈블리(Assembly): .NET 코드 통합
CREATE ASSEMBLY MyAssembly FROM 'C:\MyCode.dll';

PostgreSQL 특수 객체:

도메인(Domain): 제약 조건이 있는 사용자 정의 타입
CREATE DOMAIN us_postal_code AS TEXT CHECK(VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$');
외부 래퍼(Foreign Data Wrapper): 외부 데이터 소스 접근
CREATE FOREIGN DATA WRAPPER postgres_fdw HANDLER postgres_fdw_handler;

#13. SQL OBJECT 관리 모범 사례

효율적인 데이터베이스 객체 관리를 위한 모범 사례입니다:

명명 규칙:

일관된 명명 규칙 사용
객체 유형을 접두사로 표시 (예: tbl_employees, vw_employee_details)
의미 있고 설명적인 이름 사용
예약어 사용 피하기

성능 관리:

필요한 인덱스만 생성하고 불필요한 인덱스 제거
대용량 객체는 파티셔닝 고려
주기적으로 통계 갱신
복잡한 뷰의 성능 영향 고려

보안 관리:

최소 권한 원칙 적용
민감한 데이터에 대한 열 수준 암호화 고려
권한 관리를 위해 역할(Role) 활용
정기적인 보안 감사 수행

버전 관리:

객체 스크립트를 버전 관리 시스템에 저장
변경 이력 문서화
중요한 변경 전 백업 수행
테스트 환경에서 먼저 변경사항 검증

#14. 자주 묻는 질문

Q: 테이블과 뷰의 주요 차이점은 무엇인가요?

A: 테이블은 실제 데이터를 물리적으로 저장하는 객체인 반면, 뷰는 저장된 쿼리로 실제 데이터를 저장하지 않고 필요할 때 데이터를 조회합니다. 뷰는 데이터 보안, 복잡한 쿼리 단순화, 데이터 추상화 등의 이점을 제공합니다.

Q: 인덱스를 너무 많이 생성하면 어떤 문제가 발생할 수 있나요?

A: 과도한 인덱스는 다음과 같은 문제를 일으킬 수 있습니다:

INSERT, UPDATE, DELETE 작업의 성능 저하
추가적인 디스크 공간 소비
옵티마이저의 실행 계획 복잡성 증가
데이터베이스 유지 관리 부담 증가

Q: 트리거와 저장 프로시저의 차이점은 무엇인가요?

A: 트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되지만, 저장 프로시저는 명시적인 호출이 있을 때만 실행됩니다. 트리거는 주로 데이터 무결성 유지나 감사 로깅에 사용되며, 저장 프로시저는 더 복잡한 비즈니스 로직을 처리하는 데 적합합니다.

Q: 물리화된 뷰(Materialized View)와 일반 뷰의 차이점은 무엇인가요?

A: 일반 뷰는 쿼리 결과를 물리적으로 저장하지 않고 필요할 때마다 쿼리를 실행하지만, 물리화된 뷰는 쿼리 결과를 물리적으로 저장하여 성능을 향상시킵니다. 물리화된 뷰는 주기적으로 갱신(리프레시)해야 하며, 복잡한 조인이나 집계 연산이 포함된 쿼리에 적합합니다.

결론

이 글에서는 SQL 데이터베이스 OBJECT의 정의와 다양한 종류에 대해 자세히 살펴보았습니다. 테이블, 뷰, 인덱스부터 저장 프로시저, 트리거까지 각 객체의 특성과 활용 방법을 이해하는 것은 효율적인 데이터베이스 설계와 관리의 기본입니다.

각 객체 유형은 고유한 목적과 이점을 가지며, 이들을 적절히 조합하여 사용하면 성능, 보안, 유지보수성이 뛰어난 데이터베이스 시스템을 구축할 수 있습니다. 데이터베이스 설계와 개발에서 이러한 SQL OBJECT들을 효과적으로 활용하시기 바랍니다!

긴 글 읽어주셔서 감사합니다.

끝.

참고 자료

Oracle 공식 문서: https://docs.oracle.com/en/database/
Microsoft SQL Server 공식 문서: https://docs.microsoft.com/en-us/sql/
PostgreSQL 공식 문서: https://www.postgresql.org/docs/

저작자표시 비영리 변경금지

'■Development■ > 《Database》' 카테고리의 다른 글

[DB] Oracle에서 Procedure 작성 규칙 (0)	2025.03.06
[DB] START WITH CONNECT BY PRIOR 사용법 (0)	2024.07.03
[DB] ORA-06512 에러 원인과 해결 방법 (0)	2024.07.01
[DB] ORA-01013 에러 원인과 해결 방법 (0)	2024.07.01
[DB] 데이터 모델링 완벽 가이드 (0)	2022.09.04

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

[DB] SQL에서 OBJECT의 정의와 종류