不解的现象：批量更新某字段后，查询的where中包含此字段则非常缓慢

Question

背景：km_cmdoc_main这张表，有11w的数据，3天前，我执行了下面两条语句,更新了所有数据：update km_cmdoc_main set fd_delete_type = '02'; 
update km_cmdoc_main set fd_enter_system = '02';
现象：select *
  from (select m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
        union all
        select mau.*
          from km_cmdoc_auth au
         inner join km_cmdoc_main mau
            on au.doc_creator_company_id = mau.doc_creator_company_id
         where au.fd_auth_id = '139dc23f04c6385c52ab1724864a9a121b')
 where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'1.执行上面的sql语句，反复试了许多次，十几分钟都执行不完。请问是什么原因？
2.修改上面的sql，where语句换成这样：where doc_status <> '10，其他不变，则非常快
3.如果不用union，只单表查询，则非常快：
select *
  from km_cmdoc_main
 where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'

Answer 1

SQL优化问题一般要贴出执行计划

Answer 2

下面是执行计划，但是再慢也不至于十几分钟也没完成吧，感觉很奇怪：SELECT STATEMENT, GOAL = ALL_ROWS 24 2 19274
 VIEW SCOTT 24 2 19274
  UNION-ALL
   VIEW SYS VM_NWVW_1 17 1 9637
    HASH UNIQUE 17 1 347
     NESTED LOOPS 16 2377 824819
      TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 1 271
      TABLE ACCESS BY INDEX ROWID SCOTT KM_CMDOC_REVIEW_HANDLER 13 8053 612028
       INDEX RANGE SCAN SCOTT INDEX_FD_COMPANY_ID 0 32213
   HASH JOIN 7 1 347
    TABLE ACCESS FULL SCOTT KM_CMDOC_AUTH 3 1 76
    TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 1 271

Answer 3

如果把where语句换成 where doc_status <> '10'的话，执行计划也变了：SELECT STATEMENT, GOAL = ALL_ROWS 824 18408 177397896
 VIEW SCOTT 824 18408 177397896
  UNION-ALL
   HASH JOIN 818 18407 5687763
    TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 16 4336
    VIEW SCOTT 814 32213 1224094
     HASH UNIQUE 814 32213 2448188
      TABLE ACCESS FULL SCOTT KM_CMDOC_REVIEW_HANDLER 239 32213 2448188
   NESTED LOOPS
    NESTED LOOPS 6 1 347
     TABLE ACCESS FULL SCOTT KM_CMDOC_AUTH 3 1 76
     INDEX RANGE SCAN SCOTT IFK3F635C73D08396D 0 28
    TABLE ACCESS BY INDEX ROWID SCOTT KM_CMDOC_MAIN 3 16 4336

Answer 4

从表面看，应该是下面这段影响较大，尝试增加HINTS改变执行计划看看：
select /*+ index(mau IFK3F635C73D08396D)*/ mau.*
          from km_cmdoc_auth au
         inner join km_cmdoc_main mau
            on au.doc_creator_company_id = mau.doc_creator_company_id
         where au.fd_auth_id = '139dc23f04c6385c52ab1724864a9a121b'

Answer 5

谢谢指点，我试了，执行计划是变了，但还是20分钟没执行完，感觉已经超出我的理解范围了，我总觉得是因为我之前批量更新了这两个字段才造成了这个问题，因为1.没做更新前，查询时正常的 2.用其他字段做where条件不会有问题。执行计划：SELECT STATEMENT, GOAL = ALL_ROWS 23 2 19274
 VIEW SCOTT 23 2 19274
  UNION-ALL
   VIEW SYS VM_NWVW_1 17 1 9637
    HASH UNIQUE 17 1 347
     NESTED LOOPS 16 2377 824819
      TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 1 271
      TABLE ACCESS BY INDEX ROWID SCOTT KM_CMDOC_REVIEW_HANDLER 13 8053 612028
       INDEX RANGE SCAN SCOTT INDEX_FD_COMPANY_ID 0 32213
   NESTED LOOPS
    NESTED LOOPS 6 1 347
     TABLE ACCESS FULL SCOTT KM_CMDOC_AUTH 3 1 76
     INDEX RANGE SCAN SCOTT IFK3F635C73D08396D 0 28
    TABLE ACCESS BY INDEX ROWID SCOTT KM_CMDOC_MAIN 3 1 271

