不解的现象：批量更新某字段后，查询的where中包含此字段则非常缓慢
背景：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'
------解决方案--------------------
从表面看，应该是下面这段影响较大，尝试增加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'
[解决办法]
由于没有数据，无法做测试，只能你自己试试了，建议按如下顺序试试看
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'
[解决办法]
既然之前执行计划相对优越，后面又批量修改了数据，可尝试先重新采集数据,再看看执行计划和重新执行看看。exec dbms_stats.gather_table_stats(用户名,表名,cascade => true);
[解决办法]
可以确定的是只需要优化这段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'