오라클 데이터베이스 설계 입문: 스키마 객체, 테이블 스페이스, 테이블 생성 및 관리

Contents

1️⃣ 오라클 스키마 객체 (Oracle schema objects)
2️⃣ 테이블 스페이스 (Table space)
3️⃣ 테이블 생성과 관리 (Creating and managing tables)

오늘은 오라클에서 테이블을 어떻게 생성하고 관리하는지 전반적인 내용을 배울 예정이다.

1️⃣ 오라클 스키마 객체

(Oracle schema objects)

오라클 데이터베이스에서 **스키마(Schema)**는 데이터 구조와 제약조건을 포함하는 논리적 단위이다. 스키마는 데이터베이스 사용자에게 할당된 객체의 집합을 의미하며, 각 사용자는 고유의 스키마를 소유한다.

스키마 객체는 데이터베이스 내에서 테이블, 인덱스, 뷰, 시퀀스 등과 같은 다양한 데이터 저장 및 접근 구조를 정의하고 관리하는 역할을 한다. 위에 사진의 가장 오른쪽 부분에서 볼 수 있는 테이블, 뷰, 인덱스 등은 전부 ‘스키마 객체’라고 할 수 있다.

스키마 객체 유형

스키마 객체에는 여러 유형이 있으며, 각 유형은 고유한 목적과 기능을 가지고 있다.

✅ 테이블(Table)
테이블은 데이터를 행(row)과 열(column) 구조로 저장하는 기본적이고 핵심적인 스키마 객체이다. 테이블의 각 행은 데이터 항목을 나타내며, 테이블은 데이터를 저장, 검색, 수정할 수 있는 가장 기본적인 저장 단위이다.

✅ 뷰(View)
뷰는 하나 이상의 테이블이나 뷰에서 데이터를 조회할 수 있는 가상의 테이블 객체이다. 실제 데이터를 저장하지 않으며, 데이터를 읽기 전용으로 제공하여 특정 컬럼만 조회하거나, 특정 조건에 맞는 데이터를 검색할 때 사용된다.

✅ 인덱스(Index)
인덱스는 테이블의 데이터에 대한 빠른 검색을 제공하는 객체이다. 테이블의 하나 이상의 컬럼을 정렬하여 저장하며, 정렬된 컬럼 데이터의 로우 주소를 통해 데이터를 빠르고 효율적으로 검색할 수 있다. 인덱스는 대량의 데이터 검색 성능을 최적화하는 데 사용된다.

✅ 파티션(Partition)
파티션은 거대한 테이블과 인덱스를 분할해 놓은 객체이다. 각 파티션은 고유의 이름을 가진다. 데이터베이스 관리자는 파티션을 통해 데이터를 논리적으로 분할하고, 관리 및 성능을 최적화할 수 있다.

✅ 시퀀스(Sequence)
시퀀스는 차례대로 자동 증가하는 값을 생성하는 객체이다. 주로 기본 키(Primary Key)로 사용될 수 있는 중복되지 않는 고유 값을 생성하는 데 사용된다.

✅ 동의어(Synonym)
동의어는 다른 오라클 객체(테이블, 뷰, 시퀀스, 프로시저 등)에 대한 대체 이름(alias)이다. 동의어는 객체의 이름이 변경될 때 투명성을 유지하거나, 객체 이름을 단순화하여 사용자가 쉽게 접근할 수 있도록 하기 위해 사용된다.

✅ 저장 프로시저(Stored Procedure)
저장 프로시저는 미리 컴파일된 PL/SQL 문장 집합으로, 특정 작업을 자동으로 실행하는 데 사용된다. 테이블이나 뷰의 데이터를 수정할 때 자동으로 실행되는 **트리거(Trigger)**도 저장 프로시저의 일종으로 볼 수 있다.

✅ 트리거 (Trigger)

트리거는 데이터베이스에서 테이블 또는 뷰의 데이터가 삽입(INSERT), 수정(UPDATE), 또는 **삭제(DELETE)**될 때 자동으로 실행되는 특별한 형태의 저장 프로시저이다. 트리거는 데이터의 무결성을 보장하고, 특정 이벤트가 발생할 때 사전에 정의된 동작을 자동으로 수행하도록 설정할 수 있다.

✅ 사용자 정의 함수 (User Defined Function)

사용자가 직접 정의한 함수로, 반복적으로 수행되는 로직을 캡슐화하여 재사용할 수 있는 스키마 객체이다. 특정 연산을 수행하고 스칼라 값 또는 테이블을 반환하는 기능을 한다.

HR 스키마 개요

위의 스키마는 Human Resource 스키마 예제이다. 직원, 부서, 위치, 경력 등의 정보가 저장되어 있다.

7개의 스키마로 구성되어 있다.
각 직원(EMPLOYEES)은 부서(DEPARTMENTS)에 속하고, 특정 직책(JOBS)을 가지며, 상사(manager_id)가 존재할 수 있다.
EMPLOYEES는 부서의 department_id를 외래 키로 참조하고, 부서는 위치(LOCATIONS)에 의해 정의된다. job_id 도 외래키로 참조한다.
JOB_HISTORY는 직원의 직무 변경 사항을 기록하며, EMPLOYEES, JOBS, DEPARTMENTS와 연결된다.
LOCATIONS는 국가(COUNTRIES)와 연결되고, COUNTRIES는 지역(REGIONS)에 의해 정의된다.

Employees 테이블

Primary Key: employee_id
컬럼: first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
연관된 테이블:
- DEPARTMENTS (부서 테이블): department_id를 통해 참조
- JOBS (직책 테이블): job_id를 통해 참조
- EMPLOYEES 자기 자신(상사-부하 관계): manager_id를 통해 참조

Departments 테이블

Primary Key: department_id
컬럼: department_name, manager_id, location_id
연관된 테이블:
- EMPLOYEES: manager_id를 통해 참조
- LOCATIONS (위치 테이블): location_id를 통해 참조

Locations 테이블

Primary Key: location_id
컬럼: street_address, postal_code, city, state_province, country_id
연관된 테이블:
- DEPARTMENTS: location_id를 통해 참조
- COUNTRIES (국가 테이블): country_id를 통해 참조

Countries 테이블

