Oracle如何最快的删除大数据量表中的多个字段-亲测有效
一、查询表数据量
-- 7945387
SELECT /*+ parallel(8) */COUNT(1) sl FROM ets.tzq_invoice_batch_ti;
-- 7945304
SELECT /*+ parallel(8) */COUNT(1) sl FROM ets.tzq_invoice_batch_t;
二、正常情况下,删除一个字段要18分钟
正常情况下,删除一个字段要18分钟(18:17)
alter table TZQ_INVOICE_BATCH_TI drop column supplier_name;
三、测试过程
3.1、创建临时表
下面为测试过程,注释掉长度>500的字段,创建临时表
执行耗时:37s
CREATE TABLE TZQ_INVOICE_BATCH_TI_TEMP
tablespace TZQ_DATA
nologging
AS
SELECT batch_id
,batch_number
,invoice_number
,batch_status
,bussiness_type
,amount
,currency
,supplier_code
--,supplier_name
--,description
,due_date
,process_status
,process_date
FROM tzq.tzq_invoice_batch_ti;
3.2、删除原表:14s
耗时:14s
drop table tzq_invoice_batch_ti PURGE;
3.3、重命名临时表为原表:0.2s
ALTER TABLE TZQ_INVOICE_BATCH_TI_TEMP RENAME TO TZQ_INVOICE_BATCH_TI;
3.4、重新授权
--重新授权 Grant/Revoke object privileges
grant select on TZQ_APWF_INVOICE_BATCH_TI to PUB_TZQ_VIEW;
3.5、参考(重建索引、主键) - 使用nologging以及parallel快速重新添加索引
使用nologging以及parallel快速重新添加索引
create index IND_Afile on A(field2) tablespace ATEMP nologging parallel 6;
3.6、重建主键
alter table A add constraint PK_field primary key (field1) using index tablespace ATEMP nologging;
–参考资料
–https://blog.csdn.net/sdmanooo/article/details/54970638