分区表分区过大拆分分区

分区表分区过大拆分分区

备份分区表分区

select * from dba_direcoties

vim HS_FIN_DETAIL.par
userid='/ as sysdba'
directory=DATA_PUMP_DIR02
dumpfile=HS_FIN_DETAIL.dmp
logfile=1.log
tables=ODS_KY.HS_FIN_DETAIL:P202007

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dowKXxgC-1640251131374)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20211126160250475.png)]

查询分区表分区数据量,分区的类型

set lines 150
set pages 50
col table_name for a30
col table_owner for a20
select table_owner,table_name,partition_name,ini_trans,BLOCKS from dba_tab_partitions where table_owner='ODS_KY' and table_name='HS_FIN_DETAIL';


set lines 150
set pages 50
select 
table_name,table_owner,partition_name,high_value from dba_tab_partitions where table_name='HS_FIN_DETAIL' and TABLE_OWNER='ODS_KY';


	

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xpBJmPfG-1640251131376)(C:\Users\wang\AppData\Roaming\Typora\typora-user-images\image-20211126160613642.png)]

对用户表进行分区操作()

alter table ODS_KY.HS_FIN_DETAIL
split partition P202007
at (to_date('2020-04-01','yyyy-mm-dd'))
into
(partition P202200702,
 partition P202200701);
 
 
分区表名称在分割时可以重复名字,进行分区
alter table SCOTT.HS_EWBS_MUSTCARGOBOARD
split partition P20190101
at (to_date('2018-01-10','yyyy-mm-dd'))
into
(partition P20190103,
 partition P20190101);
 

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ol7kkyzD-1640251131377)(分区表分区过大拆分分区.assets/image-20211127094921359.png)]

查看表的索引

set lines 150
set pages 50
select index_name, INDEX_TYPE,TABLE_NAME,status from dba_indexes where TABLE_NAME='HS_FIN_DETAIL';

重建索引

alter index ODS_KY.I_HS_FIN_DETAIL_4 rebuild parallel 4;
alter index ODS_KY.PK_HS_FIN_DETAIL rebuild parallel 4;
alter index ODS_KY.I_HS_FIN_DETAIL_OPT rebuild parallel 4;
alter index ODS_KY.I_HS_FIN_DETAIL_EWB rebuild parallel 4;
alter index ODS_KY.I_HS_FIN_DETAIL_CHARGE rebuild parallel 4;


alter index ODS_KY.I_HS_FIN_DETAIL_4 noparallel;
alter index ODS_KY.PK_HS_FIN_DETAIL noparallel;
alter index ODS_KY.I_HS_FIN_DETAIL_OPT noparallel;
alter index ODS_KY.I_HS_FIN_DETAIL_EWB noparallel;
alter index ODS_KY.I_HS_FIN_DETAIL_CHARGE noparallel;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XqjpYADs-1640251131378)(分区表分区过大拆分分区.assets/image-20211127095052316.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1ZtAFmZf-1640251131378)(分区表分区过大拆分分区.assets/image-20211127095137888.png)]

重新收集统计信息

begin
dbms_stats.gather_table_stats(
ownname=>'KYMAIN',
tabname=>'HS_FIN_DETAIL',
estimate_percent=>100,
cascade=>TRUE,
method_opt=>'for all columns size auto',
degree=>8);
end;
/

如果统计信息,和索引重建完 发现数据查询缓慢可进行创建索引

create index ODS_KY.IDX$$_BE360001 on ODS_KY.HS_FIN_DETAIL("CREATED_TIME");

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2M2H3oku-1640251131378)(分区表分区过大拆分分区.assets/image-20211127171437517.png)]

验证

set lines 150
set pages 50
col table_name for a30
col table_owner for a20
select table_owner,table_name,partition_name,ini_trans,BLOCKS from dba_tab_partitions where table_owner='ODS_KY' and table_name='HS_FIN_DETAIL';


select *  from ODS_KY.HS_FIN_DETAIL	 partition(P202200701)

结论

当进行分区分割时 会造成全局索引失效

避免方式

alter table ODS_KY.HS_FIN_DETAIL
split partition P202007
at (to_date('2020-04-01','yyyy-mm-dd'))
into (partition P202200702,partition P202200701) update global indexes;	


alter index idx_part_split_col1 rebuild; 

te(‘2020-04-01’,‘yyyy-mm-dd’))
into (partition P202200702,partition P202200701) update global indexes;

alter index idx_part_split_col1 rebuild;