Primary Key: country_id
컬럼: country_name, region_id
연관된 테이블:
- LOCATIONS: country_id를 통해 참조
- REGIONS (지역 테이블): region_id를 통해 참조

Regions 테이블

Primary Key: region_id
컬럼: region_name
연관된 테이블:
- COUNTRIES: region_id를 통해 참조

Jobs 테이블

Primary Key: job_id
컬럼: job_title, min_salary, max_salary
연관된 테이블:
- EMPLOYEES: job_id를 통해 참조

Job history 테이블

Primary Key: employee_id + start_date
컬럼: employee_id, start_date, end_date, job_id, department_id
연관된 테이블:
- EMPLOYEES: employee_id를 통해 참조
- JOBS: job_id를 통해 참조
- DEPARTMENTS: department_id를 통해 참조

2️⃣ 테이블 스페이스 (Table space)

테이블 스페이스의 개요

테이블은 데이터베이스에서 데이터를 저장하는 기본적인 논리적 저장 단위이며, 테이블스페이스는 이러한 테이블 및 데이터베이스 객체가 물리적으로 저장되는 공간을 지정하는 역할을 한다. 테이블스페이스는 물리적으로 데이터 파일로 구성되며, 테이블 및 인덱스, 사용자별로 데이터를 논리적으로 분리하고, 이를 통해 데이터베이스의 성능을 최적화할 수 있다. 디스크 I/O 병목 현상을 줄이기 위해 테이블스페이스를 나누어 데이터를 분산 저장하면, 병렬 처리를 통한 성능 향상을 기대할 수도 있다.

테이블스페이스와 데이터 파일 (Data File)
테이블스페이스는 물리적으로 하나 이상의 데이터 파일로 구성된다. 예를 들어, USER_A 사용자는 DATAFILE1, DATAFILE2에 데이터를 저장하고, USER_B 사용자는 DATAFILE3, DATAFILE4에 데이터를 저장하도록 테이블스페이스를 설정할 수 있다. 이러한 방식으로 각 사용자의 데이터가 서로 다른 데이터 파일에 저장되어, 물리적으로 구분되도록 한다.

데이터베이스의 성능을 저하시키는 주요 요인 중 하나는 디스크 I/O이다. 디스크 I/O란 데이터베이스가 물리적 파일에 데이터를 읽고 쓰는 과정에서 발생하는 입출력 작업을 의미한다. 하나의 데이터 파일에 데이터가 집중적으로 저장되어 있다면, 해당 파일에 대한 접근 요청이 많아져 병목 현상이 발생할 수 있다. 이로 인해 디스크 I/O의 성능이 저하되고 전체 데이터베이스 성능에 부정적인 영향을 미칠 수 있다.

이러한 문제를 해결하기 위해 테이블스페이스를 분리하여 데이터 파일을 여러 개로 나누고, 데이터를 분산 저장하는 이유이다. 데이터 파일이 분산되어 있을 때, 데이터베이스 시스템은 여러 파일에 병렬적으로 접근할 수 있어 병렬 처리 성능이 향상된다. 예를 들어, DATAFILE1에서 데이터를 읽는 동시에 DATAFILE2에서 데이터를 쓸 수 있으므로 작업의 효율성이 높아지게 된다.

오라클 테이블스페이스의 종류

시스템 테이블스페이스 (System Tablespace)

시스템 테이블스페이스는 오라클 데이터베이스에서 데이터베이스 관리와 운영에 필요한 핵심적인 정보를 저장하는 논리적 저장 공간이다.
시스템 테이블스페이스는 SYSTEM과 SYSAUX 테이블스페이스로 구성되며, 데이터베이스 관리와 운영에 필요한 필수적인 정보를 저장한다.
데이터 사전, 시스템 정보, 사용자 권한 관리 정보 등이 저장되며, 데이터베이스가 정상적으로 작동하기 위해 반드시 필요하다.
관리자 권한이 있는 사용자만 접근할 수 있으며, 일반 사용자는 접근 권한이 제한된다.
데이터베이스 생성 시 자동으로 설정되며, 모니터링과 유지보수를 통해 안정적인 데이터베이스 운영을 보장해야 한다.

2. 사용자 테이블스페이스 (User Tablespace)

사용자 테이블스페이스는 데이터베이스 시스템의 관리 정보를 저장하는 데 사용된다.

용도:

사용자가 생성한 테이블, 인덱스, 롤백 세그먼트 등의 정보를 저장한다.
일반적으로 USERS라는 이름의 테이블스페이스가 기본 제공되며, 이를 사용자 데이터 저장 용도로 사용할 수 있다,

사용 예시:

여러 사용자가 공유하는 데이터베이스 환경에서, 각 사용자의 데이터를 USER_A_TABLESPACE, USER_B_TABLESPACE와 같이 별도의 테이블스페이스에 저장하여 데이터의 논리적 분리를 수행할 수 있다.

3.임시 테이블스페이스 (Temporary Tablespaces)

임시 테이블스페이스는 오라클 데이터베이스에서 SQL 문 실행 시 발생하는 임시 데이터를 저장하는 논리적 공간이다. 임시 데이터는 쿼리를 수행하는 동안에만 필요하고, 쿼리가 종료되면 더 이상 필요하지 않기때문에 임시 테이블 스페이스가 존재한다.
데이터베이스가 생성될 때 기본적으로 TEMP라는 이름의 임시 테이블스페이스가 생성된다. 모든 세션은 기본적으로 이 TEMP 테이블스페이스를 사용하여 임시 데이터를 저장한다. 필요한 경우, 사용자가 별도의 임시 테이블스페이스를 생성하여 사용자의 작업 부하를 분산할 수도 있다.

조회 : select * from dba_tablespaces;'

dba_tablespaces 뷰는 데이터베이스 내 모든 테이블스페이스의 속성을 조회할 수 있는 뷰로, 각 테이블스페이스의 이름, 블록 크기, 최대 크기, 확장 가능 여부 등의 정보를 확인할 수 있다.

SYSTEM, SYSAUX, UNDOTBS1, TEMP, USERS 등의 테이블스페이스가 있으며, 각각 시스템 관리 정보, 트랜잭션 복구, 임시 데이터 저장, 사용자 데이터 저장 등 다양한 용도로 사용된다.

