oracle大数据表(千万级)修改,删除优化技巧【转】

时间:2021-11-05 14:32:17

oracle大数据表(千万级)修改,删除优化技巧
运行下面的SQL
delete  from idwsq11.SH30_PRCSS_EXCTN_LOG e
where deriv_prcss_time_stamp < to_date('2010-JAN-01', 'YYYY-MON-DD');
省略其他信息。。。。。
为啥老外自己不做呢? 其实冤枉他了,他删除了的 不过等了半小时还没完,不耐烦了,然后kill了给我删除。
explain plan for delete from idwsq11.SH30_PRCSS_EXCTN_LOG e
where deriv_prcss_time_stamp < to_date('2010-JAN-01', 'YYYY-MON-DD');
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1015128491
-----------------------------------------------------------------------------------------------
| Id  | Operation         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                           |   203K| 5161K|   432   (3)| 00:00:05 |
|   1 |  DELETE           | SH30_PRCSS_EXCTN_LOG      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| SH30_PRCSS_EXCTN_LOG_IDX6 |   203K|  5161K|   432   (3)| 00:00:05 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DERIV_PRCSS_TIME_STAMP"<TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
15 rows selected.
SQL> select count(*) from idwsq11.SH30_PRCSS_EXCTN_LOG;
  COUNT(*)
----------
    342124
Elapsed: 00:00:02.87
SQL> select count(*) from idwsq11.SH30_PRCSS_EXCTN_LOG
  2  where deriv_prcss_time_stamp < to_date('2010-JAN-01', 'YYYY-MON-DD');
  COUNT(*)
----------
    192332
要删除192332条数据,表一共有342124条数据,执行计划居然走索引,很明显表统计信息过期了,执行计划有问题。
这也不难解释为什么老外删了半天没动静
select ' alter index ' ||  index_owner || '.' || index_name  ||' unusable ;'name from
dba_ind_columns where table_owner=upper('idwsq13') and
table_name=upper('SH30_PRCSS_EXCTN_LOG');
alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX3 unusable ;
alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX4 unusable ;
alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX5 unusable ;
alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX6 unusable ;
alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX2 unusable ;
SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX3 unusable ;
Index altered. 
Elapsed: 00:00:00.93
SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX4 unusable ; 
Index altered. 
Elapsed: 00:00:01.95

SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX5 unusable ; 
Index altered. 
Elapsed: 00:00:00.95
SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX6 unusable ; 
Index altered. 
Elapsed: 00:00:00.93
SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX2 unusable ;
Index altered. 
Elapsed: 00:00:02.03 
SQL> delete from idwsq11.SH30_PRCSS_EXCTN_LOG e
  2  where deriv_prcss_time_stamp < to_date('2010-JAN-01', 'YYYY-MON-DD'); 
192332 rows deleted. 
Elapsed: 00:00:07.04
SQL> commit; 
Commit complete. 
Elapsed: 00:00:00.93
SQL> alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX3 rebuild online nologging parallel ; 
Index altered. 
Elapsed: 00:02:52.76
SQL>  alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX4 rebuild online nologging parallel ;
 Index altered. 
Elapsed: 00:00:05.59
SQL>  alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX5 rebuild online nologging parallel ;
 Index altered.
 Elapsed: 00:00:04.35
SQL>  alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX6 rebuild online nologging parallel ;
Index altered.
Elapsed: 00:00:04.03
SQL>  alter index IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX2 rebuild online nologging parallel ;
Index altered.
Elapsed: 00:00:04.86
 现在多快啊,不到2分钟全部搞定。
总结:如果要对表进行大规模delete操作,大规模update,先看看执行计划,评估一下走索引好还是走全表扫描好(全表扫描还可以利用并行特征),如果表上面有索引,那么先unusable索引,然后rebuild 索引。如果是删除操作,那么还要 alter table move。降低高水位,然后再 rebuild index。
如果要对表进行大规模批量加载 那么先 unusable index ,然后 append parallel nologging ,最后rebuild index
批量加载示例:
insert into ADWGU_DICFR.DICFR_SALES_FCT select * from adwgq_dicfr.DICFR_SALES_FCT@adw1d e;
今天我就做了个 dblink加载1亿1千万条的记录,花了1小时10分钟。
注意parallel 根据CPU核数设置,如果你CPU有8个核,那么你可以设置parallel 8

delete FROM LYH_TEMP LT;

http://www.51xit.com/www/2010-12-05/5447.html