超级update请教优化方法！！！

yyp2009

UPDATE  OFR_CHILD_ASSET_N_HIST A
     SET A.END_DT = '20101210'
   WHERE A.ASSET_ROW_ID IN
         (SELECT TRIM(ASSET_ROW_ID) FROM OFR_CHILD_ASSET_N_HIST_PATCH)
     AND A.END_DT = '30001231'


                                                                       基数                   i/ocost
UPDATE STATEMENT, GOAL = ALL_ROWS                       1                    30181                        427363950
UPDATE                                                                                         OFR_CHILD_ASSET_N_HIST       
  HASH JOIN RIGHT SEMI                                    1                    30181                        427363950
   TABLE ACCESS FULL                                   124782                     546                        OFR_CHILD_ASSET_N_HIST_PATCH        48579914
   PARTITION RANGE ALL                                 229115                29127                        301363333
    TABLE ACCESS BY LOCAL INDEX ROWID                   229115                29127                  OFR_CHILD_ASSET_N_HIST        301363333
     INDEX RANGE SCAN                            229115                              5749                          IND_END_DT        86764159


历时1个半小时。

OFR_CHILD_ASSET_N_HIST按照start_dt分区
end_dt建分区索引
其他字段均无索引
OFR_CHILD_ASSET_N_HIST表物理大小56G
OFR_CHILD_ASSET_N_HIST_PATCH有155433条记录
OFR_CHILD_ASSET_N_HIST_PATCH的ASSET_ROW_ID上有索引


select count(*) from OFR_CHILD_ASSET_N_HIST A
      where A.END_DT = '30001231'

有40309375条记录。

bell6248

这2个表的关系是如何的？ 你subquery中为什么要TRIM(ASSET_ROW_ID) 呢？ 虽然OFR_CHILD_ASSET_N_HIST_PATCH的ASSET_ROW_ID上有索引， 但你用了trim就无法使用index了！

yyp2009

不！现在问题不是subquery中TRIM(ASSET_ROW_ID)上没走索引，我之前在TRIM(ASSET_ROW_ID)创建过函数索引，问题是
OFR_CHILD_ASSET_N_HIST此表太大即使分区了，仍然超级慢的，OFR_CHILD_ASSET_N_HIST此表很大是按照start_dt分区的，而update中是访问的end_dt字段，所以在end_dt上创建了分区索引。

问题就是大表按start_dt分区的，而update中是访问的end_dt字段，所以在end_dt上创建了分区索引，虽然分区内走了索引但是仍然很慢很慢！

请教一下！
是否可以拆成delete和insert操作？
如何拆？

hotdog04

关注

iori809

你这个最后update的记录数是多少？

jdw618

你先把语名改一下，看一下查询要多长时间

select  A.END_DT from  OFR_CHILD_ASSET_N_HIST A
WHERE A.ASSET_ROW_ID IN
         (SELECT TRIM(ASSET_ROW_ID) FROM OFR_CHILD_ASSET_N_HIST_PATCH)
     AND A.END_DT = '30001231')

yyp2009

SQL> select  A.END_DT from  SXMART.OFR_CHILD_ASSET_N_HIST A
  2  WHERE A.ASSET_ROW_ID IN
  3           (SELECT TRIM(ASSET_ROW_ID) FROM SXMART.OFR_CHILD_ASSET_N_HIST_PATCH)
  4       AND A.END_DT = '30001231';

已选择245067行。

已用时间:  02: 18: 07.94

执行计划
----------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |   229K|  6488K|       | 29754   (1)|     |       |
|*  1 |  HASH JOIN RIGHT SEMI               |                        |   229K|  6488K|  3344K| 29754   (1)|     |       |
|   2 |   INDEX FAST FULL SCAN              | IND_ASSET_ROW_ID       |   155K|  1517K|       |    82   (2)|     |       |
|   3 |   PARTITION RANGE ALL               |                        |   229K|  4251K|       | 29168   (1)|   1 |  2555 |
|   4 |    TABLE ACCESS BY LOCAL INDEX ROWID| OFR_CHILD_ASSET_N_HIST |   229K|  4251K|       | 29168   (1)|   1 |  2555 |
|*  5 |     INDEX RANGE SCAN                | IND_END_DT             |   229K|       |       |  5756   (1)|   1 |  2555 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ASSET_ROW_ID"=TRIM("ASSET_ROW_ID"))
   5 - access("A"."END_DT"='30001231')

Note
-----
   - 'PLAN_TABLE' is old version




统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     990022  consistent gets
     956380  physical reads
       5080  redo size
    4215568  bytes sent via SQL*Net to client
     180199  bytes received via SQL*Net from client
      16339  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     245067  rows processed

ycpengdong

END_DT 选择性怎么样啊?
还有END_DT的是LOCAL index,改成全局索引试试

iori809

我觉得你这个还是应该从那个trim函数入手，因为SQL基本也就这样了，就两个选择条件
如果能不用函数的话，开销会小很多

yyp2009

TRIM(ASSET_ROW_ID)上创建函数索引ind_ASSET_ROW_ID

执行计划中：
  2 |   INDEX FAST FULL SCAN              | IND_ASSET_ROW_ID