각 테이블스페이스의 블록 크기, 초기 익스텐트, 최대 크기, 자동 확장 설정 등을 확인할 수 있으며, 이를 통해 테이블스페이스의 구성 및 용량을 효과적으로 관리할 수 있다.

데이터 파일과 대응 #1

select * from dba_tablespaces;의 결과물인 5개의 테이블스페이스는 데이터파일과 대응됨을 확인할 수 있다. 데이터파일 위치는 지정하기 나름이지만 기본적으로 oradata의 XE폴더에 물리적으로 저장된다.

DBF는 데이터파일의 약자이다.

데이터 파일과 대응 #2

[보기]->[dba] 선택 후, 오라클 연결1(system) 계정으로 접속

USERS 테이블스페이스의 속성 정보를 조회한 결과이다. 이 화면은 USERS 테이블스페이스의 전반적인 용량, 상태, 공간 관리 설정 등을 간략하게 보여주고 있다. USERS 테이블스페이스는 일반 사용자 데이터를 저장하기 위한 기본 테이블스페이스이다.

데이터 파일과 대응 #3

테이블스페이스에 할당된 파일을 볼 수 있음

Uploaded image

Oracle SQL Developer에서 테이블스페이스에 연결된 데이터 파일의 정보를 보여주고 있다. 각 테이블스페이스가 사용하는 파일의 경로와 상태, 크기, 사용량을 확인할 수 있다.

Size (MB):
각 데이터 파일의 총 크기를 MB 단위로 나타낸다. 예를 들어, SYSTEM 테이블스페이스의 데이터 파일 크기는 1350MB이다.
Used (MB):
각 데이터 파일에서 사용 중인 용량을 MB 단위로 나타낸다. 예를 들어, SYSTEM 테이블스페이스는 1342.5MB를 사용 중이다.
Used (Proportion):
각 데이터 파일의 전체 용량 대비 사용 중인 용량을 비율로 표시하였다. 막대 그래프를 통해 각 데이터 파일의 사용 상태를 시각적으로 확인할 수 있다.

테이블 스페이스 생성 방법

SQL Developer를 이용한 생성

Oracle SQL Developer에서 GUI를 사용하여 테이블스페이스를 생성할 수 있다.
[보기] → [dba] 메뉴로 이동하여 DBA 트리 뷰를 열고, 테이블스페이스 항목에서 마우스 오른쪽 버튼을 클릭하여 "새로 만들기" 옵션을 선택하면 된다.
이 방법은 SQL 명령어를 직접 입력하지 않아도 되므로, 초보자나 GUI 환경에서 작업을 선호하는 사용자가 쉽게 테이블스페이스를 생성할 수 있게한다.

GUI A graphical user interface, or GUI (/ˈɡuːi/ GOO-ee), is a form of user interface that allows users to interact with electronic devices through graphical icons and visual indicators such as secondary notation.

SQL DDL 문으로 생성

SQL DDL 명령어를 사용하여 테이블스페이스를 생성할 수 있다. 이는 더 유연하고 상세한 설정이 가능하다.
사용되는 기본 명령어는 CREATE TABLESPACE이다. 예를 들어, 다음과 같은 명령어를 사용하여 테이블스페이스를 생성할 수 있다.

SQL Developer로 생성 #1

SQL Developer로 생성#2

데이터 파일 경로를 지정하지 않는 경우, 사용자 데이터 경로에 파일이 생성된다.

테이블 스페이스 편집 – SQL Developer #1

생성된 테이블 스페이스는 당연히 수정할 수 있다.

편집을 통해 파일을 추가할 수 있다.

테이블 스페이스 편집 – SQL Developer #2

위와 같이 파일이 2개가 된것을인할 수 있다. 데이터는 없고 공간을 확보하는 것이다.

SQL 명령문으로 생성

[테이블스페이스 편집] : DDL 문 보기

ACADEMY_TS라는 이름으로 테이블스페이스를 생성한다.

위의 이미지에서 데이터 엔지니어로써 숙지해야 할 중요한 부분

테이블스페이스 이름 및 파일 경로 설정
- 테이블스페이스 이름: ACADEMY_TS와 같은 테이블스페이스의 이름을 설정할 때, 데이터베이스 관리 및 확장성을 고려한 명명 규칙을 따르는 것이 중요하다. 예를 들어, 업무별, 부서별, 또는 사용 목적별로 이름을 지정하여 관리의 편리성을 높일 수 있기 때문이다.
- 데이터 파일 경로: 테이블스페이스에 사용될 데이터 파일의 경로를 설정할 때, 디스크 공간 분포 및 I/O 성능을 고려해야 한다. 데이터 파일이 동일한 디스크에 몰릴 경우 I/O 병목이 발생할 수 있으므로, 데이터 파일을 서로 다른 디스크나 파일 시스템에 분산 저장하여 성능을 최적화하는 것이 중요하다.
데이터 파일 크기 및 확장 설정
- 초기 데이터 파일 크기 설정 (SIZE 옵션): 데이터 파일의 크기를 설정할 때, 데이터의 초기 용량과 예상되는 데이터 증가율을 고려하여 충분한 크기로 설정해야 한다. 예제에서는 각 파일이 10MB로 설정되어 있으나, 실제 환경에서는 데이터베이스의 크기와 성장을 고려하여 더 큰 용량으로 설정해야 할 수 있다.
- 자동 확장 설정 (AUTOEXTEND 옵션): 데이터 파일이 자동으로 확장되도록 설정하여, 예상치 못한 데이터 증가로 인한 공간 부족 문제를 방지할 수 있다. 이때 확장 단위(NEXT 크기)와 최대 크기(MAXSIZE)를 신중하게 설정하여, 디스크 공간을 효율적으로 사용할 수 있도록 해야 한다.

테이블스페이스 변경

SQL문으로 당연히 가능하다.

ALTER TABLESPACE tblspace_academy
ADD DATAFILE 'd:\oradata\academy3' SIZE 10M;

ALTER TABLESPACE tblspace_academy:
tblspace_academy라는 이름의 기존 테이블스페이스를 수정하는 명령어이다. ALTER TABLESPACE 명령어를 사용하여 테이블스페이스의 속성이나 데이터 파일을 변경할 수 있다.
ADD DATAFILE 'd:\oradata\academy3' SIZE 10M:

