项目场景
项目现场的某个kettle模型执行非常缓慢，原因在于某个SQL执行效率非常的低。甲方得知此事要求公司赶紧优化，负责该模块的同事对SQL优化并不熟悉。所以作为一个立志成为优秀DBA的ETL工程师，我自告奋勇：不是DBA，就不能搞SQL优化了吗？
查询效率慢的SQL格式如下，因为涉及到项目隐私，所以对表名、字段名做了匿名化处理。
SELECT
INFO.ID,
INFO.NAME,
INFO.AGE,
INFO.SEX,
MP.ADDR,
MP.PHONE
FROM INFORMATION INFO
LEFT JOIN MP ON MP.ID = INFO.ID
WHERE INFO.ID = 1234567很简单的一个SQL。每次查询都是按照 INFO.ID = xxx 这个条件进行查询，每次只会返回1行数据。且关联字段 MP.ID 与 INFO.ID 都有索引，没有大字段。但每次执行查询，大概都要耗时2s左右；不要觉得这个耗时很短，因为有大量的数据需要查询出来进行同步，这个速度就不可被接受了。
两张表的数据量如下
SELECT COUNT(*) FROM INFORMATION --3667874
SELECT COUNT(*) FROM MP --2125263问题分析
以下是上述SQL格式的执行计划，是项目上真实SQL的执行计划。我已经将对此次优化的无关执行计划信息删除，只保留了执行计划和谓词信息；且将涉及项目隐私部分的表名字段名改写。
Plan hash value: 3295416379
----------------------------服务器托管---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29513 (100)| | 1 |00:00:01.20 | 108K|
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 115 | 29513 (1)| 00:00:02 | 1 |00:00:01.20 | 108K|
| 2 | TABLE ACCESS BY INDEX ROWID| INFORMATION | 1 | 1 | 98 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX UNIQUE SCAN | UK_20230901211918_5341 | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | TABLE ACCESS FULL | MP | 1 | 1 | 17 | 29510 (1)| 00:00:02 | 1 |00:00:01.20 | 108K|
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"=1234567)
4 - filter((TO_NUMBER("MP"."ID")=1234567 AND "A"."ID"=TO_NUMBER("MP"."ID")))
通过执行计划我们可以分析出：
- 连接方式 （执行计划：Id=1）
MP表和INFO表的连接方式是 NESTED LOOPS OUTER；因为此查询返回数据量少，每次执行只会返回1行数据，所以走嵌套循环连接是正确的。
- 访问路径（执行计划：Id=2）
INFO.ID=1234567 这个谓词过滤是走了索引ROWID扫描的；返回数据量少，走索引，也是正确的。
- 访问路径（执行计划：Id=4）
对MP表的访问路径是TABLE ACCESS FULL（全表扫描）。这个就有问题了，MP表有上百万条数据，走全表扫描肯定是低效的、错误的。MP表上的ID字段是有索引的，为什么没有走索引呢？我们继续往下看。
- 谓词信息
从谓词信息我们可以看到MP表在进行谓词过滤时，将MP表的ID字段从varchar类型的值通过to_number()转换成了number类型；因为使用了to_number()函数，索引索引失效。此转换属于是Oracle在比对不同数据类型的字段或者表达式时，自动发生的隐式转换；隐式转换的目的肯定是好的，但是在此处对SQL查询效率影响可太大了。
那么对于这个SQL优化而言，需要做的其实就是消除隐式转换带来的影响。那怎么消除嘞？大家继续看下面的SQL改写。
我不晓得会不会有朋友疑问，为什么发生隐式转换的是MP.ID 而不是 INFOMATION.ID。
我在这里多解释一句：
可以从执行计划中看出来，
距离NESTED LOOPS OUTER这个连接方式关键字下方最近的一张表就是驱动表。
这也就意味着，驱动表INFOMATION传一个number类型的数据给MP表，那么MP表必须对自己的字段进行转换才可以进行等值匹配。
SQL改写
这是改写后的SQL，现在查询只需要0.05左右，速度提高了几十倍。完全可以满足ETL要求了。
SELECT
INFO2.ID,
INFO2.NAME,
INFO2.AGE,
INFO2.SEX,
MP.ADDR,
MP.PHONE
FROM (SELECT CAST(INFO.ID AS VARCHAR2(10) AS ID,INFO.NAME,INFO.AGE,INFO.SEX FROM INFORMATION INFO WHERE INFO.ID = 1234567) INFO2
LEFT JOIN MP ON MP.ID = INFO2.ID该SQL的执行计划如下，同样的，为了隐私表名和字段名我做了匿名化处理。
Plan hash value: 3589640507
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 9 (100)| | 1 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 119 | 5 (0)| 00:00:01 | 1 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| INFORMATION | 1 | 1 | 98 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX UNIQUE SCAN | UK_20230901211918_5341 | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| MP | 1 | 1 | 21 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 5 | INDEX UNIQUE SCAN | UK_20230901210612_192177 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------服务器托管-----------------------
3 - access("INFO"."ID"=1234567)
5 - access("MP"."PID"=CAST("INFO"."ID" AS VARCHAR2(20)))服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
