原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/08/23/optimizer-tricks-1/
译文如下（原文附后）：
在优化器开始优化算法之前，我有许多（影响）优化器用来做SQL转换的小技巧的例子。最近，有关ODC的一个专题引导我发布此文章。值得注意的是，当你将这些技巧作为可能的背景知识时，从执行计划中解释和排除问题会变得更加容易。
我将这篇标记了“＃1” 标签，是因为将来我可能会再发表更多的示例，然后我将对它们进行分类-但我此刻不会做出任何承诺。
下面是表的定义，以及在表的索引上使用HINT的查询语句。
rem
rem Script: optimizer_tricks_01.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2019
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 11.2.0.4
rem
create table t1 (
v1 varchar2(10),
v2 varchar2(10),
v3 varchar2(10),
padding varchar2(100)
);
create index t1_i1 on t1(v1, v2, v3);
explain plan for
select
/*+ index(t1 (v1, v2, v3)) */
padding
from
t1
where
v1 = 'ABC'
and nvl(v3,'ORA$BASE') = 'SET2'
;
select * from table(dbms_xplan.display);
这个查询使用了索引的第一列和第三列，但是第三列包含在函数nvl()中。由于HINT的作用，优化器将生成采用索引范围扫描的执行计划。但是问题是，通过执行计划的谓词信息，ORACLE告诉我们采用的两个谓词是什么：
Plan hash value: 3320414027
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 66 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"='ABC')
filter(NVL("V3",'ORA$BASE')='SET2')
通过测试可知nvl()函数过滤是在索引范围扫描期间执行的（我记得在ORACLE的较老版本中会延迟谓词的过滤操作，直到它访问了表本身时才会）。这意味着ORACLE将对索引的v1 = ‘ABC’部分做范围扫描，再根据函数nvl() 谓词过滤找到的每一个索引条目。
但是，如果我们将v3列修改为NOT NULL属性，会发生什么？
(“alter table t1 modify v3 not null;”)，如下是新的执行计划：
Plan hash value: 3320414027
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 66 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"='ABC' AND "V3"='SET2')
filter("V3"='SET2')
优化器通过列的NOT NULL状态决定消除列上的函数nvl（）操作，并且用简单的列值比较替换了原来的函数谓词。此时，v3谓词现在可以使用索引跳扫或迭代的方法来减少检查索引条目的数量。但是ORACLE仍然必须在他所扫描过的索引条目上执行谓词过滤，因此该谓词仍然显示为过滤谓词。
顺便一提，你可能注意到Plan hash value值不会随着谓词使用的更改而更改。即使谓词使用的更改可能会对性能产生巨大的影响。（如脚本顶部的注释显示，我已经分别在11.2.0.4版本- 前述ODC中使用的版本和19.3.0.0上运行了上述脚本模型，两个版本中的表现都是相同的，并且Plan hash value值不会随版本的变化而变化）
脚注：
我之所以决定发布此文章是由于在ODC论坛上的原始报告中表述了以下矛盾的信息，索引的定义和优化器对该索引的使用，如执行计划中的谓词部分所示：
Index column name Column position
---------------------- ----------------
FLEX_VALUE_SET_ID 1
PARENT_FLEX_VALUE 2
RANGE_ATTRIBUTE 3
CHILD_FLEX_VALUE_LOW 4
CHILD_FLEX_VALUE_HIGH 5
ZD_EDITION_NAME 6
---------------------------------------------------------------------------
|* 17 | INDEX RANGE SCAN | FND_FLEX_VALUE_NORM_HIER_U1 |
---------------------------------------------------------------------------
17 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' ..... lots more bits of filter predicate.
由于表达式nvl(zd_edition_name, ‘ORABASE’) = ‘SET2’ 出现在了access和filter谓词部分，它（nvl(zd_edition_name, ‘ORABASE’)）肯定是索引中的一列。所以，要么这不是正在使用的索引的定义。要么某种程度上，对于运行时（处理）表示nvl（zd_edition_name，‘ORA $ BASE’）（的操作）时，有一个技巧可以使zd_edition_name列做为索引中的列名出现。（如果有的话，我想知道它是什么-基于版本的重新定义和使用虚拟列的技巧会浮现在我的脑海中，但当有更简单的解释时，我避免考虑过于复杂的解释）
原文内容：
I’ve got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It’s worth taking note of these tricks when you spot one as a background knowledge of what’s possible makes it much easier to interpret and trouble-shoot from execution plans. I’ve labelled this one “#1” since I may publish a few more examples in the future, and then I’ll have to catalogue them – but I’m not making any promises about that.
Here’s a table definition, and a query that’s hinted to use an index on that table.
rem
rem Script: optimizer_tricks_01.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2019
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 11.2.0.4
rem
create table t1 (
v1 varchar2(10),
v2 varchar2(10),
v3 varchar2(10),
padding varchar2(100)
);
create index t1_i1 on t1(v1, v2, v3);
explain plan for
select
/*+ index(t1 (v1, v2, v3)) */
padding
from
t1
where
v1 = ‘ABC’
and nvl(v3,‘ORA$BASE’) = ‘SET2’
;
select * from table(dbms_xplan.display);
The query uses the first and third columns of the index, but wraps the 3rd column in an nvl() function. Because of the hint the optimizer will generate a plan with an index range scan, but the question is – what will the Predicate Information tell us about Oracle’s use of my two predicates:
Plan hash value: 3320414027
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 66 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"='ABC')
filter(NVL("V3",'ORA$BASE')='SET2')
The nvl() test is used during the index range scan (from memory I think much older versions of Oracle would have postponed the predicate test until they had accessed the table itself). This means Oracle will do a range scan over the whole section of the index where v1 = ‘ABC’, testing every index entry it finds against the nvl() predicate.
But what happens if we modify column v3 to be NOT NULL? (“alter table t1 modify v3 not null;”) Here’s the new plan:
Plan hash value: 3320414027
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 66 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"='ABC' AND "V3"='SET2')
filter("V3"='SET2')
The optimizer will decide that with the NOT NULL status of the column the nvl() function can be eliminated and the predicate can be replaced with a simple column comparison. At this point the v3 predicate can now be used to reduce the number of index entries that need to be examined by using a type of skip-scan/iterator approach, but Oracle still has to test the predciate against the index entries it walks through – so the predicate still appears as a filter predicate as well.
You might notice, by the way, that the Plan hash value does not change as the predicate use changes – even though the change in use of predicates could make a huge difference to the performance. (As indicated in the comments at the top of the script, I’ve run this model against 11.2.0.4 – which is the version used in the ODC thread – and 19.3.0.0: the behaviour is the same in both versions, and the Plan hash value doesn’t change from version to version.)
Footnote
The reason why I decided to publish this note is that the original thread on the ODC forums reported the Following contradictory details – an index definition and the optimizer’s use of that index as shown in the predicate section of the plan:
Index column name Column position
---------------------- ----------------
FLEX_VALUE_SET_ID 1
PARENT_FLEX_VALUE 2
RANGE_ATTRIBUTE 3
CHILD_FLEX_VALUE_LOW 4
CHILD_FLEX_VALUE_HIGH 5
ZD_EDITION_NAME 6
---------------------------------------------------------------------------
|* 17 | INDEX RANGE SCAN | FND_FLEX_VALUE_NORM_HIER_U1 |
---------------------------------------------------------------------------
17 - access("FLEX_VALUE_SET_ID"=:B1 AND NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2')
filter((NVL("ZD_EDITION_NAME",'ORA$BASE')='SET2' ..... lots more bits of filter predicate.
Since the expression nvl(zd_edition_name, ‘ORABASE’) = ‘SET2’ appears as an access predicate and a filter predicate it must surely be a column in the index. So either this isn’t the definition of the index being used or, somehow, there’s a trick that allows zd_edition_name to appear as a column name in the index when it really means nvl(zd_edition_name,’ORABASE’) at run-time. (And if there is I want to know what it is – edition-based redefinition and tricks with virtual columns spring to mind, but I avoid thinking about complicated explanations when a simpler one might be available.)
