oracle partial join eval

在oracle sql优化中，往往只关注逻辑读的消耗，而忽略了join消耗的时间，特别是高频值进行join关联出大量rows的场景。往往join消耗的时间会比扫描对象消耗的逻辑读物理读更耗时。

例如下面这个sql(11g)：

可以看到该sql在11g中，逻辑读仅仅2489，但是却消耗了16s时间，这就是因为高频值的join消耗了大量的时间。

oracle在12c版本中，对于该sql进行了逻辑改写的查询转换，partial join eval(PJE)，属于一种启发式查询转换，也就是基于规则的查询转换，当满足规则时就会进行转换改写。

PJE的规则满足条件如下：

• 多表连接
• 多表连接查询sql的select list的字段全部来自同一个表
• select list需要带有distinct去重操作

PJE的改写逻辑：

会把JOIN转换成SEMI JOIN，巧妙的规避了高频值JOIN返回大量rows，类似JOIN等价改为子查询。

PJE的参数控制和hint：

由隐藏参数_optimizer_partial_join_eval控制，默认为true。hint为PARTIAL_JOIN/NO_PARTIAL_JOIN。

23ai中重新跑一次测试的sql：

由于SEMI JOIN不需要返回所有JOIN上的行，只需要检查是否匹配上，所以JOIN消耗的时间会大幅度降低。

10053：

outline会出现PARTIAL_JOIN(@”SEL$1″ “T1″@”SEL$1”)。

该特性在国产数据库中，几乎没有厂商支持。