Yys@kangwon.ac.kr You YOungseok 데이터베이스 테이블 및 인덱스 yys@kangwon.ac.kr You YOungseok.

Presentation on theme: "Yys@kangwon.ac.kr You YOungseok 데이터베이스 테이블 및 인덱스 yys@kangwon.ac.kr You YOungseok."— Presentation transcript:

1 yys@kangwon.ac.kr You YOungseok
데이터베이스 테이블 및 인덱스 You YOungseok

2 오라클 테이블 유형 힙 구조 테이블 인덱스 구조 테이블 인덱스 클러스터 테이블 해시 클러스터 테이블 중첩 테이블 임시 테이블
데이터가 추가 될 때 가장 적절한 빈 공간에 저장 CREATE TABLE에 의해 생성되는 기본적인 테이블 유형 정렬되지 않은 행들의 모임 인덱스 구조 테이블 기본 키(색인)에 따라 정렬된 순서로 저장 인덱스 클러스터 테이블 여러 테이블의 테이터가 동일한 블록에 저장될 수 있음 동일한 클러스터 키 값을 가지는 데이터는 함께 저장됨 클러스터 키는 B*트리 인덱스를 사용하여 만들어짐 해시 클러스터 테이블 중첩 테이블 임시 테이블 객체 테이블

3 힙 구조 테이블 기존 테이블 생성처럼 일반 CREATE TABLE 문을 사용하면 된다
CREATE TABLE Dept ( dept_no int, dept_name varchar(100) );

4 인덱스 구조 테이블 CREATE TABLE 시 PRIMARY KEY를 추가하고 구문 끝에 ORGANIZATION INDEX를 추가함 CREATE TABLE index_Dept ( dept_no int PRIMARY KEY, dept_name varchar(100) ) ORGANIZATION INDEX; ORA 에러가 발생하면 TABLESPACE를 만들어야 함 CREATE TABLESPACE [테이블스페이스 명] DATAFILE [절대경로] SIZE [용량] AUTOEXTEND ON -- 자동 테이블스페이스 파일 추가 EXTENT MANAGEMENT LOCAL AUTOALLOCATE; 예시) CREATE TABLESPACE test_space DATAFILE ‘C:\DBP\TABLESPACE\test_space.DBF' SIZE 10M AUTOEXTEND ON -- 자동 테이블스페이스 파일 추가 EXTENT MANAGEMENT LOCAL AUTOALLOCATE; CREATE TABLE index_dept ( deptno int PRIMARY KEY , dept_name varchar(100)) ORGANIZATION INDEX OVERFLOW TABLESPACE test_space; -- 테이블 스페이스 명

5 테이블스페이스 데이터를 논리적으로는 테이블스페이스에 저장하고 물리적으로는 해당 테이블스 페이스와 연관된 데이터 파일(Data file)들에 저장한다. 데이터 파일(Data file)은 하나의 테이블스페이스에만 속한다. 테이블스페이스는 하나 이상의 데이터 파일(Data file)로 구성됩니다. 데이터 파일, 테이블스페이스와 스키마 객체의 관계

6 테이블스페이스의 논리적 구조 테이블스페이스는 논리적 데이터베이스 구조에서 가장 큰 상위의 단위
데이터 베이스: 테이블 스페이스 하나 이상 포함 테이블 스페이스 : 세그먼트가 하나 이상 포함 저장소(세그먼트): 논리적 블록으로 구성된 확장영역 블록: 읽기 및 쓰기 작업의 최소 단위 확장영역: 연속적 데이터블록으로 구성되며 세그먼트 저장 단위로 사용

7 인덱스 클러스터 구조 테이블 먼저 클러스터와 클러스터에 대한 인덱스를 만들어야 함 클러스터 상에 테이블을 생성 함
create cluster emp_dept_cluster (dept_no int) size 1000; create index emp_dept_cluster_idx on cluster emp_dept_cluster; 클러스터 상에 테이블을 생성 함 CREATE TABLE cluster_Dept ( dept_no int, dept_name varchar(100) ) cluster emp_dept_cluster(dept_no); 클러스터를 삭제 하는 법 DROP CLUSTER 클러스터 이름 [ INCLUDING TABLES ];

8 해시 클러스터 구조 테이블 인덱스 클러스터와 개념적으로 유사하지만, 해시 함수를 이용하는 점이 다르다.
인덱스 클러스터 구조와 마찬가지로 먼저 클러스터를 만듦 하지만 클러스터에 인덱스가 필요 없고 해시 함수를 위한 옵션들이 추가 됨 CREATE CLUSTER hash_cluster (hash_key int) HASHKEYS SIZE 256 SINGLE TABLE HASH IS hash_key 클러스터에 테이블을 추가하는 방법은 같다 CREATE TABLE Hash_Dept ( deptno int, dept_name varchar(100) ) CLUSTER hash_cluster(deptno); 클러스터 구조 테이블의 경우 조인 쿼리에 대해 높은 성능을 보인다

