오라클 스키마 객체
스키마(Schema)
- 데이터 스키마는 데이터 구조와 제약조건을 포함하는 논리적 단위
- 오라클에서는 테이블, 인덱스 등의 객체를 스키마 객체라고 표현함
테이블(Table)
- 관계형데이터베이스의 가장 핵심적인 객체
- 데이터를 로우(행) 단위로 저장
인덱스(Index)
- 데이터 검색을 위한 빠른 접근을 제공하는 저장구조
- 테이블의 하나 이상의 컬럼을 정렬해서 가지고 있는 객체
- 정렬된 컬럼 데이터가 속해있는 로우의 주소를 이용해 빠르고 직접적으로 접근
파티션(Patition)
- 거대 테이블과 인덱스를 분할해 놓은 것
- 각 파티션은 고유의 이름을 가짐
뷰(View)
- 가상의 테이블 객체
- 하나 이상의 테이블 또는 뷰에서 데이터를 보는 방법을 제공
시퀀스(Sequence)
- 차례대로 자동 증가하는 값을 생성하는 객체
- 중복되지 않는 기본키 값을 생성하는데 사용됨
동의어(Synonyms)
- 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(alias)
- 참조하는 객체의 이름 변동시 투명성을 유지
- 이름을 간단하게 사용하거나 보안을 위해서도 사용
사용자 정의 함수(User defined function)
- 스칼라 값 또는 테이블 중 하나를 반환
- 비번히 수행되느 기능을 캡슐화 하는데 사용
저장 프로시져(Stored Procedure)
- 함께 실행되는 PL/SQL문의 집합으로 사전 컴파일됨
트리거(Trigger)
- 사용자가 테이블이나 뷰의 데이터를 수정할때 자동으로 실행되는 특별한 형태의 저장 프로시져
Human Resource ERD 실습
테이블 스페이스(Table Space)
- 테이블스페이스는 물리적으로 하나 이상의 데이터 파일로 구성됨
- 각각의 테이블을 테이블 스페이스로 나누어 관리의 편리성과 성능 향상
- 테이블스페이스를 생성하면 정의된 용량만큼 미리 확보한 테이블 스페이스가 생성되고, 생성된 테이블스페이스에 테이블의 데이터가 저장
오라클 테이블 스페이스의 종류
시스템 테이블 스페이스
- 데이터베이스 관리를 위해 오라클이 사용
- SYSTEM, SYSAUX 테이블 스페이스
- 데이터 사전과 데이터베이스 관리 정보가 포함된 테이블이 저장
- 시스템 사용자 권한을 관리자만 접근 가능
사용자 테이블스페이스
- 사용자들이 필요한 테이블, 인덱스, 롤백 세그먼트 등의 정보를 저장
- USERS 이름의 테이블 스페이스가 제공
임시 테이블스페이스
- SQL문을 처리할 때 발생하는 임시 테이블과 인덱스를 저장
- 기본적으로 TEMP 테이블 스페이스
테이블 생성과 관리
테이블(Table)
- 컬럼, 로우로 표현
- 테이블 정의는
CREATE TABLE 명령문
- 테이블 이름과 컬럼 집합으로 구성
- 각각의 컬럼에 컬럼명(column name), 데이터 타입(data type), 컬럼크기(size)를 명시
테이블 종류
힙구조 테이블 (heap-organized table)
- 데이터 로우를 특정 순서대로 저장하지 않음
- 기본 설정
인겍스구조 테이블(index-organized table)
- 기본키 값에 따라 로우를 순서대로 저장
- 검색 질의어에 따라 미리 정렬해 놓는 것이 성능향상 및 디스크 공간 활용을 효율적으로 함
외부 테이블(external table)
- 메타데이터는 데이터베이스에 있지만, 실제 데이터는 외부 데이터베이스에 있음
- 읽기 전용의 테이블
테이블 생성 문법
[TABLESPACE] tablespace:
- 데이터를 저장할 테이블스페이스를 지정
- 해당 스키마에 지정된 디폴트값이 존재
[PCTFREE int]:
- 수정시 늘어나는 데이터를 사용하기 위한 공간
- 데이터가 저장될 블록의 행 데이터 부분의 크기를 퍼센트로 지정
[PCTUSED int]: 재사용하는데 필요한 블록의 충진도 값을 설정[INTTRANS int]: 하나의 데이터 블록에 지정될 초기 트랜잭셔 값을 지정[MAXTRANS int] 하나의 데이터 블록에 지정될 트랜잭션의 최대 수를 지정
데이터베이스 블록(Database Block)
Row Migration
- 데이터블록의 공간이 없는 상태에서 기존의 로우가 업데이트 되었을때 그 크기가 커지면 다른 블록으로 옮기고 그 블록의 로우 위치에 대한 포인터를 유지
[PCTFREE int] 값의 중요성! 해당 값을 잘설정해야지 해당 동작이 덜 작동한다.
Row Chaining
- 데이터가 커서 여러 블록에 나누어 저장하는 현상
- 2개 이상의 데이터 블록을 검색해야 하므로 성능 감소
- 블록에 들어갈 수 있는 크기의 행을 지정해야 된다.
PCTFREE 파라미터
- 데이터 수정후 데이터가 2개의 블록에 저장되면 하나의 로우를 검색하기위해 여러개의 블록을 가져와야 하기때문에 검색 성능이 떨어짐
- PCTFREE 20 으로 지정하였다면 테이블이 생성될 때 할당된 블록의 행 데이터 영역의 크기에서 20% 공간을 Inert 문에서 사용하지 말고 Update를 위해 확보하아는 의미
PCTUSED 파라미터
- 하나의 로우가 지워질 때마다 데이터블록에 공간이 생긴다. 이 공간이 생길 때까지 데이터 입력이 되지 않도록 유지한다.
- PCTUSED 40으로 지정하였다면 테이블의 데이터 세그먼트에 사용된 데이터 블록의 사용영역이 40% 이하가 되지 않는다면 데이터를 입력할 수 없음
- PCTUSED 값이 적을 경우: 블록이 재사용되는 일이 적어질수 있으므로 사용되지 않는 공간이 증가
- PCTUSED 값이 큰 경우: 공간 사용도는 향상되지만 처리비용이 증가
PCTUSED, PCTFREE 권장설정
- UPDATE시 ROW의 크기가 증가될때
- PCTFREE = 20
- PCTUSED = 40
- INSERT, DELETE가 자주 발생하지만, UPDATE시 ROW 크기가 증가되지 않을 때
- 테이블이 매우 크지만 변경의 거의 발생하지 않을 때
테이블 수정
칼럼 추가
- 추가된 컬럼은 항상 마지막 순서에 놓임
- 추가된 데이터는 NULL로 삽입됨
- 테이블 내에 데이터가 존재한다면 NOT NULL인 컬럼을 추가할 수 없음
- 빈 테이블인 경우에는 NOT NULL 컬럼이 추가가능
ALTER TABLE test_01
ADD col_03 VARCHAR(30);
ALTER TABLE test_01
ADD (col_04 VARCHAR(30),
col_05 VARCHAR(30));
컬럼 삭제
- 컬럼의 데이터 유무에 상관없이 삭제됨
- 테이블의 마지막 컬럼은 삭제할 수 없음
- 제약조건의 일부이거나 인덱스의 일부인 경우 삭제할 수 없음
ALTER TABLE test_01
DROP COLUMN col_03;
컬럼 이름 변경
ALTER TABLE test_01
RENAME COLUMN col_03 TO col_06;
테이블 삭제
- 테이블 삭제시 포함된 제약조건 모두 삭제됨
- 외래키에 참도죈 경우에는 외래키 사용을 중지해야 테이블을 삭제할 수 있음
CASCATE CONSTRAINTS 옵션을 사용하면 삭제하는 테이블과 연괸된 모든 외래키를 함께 삭제함
DROP TABLE table_name [CASCADE CONSTRAINTS];
테이블 복제
- 서브쿼리를 이용하여 테이블을 복제
- 서브 쿼리에서 얻어진 자료도 같이 입력됨
- 인덱스, 기본키 등은 복제되지 않음
- 데이터 타입은 서브쿼리에서 참조된 테이블의 컬럼을 기반으로 정의됨
CREATE TABLE table_name
AS table_name_temp;
SELECT * FROM table_name_temp;
