第一章、步骤总览
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 943718400
AUTOEXTEND ON NEXT 943718400 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 819255
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
DEFAULT 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 IMMEDIATE
POTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
NOCOMPRESS 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 IMMEDIATE
POTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
NOCOMPRESS 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 IMMEDIATE
POTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
NOCOMPRESS 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 IMMEDIATE
POTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
NOCOMPRESS 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 255
COMPUTE STATISTICS
STORAGE(
INITIAL 2097152 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
POTINCREASE 0 FREELISTS 1 FREELIST GROUPS
1
BUFFER POOL DEFAULT
FLASH 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";