tblspace_academy 테이블스페이스에 새로운 데이터 파일 academy3를 추가한다는 뜻이다. 데이터 파일의 경로는 d:\oradata\academy3로 지정되어 있으며, 파일 크기는 10MB이다.

테이블스페이스 삭제

ts_academy라는 테이블스페이스를 삭제할 때 사용되는 명령어의 구조와 각 옵션의 역할을 살펴보자

DROP TABLESPACE ts_academy
INCLUDING CONTENTS
CASCADE CONSTRAINTS
AND DATAFILE;

DROP TABLESPACE ts_academy:
ts_academy라는 테이블스페이스를 삭제한다는 뜻. 테이블스페이스를 삭제하면 해당 테이블스페이스에 저장된 모든 데이터와 객체가 제거된다.
INCLUDING CONTENTS:
테이블스페이스 내에 포함된 모든 객체(테이블, 인덱스, 뷰 등)와 세그먼트를 함께 삭제한다는 뜻. 이 옵션이 없을 경우, 테이블스페이스를 삭제하려고 할 때 오류가 발생할 수 있다.
CASCADE CONSTRAINTS:
삭제할 테이블스페이스와 관련된 모든 참조 무결성 제약 조건을 함께 삭제한다는 뜻이다. 예를 들어, 삭제하려는 테이블스페이스의 테이블이 다른 테이블과 외래 키 제약 조건으로 연결되어 있을 경우, 해당 제약 조건을 자동으로 제거하게 된다.
AND DATAFILE:
테이블스페이스에 연결된 물리적 데이터 파일까지 삭제한다는 뜻이다. 이 옵션을 포함하지 않으면, 테이블스페이스는 삭제되지만 데이터 파일은 물리적으로 디스크에 남아 있을 수 있다. 이 옵션을 통해 데이터 파일을 완전히 삭제하여 디스크 공간을 확보할 수 있다. 해당 테이블스페이스에 저장된 모든 데이터와 오브젝트가 사라지므로, 삭제 전에 데이터 백업이 반드시 필요하다는 걸 알아두자.

3️⃣ 테이블 생성과 관리

(Creating and managing tables)

테이블의 개요

테이블(Table)은 데이터베이스에서 데이터를 저장하는 가장 기본적인 단위이다. 테이블은 컬럼(Column)과 로우(Row)로 구성되어 있으며, 각 로우는 데이터를 저장하고, 각 컬럼은 로우의 특정 속성을 나타낸다.

다음은 테이블에 대한 중요한 개념들이다.

컬럼(Column): 테이블에서 특정 속성이나 필드를 나타낸다. 예를 들어, 고객 테이블에서는 '이름', '나이', '주소'가 컬럼이 될 수 있다.
로우(Row): 각 로우는 테이블의 하나의 레코드를 의미하며, 컬럼에 따라 데이터를 저장한다. 예를 들어, '김철수', 25세, 서울과 같은 정보는 한 개의 로우로 저장될 수 있다.
테이블 생성 (CREATE TABLE 명령어):
- 테이블을 생성하기 위해 CREATE TABLE SQL 명령문을 사용한다.
- 테이블에는 테이블 이름과 여러 개의 컬럼이 포함된다.
- 각 컬럼에는 컬럼명, 데이터 타입(정수, 문자, 날짜 등), 그리고 컬럼 크기가 정의된다.

예시:

CREATE TABLE 고객 (
    고객ID INT PRIMARY KEY,
    이름 VARCHAR(50),
    나이 INT,
    주소 VARCHAR(100)
);

위 예시에서는 '고객'이라는 테이블을 생성하며, 고객ID, 이름, 나이, 주소와 같은 컬럼들이 포함되었다.

테이블 작업:
- 테이블이 생성된 후에는 SQL 문을 이용하여 데이터를 삽입, 수정, 삭제 및 조회할 수 있다.
- 삽입(INSERT): 새로운 데이터를 테이블에 추가
- 삭제(DELETE): 테이블에서 데이터를 삭제
- 수정(UPDATE): 테이블의 기존 데이터를 수정
- 조회(SELECT): 테이블에서 데이터를 검색하고 조회

예시로, 데이터를 삽입하는 구문은 다음과 같다.

INSERT INTO 고객 (고객ID, 이름, 나이, 주소)
VALUES (1, '김철수', 25, '서울');

이처럼 테이블을 통해 데이터베이스의 데이터를 관리하고 조작할 수 있다.

테이블의 종류

데이터베이스에서 테이블은 데이터를 저장하는 방식에 따라 여러 종류로 나뉜다. 주요 테이블의 종류는 다음과 같다

1. 일반 테이블 (Heap-organized Table)

특징: 데이터가 저장될 때 특정 순서 없이 저장
사용: 가장 일반적인 테이블 형식이며, 기본적으로 CREATE TABLE 명령어로 생성된 테이블은 이 구조를 따른다.
장점: 데이터 입력 속도가 빠르며, 특정 순서로 정렬할 필요가 없는 경우에 적합하다.
단점: 데이터를 검색할 때 추가적인 정렬 작업이 필요할 수 있으며, 검색 성능이 상대적으로 낮을 수 있다.

2. 인덱스 구조 테이블 (Index-organized Table, IOT)

특징: 데이터가 기본키(Primary Key)의 값에 따라 정렬되어 저장된다.
사용: 기본키를 자주 검색하거나, 기본키를 기준으로 데이터를 정렬된 상태로 유지해야 할 때 사용된다.
장점: 기본키 검색 성능이 뛰어나며, 데이터가 미리 정렬되어 있어 성능이 향상된다. 디스크 공간을 더 효율적으로 사용할 수 있다.
단점: 데이터 삽입 시 기본키에 따라 정렬을 유지해야 하므로, 삽입 성능이 떨어질 수 있다.

CREATE TABLE 고객IOT (
    고객ID INT PRIMARY KEY,
    이름 VARCHAR(50),
    나이 INT,
    주소 VARCHAR(100)
) ORGANIZATION INDEX;