Answer 6

由于没有数据，无法做测试，只能你自己试试了，建议按如下顺序试试看
1、单独执行看看需要多久，select m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
         where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'
2、单独执行看看需要多久，select mau.*
          from km_cmdoc_auth au
         inner join km_cmdoc_main mau
            on au.doc_creator_company_id = mau.doc_creator_company_id
         where au.fd_auth_id = '139dc23f04c6385c52ab1724864a9a121b'
   and doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'3、把上面两个UNION ALL看看需要多久
select m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
         where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'
union all
select mau.*
          from km_cmdoc_auth au
         inner join km_cmdoc_main mau
            on au.doc_creator_company_id = mau.doc_creator_company_id
         where au.fd_auth_id = '139dc23f04c6385c52ab1724864a9a121b'
   and doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'

Answer 7

实际上不用union都很慢，几分钟都执行不完。把where里面的fd_enter_system 和fd_delete_type都去掉，则恢复正常；二者保留一个，都巨慢；where语句换成其他字段，正常。select m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
         where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'

Answer 8

1.where不含那两个字段，很快，sql：select m.*
  from km_cmdoc_main m
 inner join (select distinct fd_cmdoc_id
               from km_cmdoc_review_handler
              where fd_company_id = '139dc1399ef69959cd4e0a14395a3f8e') h
    on m.fd_id = h.fd_cmdoc_id
 where doc_status <> '10'执行计划：SELECT STATEMENT, GOAL = ALL_ROWS 818 18407 5687763
 HASH JOIN 818 18407 5687763
  TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 16 4336
  VIEW SCOTT 814 32213 1224094
   HASH UNIQUE 814 32213 2448188
    TABLE ACCESS FULL SCOTT KM_CMDOC_REVIEW_HANDLER 239 32213 24481882.where包含两个字段的其中一个，非常慢，sql：select m.*
  from km_cmdoc_main m
 inner join (select distinct fd_cmdoc_id
               from km_cmdoc_review_handler
              where fd_company_id = '139dc1399ef69959cd4e0a14395a3f8e') h
    on m.fd_id = h.fd_cmdoc_id
 where doc_status <> '10'
   and fd_enter_system <> '02'
执行计划：SELECT STATEMENT, GOAL = ALL_ROWS 122 32 308384
 VIEW SYS VM_NWVW_1 122 32 308384
  HASH UNIQUE 122 32 11104
   NESTED LOOPS 120 30679 10645613
    TABLE ACCESS FULL SCOTT KM_CMDOC_MAIN 3 9 2439
    TABLE ACCESS BY INDEX ROWID SCOTT KM_CMDOC_REVIEW_HANDLER 13 3579 272004
     INDEX RANGE SCAN SCOTT INDEX_FD_COMPANY_ID 0 32213

Answer 9

既然之前执行计划相对优越，后面又批量修改了数据，可尝试先重新采集数据,再看看执行计划和重新执行看看。exec dbms_stats.gather_table_stats(用户名,表名,cascade => true);

Answer 10

可以确定的是只需要优化这段SQL就行
select m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
         where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'从执行计划来看，这个语句之所以慢是因为做了NESTED LOOPS之后再做的HASH UNIQUE
HASH UNIQUE            122    32    11104
   NESTED LOOPS            120    30679    10645613
    TABLE ACCESS FULL    SCOTT    KM_CMDOC_MAIN    3    9    2439
    TABLE ACCESS BY INDEX ROWID    SCOTT    KM_CMDOC_REVIEW_HANDLER    13    3579    272004
     INDEX RANGE SCAN    SCOTT    INDEX_FD_COMPANY_ID    0    32213   尝试通过提示改变执行计划看看是否可行
select /*+ use_hash(m,h) */ m.*
          from km_cmdoc_main m
         inner join (select distinct fd_cmdoc_id
                      from km_cmdoc_review_handler
                     where fd_company_id =
                           '139dc1399ef69959cd4e0a14395a3f8e') h
            on m.fd_id = h.fd_cmdoc_id
         where doc_status <> '10'
   and fd_enter_system <> '02'
   and fd_delete_type <> '02'