제 5 장 인덱스 생성 및 관리.

Presentation on theme: "제 5 장 인덱스 생성 및 관리."— Presentation transcript:

1 제 5 장 인덱스 생성 및 관리

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

3 오라클 테이블 유형 힙 구조 데이블 생성 예제: 인덱스 구조 테이블 생성 예제: CREATE TABLE dept (
x int primary key, y varchar2(25), z date ) ; 인덱스 구조 테이블 생성 예제: z date ) organization index;

4 오라클 테이블 유형 클러스터 테이블 생성 예제: Create cluster emp_dept_cluster
(deptno number(2)) size 1200; Create index emp_dept_cluster_idx on cluster emp_dept_cluster; Create table cluster3_dept (deptno number(2) primary key, dname varchar2(14), … ) cluster emp_dept_cluster(deptno); Create table cluster_emp (empno number primary key, ename varchar2(10), … deptno number(2) references dept(deptno) )

5 오라클 테이블 유형 두 테이블에 대한 클러스터 생성 예: 10 20 30 40

6 Speed-start Web services 2004 - SOAs and Web services
▪ 오라클의 인덱스 (INDEX) 테이블의 데이터를 빨리 찾기 위한 꼬리표/찾아보기 인덱스 데이터가 별도로 저장됨 저장된 인덱스를 통해 데이터 조회  보조 인덱스 오라클 인덱스의 종류 B*트리 인덱스 : 기본 인덱스/클러스터링 인덱스/보조 인덱스 등을 저장하는 자료 구조 인덱스 구조 테이블 : 기본 인덱스 B*트리 클러스터 인덱스 : 클러스터 인덱스 반전 키 인덱스 내림차순 인덱스 비트맵 인덱스 함수 기반 인덱스 애클리케이션 도메인 인덱스 인터미디어 텍스트 인덱스 ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

7 Speed-start Web services 2004 - SOAs and Web services
▪ 오라클의 보조 인덱스 생성/제거 인덱스 생성 CREATE [UNIQUE] INDEX [schema.] index_name ON [schema.] table_name (col1, [col2, col3 …]); create index emp_idx on emp(ename, dno); 인덱스 제거 DROP INDEX [schema.] index_name; drop index emp_idx; ibm.com/developerWorks/webservices - © Copyright 2004 IBM Corporation.

8 인덱스 관리 인덱스 생성시 고려 사항 인덱스는 열 단위에 생성된다 Where 절에서 자주 사용되는 열을 인덱스로 만든다
데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다 외래 키가 사용되는 열에는 인덱스를 생성하는 것이 좋다 Join에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋다 INSERT, DELETE, UPDATE가 얼마나 자주 일어나는지 고려해야 한다 클러스터링 인덱스는 하나만 생성할 수 있다 사용하지 않는 인덱스는 제거하라 계산 열에도 인덱스를 활용할 수 있다

9 04. 인덱스 데이터베이스의 물리적 저장 인덱스와 B-tree 오라클 인덱스 인덱스의 생성 인덱스의 재구성과 삭제

10 SQL Developer / SQL PLUS
1. 데이터베이스의 물리적 저장 중앙처리장치 OS TOOL CPU 사용자 UI SQL Developer / SQL PLUS 오라클 클라이언트 커 널 DBMS 주기억장치 RAM 오라클 INSTANCE 파일시스템 보조기억장치 DB FIELS 데이터베이스 HDD, SSD 데이터 파일 컨트롤 파일 리두 로그파일 그림 4-7 DBMS와 데이터 파일

11 1. 데이터베이스의 물리적 저장 그림 4-9 오라클의 내부 구조

12 1. 데이터베이스의 물리적 저장 표 4-8 오라클의 주요 파일 파일 설명 데이터 파일 운영체제상에 물리적으로 존재
사용자 데이터와 개체를 저장 테이블과 인덱스로 구성. 온라인 리두 로그 데이터의 모든 변경사항을 기록 데이터베이스 복구에 사용되는 로그 정보 저장 최소 두 개의 온라인 리두 로그 파일 그룹을 가짐 컨트롤 파일 오라클이 필요로 하는 다른 파일들(데이터 파일, 로그 파일 등)의 위치 정보를 저장 데이터베이스 구조 등의 변경사항이 있을 때 자동으로 업데이트됨 오라클 DB의 마운트, 오픈의 필수 파일 복구 시 동기화 정보 저장

13 2. 인덱스와 B-tree 인덱스(index, 색인)란 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조 그림 4-10 B-tree의 구조

14 2. 인덱스와 B-tree 인덱스의 특징 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함
빠른 검색과 함께 효율적인 레코드 접근이 가능함 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함 저장된 값들은 테이블의 부분집합이 됨 일반적으로 B-tree 형태의 구조를 가짐 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요함 그림 4-11 B-tree에서 검색 예

15 3.1 오라클 B-tree 인덱스 오라클 인덱스는 B-tree를 변형하여 사용하며 명칭은 B-tree로 동일한 이름으로 부름
그림 4-12 인덱스의 예

16 3.2 오라클 인덱스의 종류 표 4-9 오라클 인덱스의 종류

17 4. 인덱스의 생성 인덱스 생성 시 고려사항 인덱스의 생성 문법 인덱스는 WHERE 절에 자주 사용되는 속성이어야 함
인덱스는 조인에 자주 사용되는 속성이어야 함 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음(테이블 당 4~5개 정도 권장) 속성이 가공되는 경우 사용하지 않음 속성의 선택도가 낮을 때 유리함(속성의 모든 값이 다른 경우) 인덱스의 생성 문법 CREATE [REVERSE]┃[UNIQUE] INDEX 인덱스이름] ON 테이블이름 (컬럼 [ASC┃DESC] [{, 컬럼 [ASC | DESC]} …])[;]

18 4. 인덱스의 생성 질의 Book 테이블의 bookname 열을 대상으로 비 클러스터 인덱스 ix_Book을 생성하라. CREATE INDEX ix_Book ON Book (bookname); 질의 Customer 테이블의 name 열을 대상으로 클러스터 인덱스 cix_Customer를 생성하시오. CREATE INDEX ix_Book2 ON Book(publisher, price);

19 4. 인덱스의 생성 표 4-14 생성된 인덱스 확인 및 실행 계획

20 5. 인덱스의 재구성과 삭제 인덱스의 재구성은 ALTER INDEX 명령을 사용함 생성 문법 삭제 문법
ALTER [REVERSE] [UNIQUE] INDEX 인덱스이름 [ON {ONLY} 테이블이름 {컬럼이름 [{, 컬럼이름 } …])] REBUILD[;] 질의 인덱스 ix_Book을 재생성하시오. ALTER INDEX ix_Book REBUILD; DROP INDEX 인덱스이름 질의 인덱스 ix_Book을 삭제하시오. DROP INDEX ix_Book;