GGwanG's. 꿈★은 이루어진다.

-- 200606, 200607 PARTITION TABLE CREATE
-- 매달 마다 생성되는 로그 데이타를 관리를 수월하기 위해 파티션 테이블 이용.

DROP TABLE TEST_HIS_LOG;

CREATE TABLE TEST_HIS_LOG
(
  PHONE_NUMBER  VARCHAR2(12 BYTE)               NOT NULL,
  REQUEST_ID    NUMBER(7)                       NOT NULL,
  STRT_DATE     DATE                            NOT NULL,
  END_DATE      DATE                            NOT NULL,
  PAY_TIME      NUMBER(10),
  JOB_RESULT    NUMBER(1),
  PACKET_SIZE   NUMBER(10),
  PORT          NUMBER(5),
  DESCRIPTION   VARCHAR2(1000 BYTE),
  JOB_SERVER    VARCHAR2(20 BYTE),
  REGDATE       DATE                            DEFAULT SYSDATE               NOT NULL
)
TABLESPACE MCG_DATA1
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
 INITIAL          1040K
 NEXT      1040K
 MINEXTENTS       1
 MAXEXTENTS       2147483645
 PCTINCREASE      0
 )
PARTITION BY RANGE ( REGDATE )
( PARTITION TEST_HIS_LOG_200606 VALUES LESS THAN (TO_DATE('20060701','YYYYMMDD'))
  TABLESPACE MCG_DATA1
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE (  INITIAL 1040K
 NEXT    1040K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 2147483645
 )
 ,PARTITION TEST_HIS_LOG_200607 VALUES LESS THAN (TO_DATE('20060801','YYYYMMDD'))
  TABLESPACE MCG_DATA1
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE (  INITIAL 1040K
 NEXT    1040K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 2147483645
 )
 ,PARTITION TEST_HIS_LOG_200608 VALUES LESS THAN (TO_DATE('20060901','YYYYMMDD'))
  TABLESPACE MCG_DATA1
  PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE (  INITIAL 1040K
 NEXT    1040K
 PCTINCREASE 0
 MINEXTENTS 1
 MAXEXTENTS 2147483645
 )
);

-- LOCAL INDEX
CREATE INDEX IDX#TEST_HIS_LOG_LOCAL
ON TEST_HIS_LOG (PHONE_NUMBER) LOCAL
( PARTITION TEST_HIS_LOG_200606
  TABLESPACE MCG_IDX1  PCTFREE 10       MAXTRANS 255
      STORAGE (  INITIAL 1040K   NEXT 1040K PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 2147483645 ),
  PARTITION TEST_HIS_LOG_200607
  TABLESPACE MCG_IDX1  PCTFREE 10       MAXTRANS 255
      STORAGE (  INITIAL 1040K   NEXT 1040K PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 2147483645 ),
  PARTITION TEST_HIS_LOG_200608
  TABLESPACE MCG_IDX1  PCTFREE 10       MAXTRANS 255
      STORAGE (  INITIAL 1040K   NEXT 1040K PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 2147483645 )
);

-- PK INDEX CREATE
CREATE INDEX IDX#TEST_HIS_LOG_PK ON TEST_HIS_LOG (REQUEST_ID, PHONE_NUMBER) LOCAL
TABLESPACE MCG_IDX1  PCTFREE 10       MAXTRANS 255
            STORAGE (  INITIAL 1040K   NEXT 1040K PCTINCREASE 0
                       MINEXTENTS 1 MAXEXTENTS 2147483645 );

GRANT SELECT ON  TEST_HIS_LOG TO MCG1 WITH GRANT OPTION;

-- 매달 마다 파티션 추가 , 삭제 가능하다.
   
-- 200608월 PARTITION 추가
ALTER TABLE TEST_HIS_LOG ADD PARTITION TEST_HIS_LOG_200608
            VALUES LESS THAN (TO_DATE('20060901','YYYYMMDD'))
   TABLESPACE MCG_IDX1  PCTFREE 10  PCTUSED 40
                                    INITRANS 1   MAXTRANS 255
                         STORAGE (  INITIAL 1040K   NEXT 1040K PCTINCREASE 0
                                    MINEXTENTS 1 MAXEXTENTS 2147483645 );

-- 200608월 INDEX TABLESPACE 변경
ALTER INDEX IDX#TEST_HIS_LOG_LOCAL REBUILD
PARTITION TEST_HIS_LOG_200608
TABLESPACE MCG_IDX1  PCTFREE 10       MAXTRANS 255
    STORAGE (  INITIAL 1040K    NEXT 1040K PCTINCREASE 0
        MINEXTENTS 1 MAXEXTENTS 2147483645 );

-- 파티션 삭제
ALTER TABLE TEST_HIS_LOG DROP PARTITION TEST_HIS_LOG_200608;

-- 파티션 TRUNCATE
ALTER TABLE TEST_HIS_LOG TRUNCATE PARTITION TEST_HIS_LOG_200608;

-- LOCAL INDEX REBUILD
ALTER INDEX IDX#TEST_HIS_LOG_LOCAL REBUILD PARTITION TEST_HIS_LOG_200606;

ALTER INDEX IDX#TEST_HIS_LOG_LOCAL REBUILD PARTITION TEST_HIS_LOG_200607;

ALTER INDEX IDX#TEST_HIS_LOG_LOCAL REBUILD PARTITION TEST_HIS_LOG_200608;

-- GLOBAL INDEX REBUILD
ALTER INDEX IDX#TEST_HIS_LOG_PK REBUILD PARTITION TEST_HIS_LOG_200606 ;

ALTER INDEX IDX#TEST_HIS_LOG_PK REBUILD PARTITION TEST_HIS_LOG_200607 ;

ALTER INDEX IDX#TEST_HIS_LOG_PK REBUILD PARTITION TEST_HIS_LOG_200608 ;

이 글과 관련있는 글을 자동검색한 결과입니다 [?]

• [SQL] CREATE TABLE by JiunSuk
• mysql 자주 쓰는 명령어 by 유령
• [MySQL] 기본 명령어 - 필드 추가, 수정, 삭제 by 델피니
• MBRwizard by 휘님
• Concatenate Multiple Column Records by xstation