写点什么

oracle 已有表的分表分区优化操作步骤(单表过大)

作者:zhengzai7
  • 2023-07-26
    陕西
  • 本文字数:2230 字

    阅读完需:约 7 分钟

oracle已有表的分表分区优化操作步骤(单表过大)

第一章、步骤总览

0、获取创建表空间 DDL、创建表空间(该步骤在将分区放入不同的表空间时采用)

1、基于原表 A 在同一表空问建立临时分区表 B

2、将原表 A 数据插入到新建的临时分区表 B

3、验证分区表查询性能

4、将原表 A 重命名为 A TEMP

5,指临附分区表日重命店沙示行

6、删除原表 A_TEMP

第二章、现有表的分区优化改造步骤

第 1 节、获取创建表空间语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TS.tablespace_name) FROM DBA_TABLESPACE TS;
复制代码

第 2 节、创建表空间

CREATE TABLESPACE "MY TABLESPACE"DATAFILE SIZE 943718400AUTOEXTEND ON NEXT 943718400 MAXSIZE 32767MLOGGING ONLINE PERMANENT BLOCKSIZE 819255EXTENT MANAGEMENT LOCAL AUTOALLOCATEDEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
复制代码

第 3 节、创建分区表

3.1、创建分区表

CREATE TABLE "BL_TEMP" ("UUID" VARCHAR2(50 BYTE) NOT NULL ENABLE,"FIELD" VARCHAR2(50 BYTE) NOT NULL ENABLE,"MY DATE" DATE NOT NULL ENABIE )partition by range (MY_DATE)(partition B2020 values less than(TO DATE '2020-01-01'. 'YYYY-MM-DD'))SEGMENT CREATION IMMEDIATEPOTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255NOCOMPRESS LOGGING STORAGE(    INITIAL 17825792     NEXT 1048576    MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE O FREELISTS 1 FREELIST GROUPS    BUFFER POOL DEFAULT FLASH CACHE    DEFAULT CELL FLASH CACHE DEFAULT)TABLESPACE "MY TABLESPACE"partition B2022 values less than(TO DATE '2022-01-01'. 'YYYY-MM-DD'))SEGMENT CREATION IMMEDIATEPOTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255NOCOMPRESS LOGGING STORAGE(    INITIAL 17825792     NEXT 1048576    MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE O FREELISTS 1 FREELIST GROUPS    BUFFER POOL DEFAULT FLASH CACHE    DEFAULT CELL FLASH CACHE DEFAULT)TABLESPACE "MY TABLESPACE"partition B2023 values less than(TO DATE '2023-01-01'. 'YYYY-MM-DD'))SEGMENT CREATION IMMEDIATEPOTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255NOCOMPRESS LOGGING STORAGE(    INITIAL 17825792     NEXT 1048576    MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE O FREELISTS 1 FREELIST GROUPS    BUFFER POOL DEFAULT FLASH CACHE    DEFAULT CELL FLASH CACHE DEFAULT)TABLESPACE "MY TABLESPACE"partition AFTER2020 values less than(MAXVALUE)SEGMENT CREATION IMMEDIATEPOTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS255NOCOMPRESS LOGGING STORAGE(    INITIAL 17825792     NEXT 1048576    MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE O FREELISTS 1 FREELIST GROUPS    BUFFER POOL DEFAULT FLASH CACHE    DEFAULT CELL FLASH CACHE DEFAULT)TABLESPACE "MY TABLESPACE");
复制代码

3.2、创建索引、主键等

CREATE UNIQUE INDEX TBL_TEMP_PK" ON "TBL_TEMP" ("UUID")POTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE STATISTICSSTORAGE(INITIAL 2097152 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645POTINCREASE 0 FREELISTS 1 FREELIST GROUPS1BUFFER POOL DEFAULTFLASH CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "MY_TABLESPACE";
ALTER TABLE "TBLTEMP" ADD CONSTRAINT"TBL_TEMP_PK" PRIMARY KEY ("UUID")USING INDEX "TBL_TEMP_PK" ENABLE;COMMENT ON COLUMN "TBL TEMP" "UUID" IS '主键';COMMENT ON COLUMN "TBL_TEMP" "FIELD" IS '字段'COMMENT ON COLUMN "TBL_TEMP" "DATE" IS '日期'
复制代码

第 4 节、数据迂移

INSERT INTO TBL_TEMP (UUID, FIELD,DATE) SELECT UUID, FIELD, DATE TBL;
复制代码

第 5 节、查看分区是否正常,以及查询效率测试

5.1、插入测试数据

--存储过程1:插入 2020的数据,进入2020分区declare     i int;    begin    for i in 1..500000 loop     Insert into TBL_TEMP (UUID, FIELD, DATE)    values (i, 'FIELD', to_date('2020-03-01','YYYY-MM-DD));END LOOP;COMMIT;END;
复制代码


--存储过程1:插入 2022 年的数据,进入2022分区declare     i int;    begin    for i in 1..500000 loop     Insert into TBL_TEMP (UUID, FIELD, DATE)    values (i, 'FIELD', to_date('2022-03-01','YYYY-MM-DD));END LOOP;COMMIT;END;
复制代码

5.2、查询性能

-—分区表查询性能-全量select * from "TBL_TEMP";select * from "TBL_TEMP" partition(B2020); select * from "TBL_TEMP" partition(B2022);select * from "TBL_TEMP" partition(OTHERS);
复制代码


--条件查询select * from "TBL_TEMP" where MY_DATE > to_date('2023-02-02','yyyy-mm-dd');select * from "TBL_TEMP" partition(B2020) where MY_DATE>to_date('2023-02-02','yyyy-mm-dd');select * from "TBL_TEMP" partition(B2022) where MY_DATE > to_date('2023-02-02','yyyy-mm-dd');select * from "TBL_TEMP" partition(OTHERS) where MY_DATE > to_date('2023-02-02','yvyy-mm-dd');select * from "TBL_TEMP" where MY_DATE > to_date('2023-02-02','yyyy-mm-dd');
复制代码

5.3、结论

100W 级别数据表条件查询速度平均能优化 10ms。因此,全量查询并未优化,而条件查询有优化。

插入性能也得到提升。

6、重命名原表

RENAME "TBL" TO "TBL_OLD";
复制代码

7、重命名分区表

RENAME "TBL_TEMP" TO "TBL";
复制代码


用户头像

zhengzai7

关注

还未添加个人签名 2023-07-07 加入

还未添加个人简介

评论

发布
暂无评论
oracle已有表的分表分区优化操作步骤(单表过大)_oracle_zhengzai7_InfoQ写作社区