위 예시에서 ORGANIZATION INDEX를 통해 인덱스 구조 테이블을 생성할 수 있다.

3. 외부 테이블 (External Table)

특징: 테이블의 메타데이터는 데이터베이스에 저장되지만, 실제 데이터는 외부 소스(예: 외부 파일, 다른 데이터베이스 등)에 존재한다.
사용: 데이터베이스 외부의 데이터를 읽고 분석할 때 사용되며, 주로 읽기 전용이다.
장점: 외부 데이터를 데이터베이스 내로 가져오지 않고도 활용할 수 있으므로, 저장 공간을 절약할 수 있다.
단점: 데이터는 읽기 전용이며, 외부 소스와의 연결에 의존하므로 성능이나 안정성에서 제한이 있을 수 있다.

CREATE TABLE 외부고객
(
    고객ID INT,
    이름 VARCHAR(50),
    나이 INT,
    주소 VARCHAR(100)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY 외부디렉토리
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('외부데이터.csv')
);

위 예시에서는 CSV 파일과 같은 외부 파일을 읽어올 수 있는 외부 테이블을 정의한 예시이다. 각 테이블 종류는 사용 목적과 데이터 접근 방식에 따라 선택되게 된다.

✅구조 테이블(heap-organized table) 과 인덱스구조 테이블(index-organized table)의 차이

데이터베이스에서 데이터를 저장하고 관리하는 방식에서 차이를 보인다.

테이블 생성 문법 #1

위의 이미지는 SQL에서 테이블을 생성하는 CREATE TABLE 구문의 형식을 보여주고 있다. 주요 구성 요소는 다음과 같다:

테이블 정의:
- schema.table_name: 테이블 이름과 스키마.
- Column datatype [DEFAULT expr] [column_constraint]: 각 컬럼의 이름, 데이터 타입, 기본값 (optional), 컬럼 제약조건 (optional).
테이블 속성 (optional):
- - TABLESPACE
    - 의미: 데이터를 저장할 테이블스페이스를 지정하는 곳이다. 테이블스페이스는 데이터베이스 내에서 데이터를 물리적으로 저장할 공간을 의미한다.
    - 사용: 테이블 생성 시 특정 테이블스페이스를 지정하지 않으면, 기본적으로 해당 스키마의 디폴트 테이블스페이스가 적용되게 된다.
    - PCTFREE (int)
      - 의미: 데이터 블록의 행 데이터 부분에서 새로 추가될 데이터를 위해 남겨두는 공간의 비율(%)을 지정하는 공간이다.
      - 사용: 테이블에 데이터가 삽입된 후 수정될 경우, 수정으로 인해 데이터가 더 커질 수 있는데, 이를 위해 미리 할당해 둔 여유 공간이다.
      - 예시: PCTFREE 10은 블록의 10%를 수정 시 늘어날 데이터 공간으로 남겨둔다는 의미이다.
    - PCTUSED (int)
      - 의미: 데이터가 저장된 블록의 충진도를 비율(%)로 설정하는 곳이다.
      - 사용: 블록이 데이터로 가득 차게 되었을 때, 이 값보다 사용된 공간이 적으면 블록은 재사용될 수 있게 된다.
      - 예시: PCTUSED 40은 블록 내에서 40% 미만의 공간만 차있을 때 그 블록이 재사용될 수 있음을 의미한다.
    - INITTRANS (int)
      - 의미: 하나의 데이터 블록에 지정될 초기 트랜잭션 슬롯의 개수를 설정한다.
      - 사용: 동시에 여러 트랜잭션이 데이터를 수정하거나 삽입하는 상황에서 블록이 관리할 수 있는 초기 트랜잭션의 수를 나타낸다.
      - 예시: INITTRANS 2는 각 데이터 블록에서 두 개의 트랜잭션을 동시에 관리할 수 있음을 의미한다.
    - MAXTRANS (int)
      - 의미: 하나의 데이터 블록에서 동시에 관리할 수 있는 최대 트랜잭션 수를 지정하는 곳이다.
      - 사용: 특정 블록에서 동시에 처리할 수 있는 트랜잭션 수를 제한하여, 동시성 제어를 관리한다.
      - 예시: MAXTRANS 255는 하나의 데이터 블록에서 동시에 255개의 트랜잭션이 접근할 수 있음을 의미한다.

위 구문은 데이터베이스에서 테이블을 생성할 때 사용되는 명령어의 기본 구조를 설명하고 있다. 테이블 생성 시 위의 옵션들을 사용하여 테이블스페이스를 지정하거나, 블록 내의 데이터 공간 관리 및 트랜잭션 동시성 제어 등을 설정할 수 있다.

테이블 생성 예제 #1

테이블 생성 예제 #2

TABLESPACE USERS: 테이블이 USERS라는 테이블스페이스에 저장됨.
PCTFREE 10: 블록의 10%를 데이터 수정 시 사용할 여유 공간으로 남겨둠.
PCTUSED 40: 블록의 40%가 사용될 때 블록이 재사용 가능하도록 설정.
INITRANS 1: 초기 트랜잭션 수를 1로 설정.
MAXTRANS 100: 블록당 최대 100개의 트랜잭션을 허용.

위의 예시는 employees_temp라는 임시 테이블을 생성할 때 사용하는 구체적인 SQL 명령문과 그 속성을 보여주고 있다.

테이블 데이터 블록

위의 이미지는 데이터베이스 블록(Database Block)에서 로우(Row)가 저장되는 방식과 블록 내부 구조를 보여주고 있다. 각 데이터베이스 블록은 데이터베이스가 데이터를 저장하는 기본 단위로, 해당 이미지에서 로우 조각(row piece)과 데이터 블록의 다양한 구성 요소를 시각적으로 표현하고 있다. 위 이미지에서 알아야 할 중요한 부분은 데이터베이스가 데이터를 블록 단위로 관리하며, 각각의 데이터 블록이 어떻게 구성되고 동작하는지를 이해하는 것이다.

