删除大表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/