Oracle表碎片查询以及整理（高水位线）

参考：https://blog.csdn.net/shiyu1157758655/article/details/78051637

 

 

记2020.8.6 一次数据碎片整理思路  我采取的是全面新建表

--创建新表

create table DPCA_PURCHASE_INFO_yx as SELECT * FROM DPCA_PURCHASE_INFO;
--备份旧表--删除bak表上的索引
alter table DPCA_PURCHASE_INFO rename to DPCA_PURCHASE_INFO_bak;
--把新表重命名为 DPCA_PURCHASE_INFO
alter table DPCA_PURCHASE_INFO_yx rename to DPCA_PURCHASE_INFO
--创建索引
create index DPCA_PURCHASE_INFO_BGDH on DPCA_PURCHASE_INFO (办公电话);
create index DPCA_PURCHASE_INFO_CC on DPCA_PURCHASE_INFO (网点编码);
create index DPCA_PURCHASE_INFO_CX on DPCA_PURCHASE_INFO (车系);
create index DPCA_PURCHASE_INFO_GSJTDH on DPCA_PURCHASE_INFO (公司联系人家庭电话);
create index DPCA_PURCHASE_INFO_JTDH on DPCA_PURCHASE_INFO (家庭电话);
create index DPCA_PURCHASE_INFO_M on DPCA_PURCHASE_INFO (公司联系人联系电话);
create index DPCA_PURCHASE_INFO_M1 on DPCA_PURCHASE_INFO (联系电话);
create index DPCA_PURCHASE_INFO_M2 on DPCA_PURCHASE_INFO (移动电话);
create index DPCA_PURCHASE_INFO_M4 on DPCA_PURCHASE_INFO (公司联系人移动电话);
create index DPCA_PURCHASE_INFO_M5 on DPCA_PURCHASE_INFO (公司联系人办公电话);
create index DPCA_PURCHASE_INFO_SBRQ on DPCA_PURCHASE_INFO (申报日期);
create index DPCA_PURCHASE_INFO_SFZJ on DPCA_PURCHASE_INFO (身份证号企业组织代码);
create index DPCA_PURCHASE_INFO_VIN on DPCA_PURCHASE_INFO (VIN码);
--收集该表所有信息（包括索引）
exec dbms_stats.gather_table_stats(ownname =>user ,tabname=>'DPCA_PURCHASE_INFO' ,estimate_percent => 20,degree => 10,granularity => 'ALL',cascade => TRUE);
--分析该表所有信息（包括索引）
analyze table DPCA_PURCHASE_INFO compute statistics;