제약 조건이 있는 인덱스 생성

개요

Oracle 데이터베이스에서 제약 조건(Constraints)은 데이터 무결성을 유지하는 데 중요한 역할을 합니다. 이러한 제약 조건을 활용하여 인덱스를 생성하면 성능 향상과 데이터 관리 효율성을 높일 수 있습니다. 본 가이드에서는 다양한 제약 조건과 함께 인덱스를 생성하는 방법과 실제 예시를 통해 실무에 적용 가능한 지침을 제공합니다.

제약 조건이 있는 인덱스 생성의 필요성

제약 조건은 테이블의 데이터에 대한 규칙을 정의합니다. 일반적인 제약 조건 유형은 다음과 같습니다.

  • PRIMARY KEY: 테이블에서 각 행을 고유하게 식별하는 데 사용되는 열 또는 열 집합입니다.
  • UNIQUE: 테이블에서 열 또는 열 집합의 모든 값이 고유해야 함을 지정합니다.
  • NOT NULL: 열에 Null 값이 포함될 수 없음을 지정합니다.
  • FOREIGN KEY: 한 테이블의 열이 다른 테이블의 열을 참조하도록 지정하여 테이블 간의 관계를 설정합니다.
  • CHECK: 테이블의 모든 행에 대해 true로 평가되어야 하는 조건을 지정합니다.

이러한 제약 조건이 정의된 열에 대해 인덱스를 생성하면 다음과 같은 이점을 얻을 수 있습니다.

  • 성능 향상: 쿼리 실행 속도를 높입니다.
  • 데이터 무결성 강화: 데이터의 유효성을 보장합니다.
  • 효율적인 데이터 관리: 데이터베이스 관리 작업을 간소화합니다.

실무 예시

다음은 실제 데이터베이스 환경에서 제약 조건과 함께 인덱스를 생성하는 예시입니다. 예시에서는 HR 스키마를 사용합니다.

PRIMARY KEY 제약 조건과 인덱스 생성

시나리오: employees 테이블에서 employee_id 열을 기본 키로 설정하고, 해당 열에 인덱스를 생성합니다.


-- 기본 키 제약 조건 생성
ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id);

-- 기본 키 제약 조건에 대한 인덱스 자동 생성
-- (대부분의 데이터베이스 시스템에서 자동으로 생성되지만,
--  명시적으로 생성할 수도 있습니다.)
CREATE INDEX emp_emp_id_idx
ON employees (employee_id);

UNIQUE 제약 조건과 인덱스 생성

시나리오: departments 테이블에서 department_name 열이 고유한 값을 가지도록 설정하고, 해당 열에 인덱스를 생성합니다.


-- UNIQUE 제약 조건 생성
ALTER TABLE departments
ADD CONSTRAINT dept_name_unq UNIQUE (department_name);

-- UNIQUE 제약 조건에 대한 인덱스 자동 생성
-- (대부분의 데이터베이스 시스템에서 자동으로 생성되지만,
--  명시적으로 생성할 수도 있습니다.)
CREATE INDEX dept_name_idx
ON departments (department_name);

NOT NULL 제약 조건과 인덱스 생성

시나리오: employees 테이블에서 last_name 열에 Null 값이 없도록 설정하고, 해당 열에 인덱스를 생성합니다.


-- NOT NULL 제약 조건 추가 (이미 NOT NULL이 아닌 경우)
ALTER TABLE employees
MODIFY (last_name CONSTRAINT emp_last_name_nn NOT NULL);

-- NOT NULL 열에 대한 인덱스 생성
CREATE INDEX emp_last_name_idx
ON employees (last_name);

FOREIGN KEY 제약 조건과 인덱스 생성

시나리오: employees 테이블에서 department_id 열이 departments 테이블의 department_id 열을 참조하도록 설정하고, 해당 열에 인덱스를 생성합니다.


-- FOREIGN KEY 제약 조건 생성
ALTER TABLE employees
ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

-- FOREIGN KEY 열에 대한 인덱스 생성
CREATE INDEX emp_dept_id_idx
ON employees (department_id);

CHECK 제약 조건과 인덱스 생성

시나리오: employees 테이블에서 salary 열이 0보다 크도록 설정하고, 해당 열에 인덱스를 생성합니다.


-- CHECK 제약 조건 생성
ALTER TABLE employees
ADD CONSTRAINT emp_salary_ck CHECK (salary > 0);

-- CHECK 제약 조건에 대한 인덱스 생성
CREATE INDEX emp_salary_idx
ON employees (salary);

제약 조건이 있는 복합 인덱스 생성

여러 열에 걸쳐 제약 조건이 정의된 경우, 복합 인덱스를 생성하여 성능을 최적화할 수 있습니다.

시나리오: employees 테이블에서 first_name과 last_name 열이 함께 고유해야 하는 경우, 복합 UNIQUE 제약 조건과 해당 인덱스를 생성합니다.


ALTER TABLE employees
ADD CONSTRAINT emp_name_uq UNIQUE (first_name, last_name);

CREATE INDEX emp_name_idx
ON employees (first_name, last_name);

주의사항

  • 인덱스 생성은 데이터베이스 성능에 영향을 미칠 수 있으므로 신중하게 결정해야 합니다.
  • 불필요한 인덱스는 오히려 성능 저하를 초래할 수 있습니다.
  • 데이터베이스 시스템에 따라 제약 조건 생성 시 자동으로 인덱스가 생성되는 경우가 있습니다. 이 경우 중복된 인덱스를 생성하지 않도록 주의해야 합니다.

결론

Oracle 데이터베이스에서 제약 조건과 함께 인덱스를 생성하는 것은 데이터 무결성을 유지하면서 쿼리 성능을 최적화하는 데 매우 효과적인 방법입니다. 본 가이드에서 제시된 예시와 지침을 통해 실제 데이터베이스 환경에서 효율적인 인덱스 전략을 수립할 수 있습니다. 항상 데이터베이스 시스템의 특성과 쿼리 패턴을 고려하여 최적의 인덱스를 설계하는 것이 중요합니다.

위로 스크롤