로우 헤더(Row Header): 로우에 대한 메타데이터(예: 컬럼 수, 클러스터 키 ID, ROWID 등)를 저장.
컬럼 데이터(Column Data): 각 컬럼의 실제 데이터 값이 저장되는 부분.
데이터베이스 블록(Database Block): 데이터베이스가 데이터를 저장하는 기본 단위.
- 공통 및 가변 헤더: 블록에 대한 정보 저장.
- 테이블 디렉터리: 테이블 관련 정보 저장.
- 로우 디렉터리: 블록 내 로우의 위치를 추적.
- 자유 공간: 새로운 데이터를 삽입할 때 사용되는 공간.
- 로우 데이터: 실제 데이터가 저장되는 부분.

Row Migration

Row Migration은 데이터블록에 공간이 부족할 때 로우가 업데이트되면서 크기가 커지면 다른 데이터 블록으로 이동하는 현상을 일컫는다.
기존 로우가 업데이트되어 데이터 크기가 커질 경우, 해당 블록에 더 이상 저장 공간이 없으면 다른 블록으로 이동되게 된다.
이 과정에서 원래 블록에 포인터를 남겨, 해당 로우의 새로운 위치를 추적할 수 있도록 한다.
데이터를 조회할 때, 원래 블록에서 포인터를 통해 다른 블록을 참조해야 하므로 추가적인 디스크 I/O가 발생해 성능이 저하되게 된다.
이를 최소화하기 위해 적절한 PCTFREE 설정과 정기적인 테이블 관리가 필요하다.

이는 데이터 수정 시 블록 내 공간 관리의 중요성을 보여주며, 빈번한 Row Migration은 성능 저하를 일으킬 수 있음을 알아두자

Row Chaining

로우 체이닝이란 로우가 하나의 데이터 블록에 완전히 저장될 수 없을 때, 데이터를 여러 블록에 나누어 저장하는 현상을 나타낸다.

왼쪽 블록에는 첫 번째 로우 조각(Piece 1)이 저장되고, 그 다음 데이터는 같은 블록에 저장될 공간이 없어 다른 블록(오른쪽 블록)에 두 번째 로우 조각(Piece 2)이 저장된다.
Row Piece Pointer: 첫 번째 블록에서 두 번째 블록의 로우 위치를 추적하는 포인터로 연결하게 된다.
추가적인 I/O 작업: 데이터를 조회할 때 여러 블록을 참조해야 하므로 추가적인 디스크 I/O가 발생해 성능이 저하될 수 있고 큰 데이터를 자주 저장하거나 수정하는 테이블에서는 Row Chaining이 자주 발생할 수 있다.
이를 피하려면 블록 크기와 PCTFREE 값을 적절히 관리하는 것이 중요하다.

✅ Row Chaining과 Row Migration의 차이:

Row Chaining: 로우 자체가 너무 커서 처음부터 여러 블록에 걸쳐 저장되는 경우.
Row Migration: 데이터가 수정되어 기존 블록에 남은 공간이 부족해 다른 블록으로 옮겨지는 경우.

PCTREE 파라미터

위의 이미지는 PCTFREE 설정을 설명하는 다이어그램이다. PCTFREE = 20으로 설정된 데이터 블록을 보여주고 있으며, 이는 블록 내 20%의 공간을 여유 공간으로 남겨둔다는 의미이다.

20% Free Space: 데이터 블록은 80%까지 데이터 삽입이 가능하며, 나머지 20%는 수정된 로우가 업데이트될 때를 대비해 여유 공간으로 남겨둔다.
PCTFREE 값은 데이터 삽입 후 여유 공간을 얼마나 남길지를 결정하는 중요한 속성이다.

중요한 부분:

PCTFREE의 역할:
- PCTFREE는 데이터 수정을 위해 데이터 블록에서 남겨두는 공간의 비율을 뜻한다. 즉, 데이터를 삽입한 후에도 블록 내 공간의 일부를 비워 둬서 수정 시 데이터를 추가할 수 있도록 하는 역할을 한다.
- 이미지에서 PCTFREE = 20은 블록의 80%가 차면 더 이상 데이터를 삽입하지 않고, 20%는 나중에 삽입된 데이터가 수정될 때 사용되도록 남겨둔다는 뜻이다.
성능에 미치는 영향:
- 적절한 PCTFREE 값 설정: PCTFREE 값을 너무 낮게 설정하면 수정 시 공간이 부족해 Row Migration이 발생할 수 있다. 반대로 너무 높게 설정하면 여유 공간이 너무 많아 저장 공간을 비효율적으로 사용되게 된다.
- 데이터 수정이 잦은 경우: 데이터가 자주 수정되는 테이블에서는 적절한 PCTFREE 값을 설정하여 성능을 최적화할 수 있다.
PCTFREE와 데이터 관리:
- 삽입과 수정 간의 균형: 데이터가 처음 삽입될 때는 최대한 블록을 채우지만, 수정을 고려해 일정 비율의 공간을 남겨둔다. 이를 통해 데이터 수정 시 Row Migration을 줄이고 성능을 향상시킬 수 있다.
- 테이블 특성에 맞는 PCTFREE 값: 테이블의 데이터 삽입 및 수정 패턴을 분석하여 최적의 PCTFREE 값을 설정하는 것이 중요하다.

핵심 요약:

PCTFREE는 데이터 수정 시 사용할 여유 공간을 설정하는 속성으로, 성능 및 저장 공간 효율성에 큰 영향을 미칩니다.
PCTFREE 값 설정은 데이터 수정 빈도에 맞춰 적절히 관리해야 하며, 잘못된 설정은 성능 저하나 비효율적인 공간 사용을 초래할 수 있습니다.

PCUSED 파라미터

PCTUSED는 데이터 블록이 재사용될 수 있는 시점을 결정하는 기준이다. 이 값은 테이블에서 데이터를 삭제했을 때 얼마나 많은 공간이 사용 중인지에 따라 블록을 다시 사용할 수 있는지를 결정한다. 테이블을 생성할 때 PCTUSED 값은 데이터 삽입, 삭제, 수정 시 블록 사용률 관리의 중요한 요소를 한다.