9 실습 #1 앞에서 만든 각 테이블 종류 별로 아래의 쿼리를 실행한 뒤 결과를 비교해 보자 DROP TABLE Dept;
INSERT INTO Dept VALUES(1, 'A'); INSERT INTO Dept VALUES(3, 'C'); INSERT INTO Dept VALUES(2, 'B'); INSERT INTO Dept VALUES(5, 'E'); SELECT * FROM Dept; DELETE FROM Dept where dept_name = 'B'; INSERT INTO Dept values(4, 'D'); DROP TABLE Dept; CREATE TABLE Dept ( dept_no int, dept_name varchar(100) ); INSERT INTO Dept VALUES(1, 'A'); INSERT INTO Dept VALUES(3, 'C'); INSERT INTO Dept VALUES(2, 'B'); INSERT INTO Dept VALUES(5, 'E'); SELECT * FROM Dept; DELETE FROM Dept where dept_name = 'B'; INSERT INTO Dept values(4, 'D'); DROP TABLE index_Dept; CREATE TABLE index_Dept ( dept_no int PRIMARY KEY, ) ORGANIZATION INDEX; INSERT INTO index_Dept VALUES(1, 'A'); INSERT INTO index_Dept VALUES(3, 'C'); INSERT INTO index_Dept VALUES(2, 'B'); INSERT INTO index_Dept VALUES(5, 'E'); SELECT * FROM index_Dept; DELETE FROM index_Dept where dept_name = 'B'; INSERT INTO index_Dept values(4, 'D'); drop index emp_dept_cluster_idx; drop cluster emp_dept_cluster including tables; -- 테이블 동시 삭제 --drop table cluster_Dept; create cluster emp_dept_cluster (dept_no int) size 1000; create index emp_dept_cluster_idx on cluster emp_dept_cluster; CREATE TABLE cluster_Dept ( ) cluster emp_dept_cluster(dept_no); INSERT INTO cluster_Dept VALUES(1, 'A'); INSERT INTO cluster_Dept VALUES(3, 'C'); INSERT INTO cluster_Dept VALUES(2, 'B'); INSERT INTO cluster_Dept VALUES(5, 'E'); SELECT * FROM cluster_Dept; DELETE FROM cluster_Dept where dept_name = 'B'; INSERT INTO cluster_Dept values(4, 'D'); drop cluster hash_cluster including tables; CREATE CLUSTER hash_cluster ( hash_key int ) HASHKEYS 10000 SIZE 256 SINGLE TABLE HASH IS hash_key; CREATE TABLE hash_Dept ( ) cluster hash_cluster(dept_no); INSERT INTO hash_Dept VALUES(1, 'A'); INSERT INTO hash_Dept VALUES(3, 'C'); INSERT INTO hash_Dept VALUES(2, 'B'); INSERT INTO hash_Dept VALUES(5, 'E'); SELECT * FROM hash_Dept; DELETE FROM hash_Dept where dept_name = 'B'; INSERT INTO hash_Dept values(4, 'D');

10 실습 #1 결과 힙 구조 테이블 인덱스 구조 테이블 인덱스 클러스터 구조 테이블 해시 클러스터 구조 테이블

11 실습 #1 테이블 삭제 DROP TABLE Dept; DROP TABLE index_Dept;
DROP INDEX emp_dept_cluster_idx; DROP CLUSTER emp_dept_cluster INCLUDEING TABLES; -- 테이블 동시 삭제 --DROP TABLE cluster_Dept; DROP CLUSTER hash_cluster INCLUDEING TABLES;

12 실습 #2 아래 테이블을 각 테이블 종류 별로 만드세요 다음 코드를 이용하여 각 테이블에 테스트용 데이터를 삽입 EMPNO
EMP, INDEX_EMP, CLUSTER_EMP, HASH_EMP DEPT, INDEX_DEPT, CLUSTER_DEPT, HASH_DEPT 다음 코드를 이용하여 각 테이블에 테스트용 데이터를 삽입 EMPNO EMP_NAME SALARY DEPTNO INT VARCHAR(100) DEPTNO DEPT_NAME INT VARCHAR(100)

13 실습 #2 각 테이블 구조에 대한 다음 질의의 실행 플랜을 서로 비교하기
select * from emp where empno = 65000; select * from index_emp where empno = 65000; select * from cluster_emp where empno = 65000; select * from hash_emp where empno = 65000; 각 테이블 구조에 대한 다음 조인 질의의 실행 플랜을 서로 비교하기 select empno, salary, dept_name from emp, dept where emp.deptno = dept.deptno ; select empno, salary, dept_name from index_emp, index_dept where index_emp.deptno = index_dept.deptno; select empno, salary, dept_name from cluster_emp, cluster_dept where cluster_emp.deptno = cluster_dept.deptno; select empno, salary, dept_name from hash_emp, hash_dept where hash_emp.deptno = hash_dept.deptno;

14 % 실행 플랜 보는 방법 자동 추적 기능(또는 F6 키)를 선택함

15 보조 인덱스 만들기 기본 키에 대한 보조 인덱스 만들기 테이블에 기본 키를 지정하여 테이블을 생성함
create table pk2_emp (empno int primary key, emp_name varchar(100), salary int, deptno int); 비 기본 키에 대한 보조 인덱스 만들기 비기본 키에 대해 명시적으로 색인을 생성함 create table nk2_emp (empno int, emp_name varchar(100), salary int, deptno int); create index nk2_emp_idx on nk2_emp (salary);

16 실습 #3 기본키 보조 색인를 갖는 힙구조 테이블, 비기본키 보조 색인을 갖는 힙구조 테이 블에 데이터 입력함
기본키 보조 색인를 갖는 힙구조 테이블, 비기본키 보조 색인을 갖는 힙구조 테이 블에 데이터 입력함 insert into pk2_emp select * from emp; insert into nk2_emp select * from emp; 아래 질의를 실행하여 실행 플랜을 서로 비교하기 select empno, salary from emp where empno = 65000; select empno, salary from pk2_emp where empno = 65000; select empno, salary from nk2_emp where empno = 65000; select empno, salary from emp where salary = 30000; select empno, salary from pk2_emp where salary = 30000; select empno, salary from nk2_emp where salary = 30000;