删除大表T_HP_PRODUCT-优快云博客

删除一张大表的过程

1,直接删除，删除4万行用了半小时

2,建好表结构（包括索引和约束），然后insert ,,, As Select * From ,这种方式将undo插入满了，虽然我这里用的是append加nologging的方式。

3,直接create Table As Select * From ,,,这种方式建好表只要6分钟，然后就是建立索引和约束了。

-----------------------------------------------------------------

删除的第一部始终是检查它是否当了别人的’部门‘，虽然直接删除此表数据

会报外键约束的错误，但是通过rename的方式移表可以跳过外键检查，这里

就会很危险，所以必须要检查清楚

select a.owner 主键拥有者,

a.table_name 主键表,

b.column_name 主键列,

C.OWNER 外键拥有者,

c.table_name 外键表,

d.column_name 外键列

from user_constraints a,

user_cons_columns b,

user_constraints C,

user_cons_columns d

where a.constraint_name=b.constraint_name

and C.R_CONSTRAINT_NAME=a.constraint_name

and c.constraint_name=d.constraint_name

and a.constraint_type='P'

and a.table_name='T_HP_PRODUCT' --需要查看主外键关系的表

order by a.table_name

它没有当别人的部门，可以直接删除它的数据，这里也不会

报外键约束错误。

-----------------------------------------------------------------

Select Count(*) From T_HP_PRODUCT

Select Max(SALE_DATE) From T_HP_PRODUCT --2050/12/31

Select Min(SALE_DATE) From T_HP_PRODUCT --2010/1/1

-----------------------------------------------------------------

<1>采用直接删除的方法，4万行的数据居然用了半个小时，这里绝对是

外键，索引和碎片的问题

Delete From T_HP_PRODUCT Where SALE_DATE

Between to_date('2010-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')

and to_date('2010-03-02 00:00:00','yyyy/MM/DD/ HH24:MI:SS')

查看此表发现者张表上面有很多索引

Select User_Ind_Columns.Index_Name,

Index_Type,

Uniqueness,

Last_Analyzed,

Column_Name,

User_Indexes.Table_Name,

Tablespace_Name

From User_Indexes, User_Ind_Columns

Where User_Indexes.Index_Name = User_Ind_Columns.Index_Name

And User_Indexes.Table_Name = 'T_HP_PRODUCT';

直接删除的方法多半行不通

--------------------------------------------------------------------

-----------------------------------------------------------------

<2>我先将表T_HP_PRODUCT的建表结构提出来，建立好表以及表上的索引和

约束，然后直接将需要的数据插入此表，并准备用rename的方式移动此表

下面的是要插入的数据行数，此处建立的是表T_HP_PRODUCT_1

Select Count(*) From T_HP_PRODUCT Where

SALE_DATE>= to_date('2011-01-01 00:00:00','yyyy/MM/DD/ HH24:MI:SS')

首先让T_HP_PRODUCT_1不记日志

Alter Table T_HP_PRODUCT_1 nologging

然后通过直接路径插入，我在晚上凌晨运行此条SQL

Insert /*+append*/ Into t_Hp_Product_1

Select * From t_Hp_Product

Where Sale_Date >=To_Date('2011-01-01 00:00:00', 'yyyy/MM/DD/ HH24:MI:SS')

第二天上班报出此错：

ora-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

可以看见，是undo表空间UNDOTBS2被插满了，但是这种情况数据库并没有挂起，

插入的操作被自动中断了然后回滚了。表空间满了是不是不会引起数据库挂起

只要当磁盘上面的空间满了之后数据库才会挂起。

这里插入失败应该是上面的索引还会写undo,因为直接插入应该是不写索引的

我现在将表的索引全部删除，然后使外键失效

---------------------------------------------------------------

-----------------------------------------------------------------

<3>用create Table As Select * From 的方式来建表，这种情况下出来的表

是没有索引和约束的，下的这种情况运行起来要大大快于上面的情况，那么

是不是可以把上面慢的情况完全定位到索引和外键约束身上去了呢？

--下面命令用时6分钟半钟，这个涨I/O(应该有50142652)

Create Table t_Hp_Product_2 As Select * From t_Hp_Product

Where Sale_Date >= To_Date('2011-01-01 00:00:00', 'yyyy/MM/DD/ HH24:MI:SS')

要用operation_time 把差异数据补回去了

--这个不涨I/O，5分钟完成，这张表移动过去后吃了5G左右空间，现在还没添加索引

alter table t_Hp_Product_2 move tablespace USERLOB;

Select Count(*) From t_Hp_Product_2 --50142652

Select Count(*) From t_Hp_Product Where Sale_Date >= To_Date('2011-01-01 00:00:00', 'yyyy/MM/DD/ HH24:MI:SS') --50142730

--49591190

Select Max(operation_time) From t_Hp_Product

---------------------------------------

在创建完约束和索引后，添加索引的过程仍然消耗i/o，一个索引5分钟左右。

要用operation_time 把差异数据补回去了

Select Max(operation_time) From t_Hp_Product

Insert Into t_Hp_Product_2 Select * From t_Hp_Product Where operation_time> To_Date('2012-3-26 17:29:06', 'yyyy/MM/DD/ HH24:MI:SS')

Rename t_Hp_Product To t_Hp_Product_bakk;

Rename t_Hp_Product_2 To t_Hp_Product;

exec dbms_stats.gather_index_stats('gc','PK_T_HP_PRODUCT');

select PRODUCT_ID From t_Hp_Product_2

Minus

select PRODUCT_ID From t_Hp_Product

Delete From t_Hp_Product_2 Where product_id In (

'462473047',

'462473048',

........

'513828813'

)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10678398/viewspace-720574/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10678398/viewspace-720574/