川川找我优化SQL，逻辑读达到398,000，安排一下。
SQL和执行计划：
SELECT t1.*, t3.bed_number, t3.patient_name, t4.name
FROM odw_checkrecipe_result t1
left join lenovo_his.ip_patient_regis t3
on t3.ip_number = t1.visit_id
left join hd_advice t4
on t4.advice_id = t1.order_id
WHERE NOT EXISTS
(select *
from odw_checkrecipe_result t2
WHERE t1.DETAIL_ID = t2.DETAIL_ID
AND t1.AUDIT_TIME < t2.AUDIT_TIME)
and t1.detail_id in (select advice_main_id
from hd_advice
where create_id = '70013'
and status = 1
and advice_type in (11, 12, 13)
group by advice_main_id);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9mmh1q6bxznxj, child number 0
-------------------------------------
SELECT t1.*, t3.bed_number, t3.patient_name, t4.name FROM
odw_checkrecipe_result t1 left join lenovo_his.ip_patient_regis t3
on t3.ip_number = t1.visit_id left join hd_advice t4 on
t4.advice_id = t1.order_id WHERE NOT EXISTS (select * from
odw_checkrecipe_result t2 WHERE t1.DETAIL_ID = t2.DETAIL_ID
AND t1.AUDIT_TIME < t2.AUDIT_TIME) and t1.detail_id in
(select advice_main_id from hd_advice
where create_id = '70013'
and status = 1 and advice_type in (11, 12,
13) group by advice_main_id)
Plan hash value: 3849450906
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.85 | 398K| | | |
| 1 | HASH GROUP BY | | 1 | 93 | 0 |00:00:00.85 | 398K| 760K| 760K| |
| 2 | NESTED LOOPS OUTER | | 1 | 93 | 0 |00:00:00.85 | 398K| | | |
| 3 | NESTED LOOPS OUTER | | 1 | 93 | 0 |00:00:00.85 | 398K| | | |
| 4 | NESTED LOOPS | | 1 | 93 | 0 |00:00:00.85 | 398K| | | |
|* 5 | HASH JOIN RIGHT ANTI | | 1 | 1372 | 135K|00:00:00.22 | 3268 | 9702K| 3788K| 8770K (0)|
| 6 | TABLE ACCESS FULL | ODW_CHECKRECIPE_RESULT | 1 | 137K| 141K|00:00:00.02 | 1634 | | | |
| 7 | TABLE ACCESS FULL | ODW_CHECKRECIPE_RESULT | 1 | 137K| 141K|00:00:00.05 | 1634 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| HD_ADVICE | 135K| 1 | 0 |00:00:00.61 | 394K| | | |
|* 9 | INDEX RANGE SCAN | IDX_HD_ADVICE_MAIN_ID | 135K| 1 | 180K|00:00:00.31 | 260K| | | |
| 10 | TABLE ACCESS BY INDEX ROWID | IP_PATIENT_REGIS | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | IDX_IP_PATIENT_REGIS_NUM | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | HD_ADVICE | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 13 | INDEX UNIQUE SCAN | HD_ADVICE_PK | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."DETAIL_ID"="T2"."DETAIL_ID")
filter("T1"."AUDIT_TIME"<"T2"."AUDIT_TIME")
8 - filter(("STATUS"=1 AND "CREATE_ID"='70013' AND (TO_NUMBER("ADVICE_TYPE")=11 OR TO_NUMBER("ADVICE_TYPE")=12 OR
TO_NUMBER("ADVICE_TYPE")=13)))
9 - access("ADVICE_MAIN_ID"=TO_NUMBER("T1"."DETAIL_ID"))
11 - access("T3"."IP_NUMBER"="T1"."VISIT_ID")
13 - access("T4"."ADVICE_ID"=TO_NUMBER("T1"."ORDER_ID"))
45 rows selected.
这条SQL比较简单，我其实就喵了一眼执行计划就看到问题，hd_advice表有700W行数据，通过谓词信息了解到 第9行 access("ADVICE_MAIN_ID"=TO_NUMBER("T1"."DETAIL_ID")) t1 表和 hd_advice 表关联访问是用到索引的，
但是执行到第8行 filter(("STATUS"=1 AND "CREATE_ID"='70013' AND (TO_NUMBER("ADVICE_TYPE")=11 OR TO_NUMBER("ADVICE_TYPE")=12 OR TO_NUMBER("ADVICE_TYPE")=13))) 这段条件过滤时候又进行了回表扫描，
说明现有的索引不合适这个谓词过滤条件，索引块获取不到相关的记录，需要再次回表扫描，产生大量的逻辑读。
创建联合索引进行优化规避回表扫描：
create index idx_hd_advice_1_2_3_4 on hd_advice(create_id,status,advice_type,advice_main_id)；
创建完索引后逻辑读应该就能降下来了，川川也提供了验证，buffer 降到3，这条SQL到此已经完成优化。
最后总结：
ORACLE调优慢SQL的步骤如下：
1、先尝试创建索引或者修改数据库参数看看能否达到调优目的。
2、上面手段无效果尝试通过HINT干预执行计划进行调优，如果有效果使用sqlprofile绑定新的执行计划。
3、如果上述手段均无办法调优SQL，只能通过SQL等价改写、升级硬件设备、业务代码重构等方式进行优化。
漫威作品中为什么蜘蛛侠人气那么高?(蜘蛛侠为什么人气最高)官方教程：小米云同步开启后，如何只删除本地照片