一个SQL的简单分析与处理

最近在做利用透明网关将SQLServer数据迁移到Oracle端。

结果下面的insert语句很慢，差不多要插入450w的数据，结果需要几十分钟。

insert /*+ append */ into topbox_unitscore(id,icnum,unit_id,testdate,score,topboxcode)
select icnum,unit_id,testdate,score,topboxcode
from (select distinct trim("icnum") as icnum,max(u."oracleid") over(partition by hu."icnum",u."unitid_old",hu."datetime") as unit_id,
          "datetime" as testdate,"result" as score,"topboxid" as topboxcode
    from jiangsu_hunit hu
    inner join jiangsu_unit u on hu."unitid"=u."unitid_old"  
    inner join jiangsu_class2008w h on hu."icnum"=h."icnum");

Execution Plan
----------------------------------------------------------
Plan hash value: 4010843544
---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |  9412K|   430M|       |   258K  (2)| 00:51:41 |
|   1 |  VIEW                     |               |  9412K|   430M|       |   258K  (2)| 00:51:41 |
|   2 |   HASH UNIQUE             |               |  9412K|   484M|  1158M|   258K  (2)| 00:51:41 |
|   3 |    WINDOW SORT            |               |  9412K|   484M|  1158M|   258K  (2)| 00:51:41 |
|*  4 |     HASH JOIN             |               |  9412K|   484M|       |  6926   (5)| 00:01:24 |
|   5 |      TABLE ACCESS FULL    | JIANGSU_UNIT  |   378 |  3780 |       |     9   (0)| 00:00:01 |
|*  6 |      HASH JOIN            |               |  4700K|   197M|       |  6807   (4)| 00:01:22 |
|   7 |       INDEX FAST FULL SCAN| IND_ICNUM     | 32208 |   283K|       |    23   (5)| 00:00:01 |
|   8 |       TABLE ACCESS FULL   | JIANGSU_HUNIT |  4700K|   156M|       |  6728   (3)| 00:01:21 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("HU"."unitid"="U"."unitid_old")
   6 - access("HU"."icnum"="H"."icnum")

Statistics
----------------------------------------------------------
       1038  recursive calls
         29  db block gets
      29930  consistent gets
     203269  physical reads
          0  redo size
  176690023  bytes sent via SQL*Net to client
    3264884  bytes received via SQL*Net from client
     296767  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
    4451476  rows processed

于是思考要对SQL和数据库做些处理：

看见执行计划，发现走的是全表扫描，于是给where条件的相应字段加入了index，但再次执行，效率依然低下。而且undo表空间32G都被沾满，发现原来是在inesrt时，oracle维护index花费了大量的时间和空间。

1.将目标表的index先删除，再重建。

2.将需要联接的SQLServer端的表，通过CTAS在Oracle端创建临时表

这样处理后，发现insert需要不到1分钟就可以完成，基本满足要求。
结论：数据量比较大的表如果建立索引，不但需要相应的磁盘空间，还对于大规模的DML操作的效率有很大影响。

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

转载于:http://blog.itpub.net/10710960/viewspace-610021/