인덱스 클러스터 생성

인덱스 클러스터 생성

인덱스 클러스터의 생성순서

• 1. 클러스터를 생성한다.
• 2. 클러스터 인덱스를 생성한다.
• 3. 클러스터 테이블들을 생성한다.

클러스터 생성

문법

• - cluster : 클러스터 이름
• - column : 키 열 이름
• - data type : 키 열의 데이터 유형과 크기
• - TABLESPACE : 클러스터를 저장할 테이블스페이스 지정
• - SIZE : 클러스터 키의 최대 값, 킬로바이트, 또는 메가바이트 단위로 지정
• - INITRANS : 각 블록에 미리 할당된 트랜잭션 엔트리의 수를 지정(기본값은 최소값인 2)
• - MAXTRANS : 각 블록에 할당될 수 있는 트랜잭션 엔트리의 수를 제한.

예제

 
-- 클러스터 키가 deptno인 클러스터를 생성 예제.
SQL> CREATE CLUSTER emp_cluster (deptno NUMBER(3))
     TABLESPACE users;    
    

클러스터 인덱스 생성

  클러스터 인덱스의 생성은 일반 인덱스 생성과 크게 다르지 않으며 CREATE INDEX뒤에 ON CLUSTER옵션을 붙여서 클러스터 인덱스 임을 명시 합니다.

  클러스터 인덱스도 일반 인덱스 생성과 같이 PCTUSED를 사용 할 수 없습니다.

문법

• - index : 인덱스 이름
• - cluster : 클러스터 이름

예제

 
SQL> CREATE INDEX emp_cluster_idx    -- 인덱스명 지정
     ON CLUSTER emp_cluster          -- 클러스터명 지정
     TABLESPACE users;  
    

클러스터 테이블 생성

  클러스터 테이블의 생성은 일반 테이블 생성방법과 같으며 뒤에 사용할 클러스터 명을 명시해 주면 됩니다.

문법

예제

  컬럼을 공유하는 두 테이블 사이의 컬럼에 클러스터를 지정 합니다.

  이렇게 생성된 dept_cls테이블과 emp_cls테이블의 데이터는 같은 블록에 저장되게 됩니다.

 
SQL> CREATE TABLE dept_cls (
        DEPTNO    NUMBER(3) primary key,
        DNAME     VARCHAR2(14),
        LOC       VARCHAR2(13))
        CLUSTER emp_cluster (deptno);  -- 클러스터 명 지정 
테이블이 생성되었습니다.


SQL> CREATE TABLE emp_cls (
        EMPNO     NUMBER(5) PRIMARY KEY,
        ENAME     VARCHAR2(10),
        JOB       VARCHAR2(9),
        MGR       NUMBER(4),
        HIREDATE  DATE,
        SAL       NUMBER(7,2),
        COMM      NUMBER(7,2),
        DEPTNO    NUMBER(3))
        CLUSTER emp_cluster (deptno); -- 클러스터 명 지정 
테이블이 생성되었습니다.
    

출처: 이하내용

인덱스 클러스터 생성 관련 강좌 정보

• - 대용량 데이터베이스 솔루션 1 - 3.클러스터링의 활용

문서에 대하여

• - 작성자 : 김정식 (oramaster _at_ naver.com)
• - 작성일 : 2002-01-24
  - 강좌 URL : http://www.oracleclub.com/lecture/1116  
   
  
   클러스터 활용기준
• 
  
• - 지정된 컬럼값의 순서대로 로우를 저장시키는 방법
• - 하나 혹은 그 이상의 테이블을 같은 클러스터 내  저장 가능
• - 엑세스 기법이 아니라 엑세스  효율향상을 위한 물리적 저장기법
• - 검색의 효율을 높여주나 입력, 수정, 삭제시는 부하 증가
• - 분포도가 넓을 수록 오히려 유리 (인덱스의 단점을 해결 5~7배)
• - 분포도가 넓은 테이블의 클러스터링은 오히려 저장공간 절약
• - 6 블록 이상의 테이블
• - 다량의 범위를 자주 엑세스  해야 하는 경우 
• - 인덱스를 사용한 처리가 부담이 되는 넓은 분포도
• - 여러 개의 테이블이 빈번한 조인을 일으킬 때
• - 반복 컬럼이 정규화 작업에 의해 어쩔 수 없이 분할된 경우
• - UNION, DISTINCT, ORDER BY, GROUP BY 가 빈번한 컬럼이면 고려해 볼 것(통계 등) 
• - 수정이 자주 발생하지 않는 컬럼 
• - 처리범위가 넓어 문제가 발생되는 경우는 단일 테이블 클러스터링
• - 조인이 많아 문제가 발생되는 경우는 다중 테이블 클러스터링
• 
  
• 클러스터 유의사항
• 
  
• - 데이타 처리(입력,수정,삭제)에 오버헤드 발생 주의
• - 인덱스로도 충분한 범위는 클러스터링 효과가 없음 
• - 클러스터 키는 수정이 빈번하지 않을 것
• - 각종 엑세스 형태에 대해 인데스와  적절한 역할 분담
• - 클러스터링은 기존의 인덱스의 수를 감소시킴(인덱스 재구성)
• - 클러스터 SIZE parameter 가 중요
• - 클러스터 키별 로우 수의 편차가 심하지 않을 것
• - 클러스터에 데이터 입력시 로우가 적은 테이블부터 실시할 것
• - 클러스터링된  테이블 조인시  로우 수의 역순으로 FROM 절에 기술할 것(맨뒤에다 써주는 것이 좋다)
• - 클러스터 키를 첫번째로 하는 인덱스는 생성하지 말 것
  
  
• 클러스터 SIZE 예상 및 클러스터 구성 방법
• 
  
• SQL> ANALYZE  TABLE  대상테이블  COMPUTE  STATISTICS; -> 통계정보 생성
• SQL> SELECT  AVG_ROW_LEN  FROM  USER_TABLES WHERE TABLE_NAME = '대상테이블'; -> 통계정보를 이용해서 평균 로우의 길의 확인
• SQL> SELECT  AVG(COUNT(*))  FROM  대상테이블  GROUP BY 클러스터_대상_컬럼; -> 클러스터 인덱스 대상 컬럼으로 그룹바이해서 평균 로우수 확인
• 
  
• CLUSTER_SIZE = (AVG_ROW_LEN *  AVG(COUNT(*)) )  * 1.1
• - AVG_ROW_LEN   = ROW의 평균길이,  
• - AVG(COUNT(*)) = Cluster Key 당 평균 row) 
• - 1.1 = 해더값 오버해드 추가
• 
  
• SQL> CREATE  CLUSTER  클러스터명  (클러스터_대상_컬럼  CHAR(6) )
•      PCTFREE  10   PCTUSED  60   SIZE  3800; -> 클러스터 생성
• SQL> CREATE  INDEX  클러인덱스명 ON  CLUSTER  클러스터명; -> 클러스터 인덱스 생성
• 
  
• SQL> CREATE  TABLE  대상테이블 
•                         ( SALENO  VARCHAR2(6)  NOT  NULL,
•                           . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  )
•           CLUSTER  클러스터명  (클러스터_대상_컬럼) -> 클러스터테이블 생성
•           
• SQL>  INSERT  INTO  대상테이블  SELECT *  FROM  기존테이블; -> 기존 데이터 부어넣기
• SQL>  DROP  TABLE  기존테이블삭제; -> 기존 테이블 삭제
• SQL>  CREATE  INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . .  -> 필요한 인덱스 생성