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 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";