PCTUSED의 역할:
- PCTUSED는 데이터 블록에서 최소한으로 사용되어야 하는 비율을 지정한다. 즉, 블록의 사용량이 이 값보다 떨어질 때 해당 블록이 다시 새로운 데이터를 저장할 수 있는 상태로 변경된다.
- 예를 들어, PCTUSED = 40이라면, 해당 블록의 사용 공간이 40% 이하로 떨어져야 다시 새로운 데이터가 삽입될 수 있게된다.
동작 방식:
- 하나의 로우가 삭제되면 데이터 블록에 여유 공간이 생기게 됩니다. 이때 블록의 사용 영역이 PCTUSED 값 이하로 떨어지면 해당 블록이 새로운 데이터 입력을 허용할 수 있게 된다.
- 반대로, PCTUSED 값보다 높은 사용 공간이 유지된다면 새로운 데이터가 해당 블록에 삽입되지 않고 다른 블록에 삽입되게 된다.
PCTUSED 값이 작은 경우:
- 블록이 재사용되지 않는 상황이 발생할 수 있으며, 미사용 공간이 증가할 가능성이 큽니다. 이는 블록이 가득 차거나 PCTUSED 값을 만족하지 않으면 다시 데이터가 들어갈 수 없기 때문이다,
- 결과적으로 데이터 공간 낭비가 발생할 수 있다.
PCTUSED 값이 큰 경우:
- 공간 활용도는 향상되며, 사용된 블록이 빠르게 재사용될 수 있다.
- 하지만, 재사용을 위해 빈 공간을 더 자주 확인해야 하기 때문에 처리 비용이 증가할 수 있다. 블록의 사용 상태를 자주 점검해야 하므로 성능에 영향을 미칠 수 있다.

요약:

PCTUSED는 데이터 블록에서 새로운 데이터가 삽입될 수 있는 최소한의 사용률을 의미한다.
PCTUSED 값이 작을 경우: 블록이 재사용되지 않아 미사용 공간이 많아질 수 있음.
PCTUSED 값이 클 경우: 공간 활용도가 높아지지만, 처리 비용이 증가할 수 있음.
최적의 PCTUSED 값을 설정하는 것은 테이블의 데이터 삽입 및 삭제 패턴에 맞추어 성능 최적화와 공간 효율성을 동시에 고려하는 것이 중요하다.

권장 설정

PCTFREE와 PCTUSED의 권장 설정을 상황에 따라 어떻게 적용해야 하는지 알아보자. 다양한 상황에서 두 값이 어떻게 설정되는지에 대한 기준에 대해서 설명한다.

UPDATE 시 로우의 크기가 증가할 때
- PCTFREE = 20: 수정 시 데이터를 추가할 수 있도록 20%의 여유 공간을 남겨둔다.
- PCTUSED = 40: 블록의 사용 공간이 40% 이하로 떨어질 때 다시 데이터를 삽입할 수 있게 설정한다.
- 적용 상황: 데이터 수정이 빈번하고, 수정 시 데이터 크기가 증가하는 경우에 적합하다.
INSERT, DELETE가 자주 발생하지만, UPDATE 시 로우 크기가 증가하지 않을 때
- PCTFREE = 5: 수정 시 크기 증가가 없으므로 5% 정도의 여유 공간만 남겨둔다.
- PCTUSED = 60: 사용 공간이 60% 이하로 떨어질 때 블록이 재사용된다.
- 적용 상황: 삽입과 삭제는 자주 일어나지만, 데이터 크기가 수정될 때 증가하지 않는 경우에 적합하다.
테이블이 매우 크지만 변경이 거의 발생하지 않을 때
- PCTFREE = 5: 변경이 거의 없기 때문에 여유 공간을 최소한으로 설정한다.
- PCTUSED = 90: 블록이 거의 가득 찰 때까지 데이터를 삽입한 후 재사용을 시작한다.
- 적용 상황: 테이블이 매우 크고, 데이터 수정이 거의 발생하지 않는 읽기 전용 또는 변경이 드문 경우에 적합하다.

알아야 할 중요한 부분:

PCTFREE와 PCTUSED의 최적 설정은 테이블의 사용 패턴에 따라 달라진다.
데이터 수정이 빈번하고 크기가 커질 경우, 여유 공간을 충분히 남겨야 한다(PCTFREE 값이 크고, PCTUSED 값이 낮음).
반대로, 수정이 거의 없고 삽입만 자주 발생하는 경우, 여유 공간을 적게 설정하고, 더 많은 데이터를 블록에 저장할 수 있다(PCTFREE 값이 작고, PCTUSED 값이 큼).
적절한 설정은 성능 최적화와 공간 효율성을 동시에 달성하는 데 중요하다. 테이블의 성격에 맞는 최적 값을 설정해야 성능 저하나 공간 낭비를 피할 수 있다.

테이블 수정 #1

ALTER TABLE 명령어는 테이블의 구조 변경을 위한 중요한 SQL 문이다. 테이블 이름 변경, 컬럼 추가/수정/삭제와 같은 작업을 수행할 수 있으며, 이는 데이터베이스 관리에 매우 유용하다. 데이터 무결성을 고려하여 신중하게 사용해야 하며, RENAME이나 DROP과 같은 작업은 의존성을 확인하고 사용해야 함을 알아두자.

ALTER TABLE 명령어 주요 작업:

RENAME TO: 테이블 이름을 변경한다는 명령어이다.
- 예: ALTER TABLE test_01 RENAME TO test_02;
ADD: 새로운 컬럼을 추가
- 예: ADD (column_name datatype [DEFAULT expr] [column_constraint])
MODIFY: 기존 컬럼의 데이터 타입, 기본값 등을 수정
- 예: MODIFY (column_name datatype [DEFAULT expr])
DROP COLUMN: 테이블에서 특정 컬럼을 삭제
- 예: DROP COLUMN column_name
RENAME COLUMN: 테이블 내의 특정 컬럼의 이름을 변경
- 예: RENAME COLUMN old_column TO new_column

중요한 부분:

테이블 구조 수정 가능:
- ALTER TABLE 명령어는 테이블의 이름이나 컬럼을 변경하거나, 새로운 컬럼을 추가하고 기존 컬럼을 수정 및 삭제할 수 있는 강력한 도구이다.
- 이를 통해 테이블을 재정의하지 않고도 구조적인 변경을 쉽게 적용할 수 있다.
데이터 무결성 유지:
- 테이블의 컬럼을 추가하거나 수정할 때, 데이터 무결성을 유지하는 것이 중요하다. 예를 들어, 기존에 존재하는 데이터에 영향을 미치지 않도록 수정 시 주의를 기울여야 한다.
RENAME과 DROP 사용 시 주의:
- 테이블 이름이나 컬럼 이름을 RENAME(이름 변경)하거나 DROP(삭제)할 때는, 해당 이름을 참조하는 다른 데이터베이스 객체(뷰, 인덱스 등)가 있는지 확인해야 한다. 잘못된 사용은 시스템에 연결 문제를 일으킬 수 있기 때문이다.

테이블 수정 #2 컬럼 추가 ADD

추가된 컬럼은 항상 마지막에 위치한다.
테이블에 기존 데이터가 있는 경우, 새로운 컬럼의 값은 기본적으로 NULL로 채워진다.
만약 테이블에 데이터가 존재한다면, NOT NULL 제약 조건이 있는 컬럼은 추가할 수 없다. 이는 테이블의 기존 행들에 대해 NULL 값을 허용하지 않기 때문에 발생하는 제약이다.
반면 테이블이 비어 있을 경우, NOT NULL 제약 조건이 있는 컬럼을 추가할 수 있다.
오른쪽의 예제는 ‘다중 컬럼 추가’이다. 여러 컬럼을 한 번에 추가할 수 있으며, 마찬가지로 새 컬럼은 테이블의 마지막에 추가된다.

테이블 수정 #3 컬럼 삭제 - DROP COLUMN

이 명령어는 test_01 테이블에서 col_03 컬럼을 삭제할 수 있다.

데이터 유무와 관계없이 삭제(Delete regardless of whether data exists)
- 컬럼에 데이터가 있더라도 삭제할 수 있다. 컬럼을 삭제하면 해당 컬럼에 저장된 모든 데이터도 삭제된다.
마지막 컬럼 삭제 불가(Cannot delete the last column)
- 테이블의 마지막 컬럼은 삭제할 수 없다. 컬럼 삭제 작업은 마지막 컬럼을 제외한 컬럼에서만 수행 가능하다.
제약 조건 및 인덱스(Constraints and index)
- 컬럼이 제약 조건(예: 외래 키, 기본 키)이나 인덱스의 일부일 경우, 그 컬럼은 삭제할 수 없다.
- 이런 경우, 해당 컬럼을 삭제하려면 먼저 제약 조건이나 인덱스를 제거해야 한다.

테이블 수정 #4 컬럼 이름 변경 RENAME ~ TO ~

이 명령어는 test_01 테이블에서 컬럼 col_03의 이름을 col_06으로 변경할 수 있다.

RENAME COLUMN 사용:
- 컬럼의 이름을 변경하기 위해서는 RENAME COLUMN 구문을 사용한다.
- 테이블 자체의 이름을 변경하는 것과는 다른 구문이므로 혼동하지 않아야 한다.
하나의 컬럼에만 적용:
- 한 번에 하나의 컬럼 이름만 변경할 수 있다. 여러 컬럼의 이름을 동시에 변경하는 기능은 제공되지 않으므로, 각 컬럼에 대해 개별적으로 변경해야 한다.

테이블 삭제 - DROP TABLE 명령문

제약 조건 삭제 (Deleting constraints):
- 테이블을 삭제할 때, 해당 테이블에 적용된 모든 제약 조건(예: 기본 키, 외래 키)도 함께 삭제된다.
외래 키 참조 문제 (Foreign key reference issue):
- 다른 테이블이 외래 키로 참조하는 테이블은 바로 삭제할 수 없다. 이 경우, 외래 키 참조 관계를 먼저 중지하거나 삭제해야 한다.
- 외래 키 참조 관계를 끊기 전까지는 해당 테이블의 삭제가 불가능하다.
CASCADE CONSTRAINTS 옵션(option):
- 다른 테이블에서 외래 키로 참조 중인 경우, 먼저 외래 키 참조 관계를 끊거나 CASCADE CONSTRAINTS 옵션을 사용하여 외래 키를 함께 삭제할 수 있다.
- 즉 CASCADE CONSTRAINTS 옵션을 사용하면, 삭제하려는 테이블과 연관된 모든 외래 키도 함께 삭제된다. 결과적으로 외래 키를 따로 제거하지 않고도 테이블을 삭제할 수 있게된다. (예 DROP TABLE test_01)

테이블 복제 #1

CREATE TABLE table_name: 새로운 테이블을 생성할 때 사용하는 명령어이다. table_name은 생성할 테이블의 이름을 뜻한다.
AS subquery: 서브쿼리 결과를 사용하여 테이블을 생성하는 명령어이다. subquery는 기존 테이블에서 데이터를 조회하는 SELECT 쿼리로, 이 쿼리에서 반환된 컬럼 구조와 데이터를 기반으로 새로운 테이블이 생성된다.
1. 테이블 생성과 동시에 데이터 복사(Creating a table and copying data simultaneously)
  - 서브쿼리의 결과로 새로운 테이블이 만들어지고, 쿼리 결과에 따라 데이터도 함께 복사된다.
2. 인덱스 및 제약 조건 미복사(Indexes and constraints are not copied)
  - 서브쿼리로 만든 테이블에는 인덱스나 제약 조건이 포함되지 않으므로, 필요한 경우 새로 설정해야 한다.
3. 데이터 타입 자동 설정(Data types are automatically set)
  - 서브쿼리에서 반환된 컬럼의 데이터 타입을 기반으로 새 테이블의 컬럼 데이터 타입이 자동으로 설정된다.

테이블 복제 #2

서브쿼리를 사용하여 테이블을 복제한 후, 복제된 테이블에서 데이터를 조회하는 과정을 보여주고있다.

서브쿼리로 테이블 복제: create table as 구문을 사용하여, 기존 테이블의 데이터를 서브쿼리를 통해 새로운 테이블로 복사할 수 있다.
구조와 데이터만 복제: 테이블의 구조와 데이터만 복제되며, 인덱스나 제약 조건은 복사되지 않는다.
데이터 조회: 복제된 테이블은 원본 테이블과 동일한 데이터를 포함하지만, 이후에 복제된 테이블에서 데이터를 독립적으로 수정할 수 있다.

학습정리

Command Palette