Oracle 表空间、自增分区分区表压缩

原创 Mr.Cui 2023-12-06

638

Oracle 表空间、自增分区分区表压缩

– 创建压缩表空间

create tablespace compress_data_tbs DATAFILE 'XXX' size 100m autoextend on next 128m maxsize unlimited default COMPRESS FOR OLTP;

复制

– omf 管理，可不指定名称

create tablespace compress_data_tbs DATAFILE size 100m autoextend on next 128m maxsize unlimited default COMPRESS FOR OLTP NOLOGGING;
复制

– nologging 设置不记录日志，需要关闭DB级force logging

alter database no force logging;
复制

– 删除表空间及数据文件

drop tablespace compress_data_tbs including contents and datafiles; 
复制

– 创建压缩分区表（按月自动分区），如果是使用的压缩表空间，可不指定compress参数

DROP TABLE SCOTT.TEST_COMPRESS_PARTTAB;

CREATE TABLE SCOTT.TEST_COMPRESS_PARTTAB
(
    COL1            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL
  , COL2            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL
  , COL3            VARCHAR2(10 BYTE) DEFAULT (' ') NOT NULL
  , TIME            VARCHAR2(17 BYTE) DEFAULT (' ') NOT NULL
  , COL5            VARCHAR2(50 BYTE) DEFAULT (' ') NOT NULL
  , COL6            VARCHAR2(25 BYTE) DEFAULT (' ') NOT NULL
  , COL7            VARCHAR2(20 BYTE) DEFAULT (' ') NOT NULL
  , COL8            VARCHAR2(30 BYTE) DEFAULT (' ') NOT NULL
  , COL9            VARCHAR2(30 BYTE) DEFAULT (' ') NOT NULL
  , COL10           VARCHAR2(4000 BYTE) DEFAULT (' ') NOT NULL
  , CLOB_COL        CLOB DEFAULT (' ') NOT NULL
  , PARTITION_DATE  TIMESTAMP(3) GENERATED ALWAYS AS(TO_TIMESTAMP(TIME, 'yyyymmddhh24missff3'))
)
LOB (CLOB_COL) STORE AS SECUREFILE (TABLESPACE DATA_TS ENABLE STORAGE IN ROW COMPRESS)
PARTITION BY RANGE(PARTITION_DATE) INTERVAL ( NUMTOYMINTERVAL(1, 'MONTH') ) STORE IN (DATA_TS,IDX_TS)
    (   PARTITION PART_202201 VALUES LESS THAN (TO_DATE('2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202202 VALUES LESS THAN (TO_DATE('2022-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202203 VALUES LESS THAN (TO_DATE('2022-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202204 VALUES LESS THAN (TO_DATE('2022-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202205 VALUES LESS THAN (TO_DATE('2022-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202206 VALUES LESS THAN (TO_DATE('2022-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202207 VALUES LESS THAN (TO_DATE('2022-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202208 VALUES LESS THAN (TO_DATE('2022-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202209 VALUES LESS THAN (TO_DATE('2022-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202210 VALUES LESS THAN (TO_DATE('2022-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202211 VALUES LESS THAN (TO_DATE('2022-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202212 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202301 VALUES LESS THAN (TO_DATE('2023-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202302 VALUES LESS THAN (TO_DATE('2023-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202303 VALUES LESS THAN (TO_DATE('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202304 VALUES LESS THAN (TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202305 VALUES LESS THAN (TO_DATE('2023-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202306 VALUES LESS THAN (TO_DATE('2023-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202307 VALUES LESS THAN (TO_DATE('2023-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202308 VALUES LESS THAN (TO_DATE('2023-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202309 VALUES LESS THAN (TO_DATE('2023-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202310 VALUES LESS THAN (TO_DATE('2023-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS
      , PARTITION PART_202311 VALUES LESS THAN (TO_DATE('2023-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) COMPRESS FOR OLTP TABLESPACE DATA_TS)
PCTFREE 1
COMPRESS FOR OLTP
TABLESPACE DATA_TS
NOCACHE
ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX SCOTT.TEST_COMPRESS_PARTTAB_PK ON SCOTT.TEST_COMPRESS_PARTTAB(COL1, COL2, COL3, TIME, COL5, COL6, COL7) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1;
ALTER TABLE SCOTT.TEST_COMPRESS_PARTTAB ADD(CONSTRAINT TEST_COMPRESS_PARTTAB_PK PRIMARY KEY(COL1, COL2, COL3, TIME, COL5, COL6, COL7) USING INDEX SCOTT.TEST_COMPRESS_PARTTAB_PK ENABLE VALIDATE);
CREATE INDEX SCOTT.TEST_COMPRESS_PARTTAB_IDX_01 ON SCOTT.TEST_COMPRESS_PARTTAB(COL1, COL2, TIME) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1 LOCAL;
CREATE INDEX SCOTT.IDX_TEST_COMPRESS_PARTTAB_TIME ON SCOTT.TEST_COMPRESS_PARTTAB(TIME) COMPRESS ADVANCED HIGH TABLESPACE IDX_TS PCTFREE 1 LOCAL;

复制