最近，在Oracle开发者社区论坛上出现一个关于MIN/MAX索引扫描的问题。贴子中提供的查询有一点怪异–你也许会说为什么会有人原样执行它。所以，我修改了它，以便可以让演示的一系列细节更怪异。
我会从一个简单的数据集开始，不必费心去收集统计信息，因为在创建我的脚本时，将自动完成。
rem
rem Script: min_max_cost_bug.sql
rem Author: Jonathan Lewis
rem Dated: Jul 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
create table t1
as
select *
from all_objects
where rownum <= 50000 -- > comment to avoid wordpress format issue
;
create index t1_i1 on t1(object_name);
以下几个简单的查询，将帮助我进一步捕获和展示内存中的执行计划：
set linesize 156
set pagesize 60
set trimspool on
set serveroutput off
alter session set statistics_level = all;
prompt =====================
prompt Baseline select max()
prompt =====================
select max(object_name) from t1;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt ============================
prompt select max() with dummy join
prompt ============================
select max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt =============================================
prompt select max() with dummy join and index() hint
prompt =============================================
select /*+ index(t1) */ max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt ============================================
prompt select max() with dummy join and inline view
prompt ============================================
select obj
from (
select max(object_name) obj
from t1
),
dual
where dummy is not null
/
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
prompt ====================================
prompt select max() with existence subquery
prompt ====================================
select max(object_name) from t1 where exists (select null from dual where dummy is not null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
prompt ============================================
prompt select max() with failing existence subquery
prompt ============================================
select max(object_name) from t1 where exists (select null from dual where dummy is null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
50000行和适当的索引，使得Oracle非常快速地找到最大值。我们期望优化器使用“index full scan (min/max)”操作，只访问索引最外侧的叶子块。实际上，并没有让我们失望，以下就是Baseline查询展现给我们的：
=====================
Baseline select max()
=====================
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
| 2 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
然而，当我们引入连接一个DUAL表（也许这不公平）时，发生非常不一样的事情–优化器忘记了关于MIN/MAX的最优方法，并且执行了在T1_I1索引上进行了索引快速全扫描，传递了全部50000行给父操作。
============================
select max() with dummy join
============================
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 (100)| 1 |00:00:00.02 | 360 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.02 | 360 |
| 2 | NESTED LOOPS | | 1 | 50000 | 50 (6)| 50000 |00:00:00.01 | 360 |
|* 3 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 4 | INDEX FAST FULL SCAN| T1_I1 | 1 | 50000 | 48 (7)| 50000 |00:00:00.01 | 357 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DUMMY" IS NOT NULL)
当然，我们可以尝试用索引范围扫描（或全扫描）提示，看看会发生什么。但结果甚至更让人惊奇了：Oracle接受了提示，使用了MIN/MAX的最优方法，但没有使用默认的路径，因为它已经忘掉如何正确估算其成本。
注意，ID为5的操作的成本值是354，而初始的MIN/MAX成本是3，也许是优化器认为我们需要最终访问全部50000个索引条目。但在运行时，Oracle正确使用了只需要访问一个索引条目的路径：
=============================================
select max() with dummy join and index() hint
=============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 356 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 50000 | 356 (2)| 1 |00:00:00.01 | 6 |
|* 3 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 4 | FIRST ROW | | 1 | 50000 | 354 (2)| 1 |00:00:00.01 | 3 |
| 5 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 50000 | 354 (2)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DUMMY" IS NOT NULL)
当然，我们可以确认到，对t1的访问和对dual的访问可能是不相关的，并希望优化器不要试图使用复杂视图合并（也许我们应该包含一个/+no_merge/的提示）返回到一个简单的连接。幸运的是，优化器没有尝试合并两个查询块，因此它正确地优化了max（object_name）查询块，从而为我们提供了最小/最大优化方法的好处。我在对dbms_xplan（）的调用中包含了’alias’的格式选项，这样我们就可以看到分别优化的两个查询块。
============================================
select max() with dummy join and inline view
============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 1 | 5 (0)| 1 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
| 4 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
| 5 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DUAL@SEL$1
3 - SEL$2 / from$_subquery$_001@SEL$1
4 - SEL$2
5 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DUMMY" IS NOT NULL)
有一条格言（或准则，或经验法则），如果查询的from子句中包含在选择列表中没有被引用的表，那么这些表应该（可能）出现在子查询中。当然，这个指导原则有时会被证明是一个非常糟糕的主意，有时它只是意味着优化器取消子查询并重新创建我们开始使用的连接，但是让我们尝试一下这个查询的方法。我再次加入了“alias”选项，这样您可以看到这个计划被优化为两个查询块，允许max（object_name）查询块找到min/max策略。
====================================
select max() with existence subquery
====================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
|* 2 | FILTER | | 1 | | | 1 |00:00:00.01 | 6 |
| 3 | FIRST ROW | | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$2 / DUAL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
5 - filter("DUMMY" IS NOT NULL)
上面的执行计划中有一个非常重要的细节。乍一看，优化器似乎有一个使用简单过滤子查询操作的计划，这意味着您可能会被愚弄，而将其解读为“对于操作3返回的每一行调用操作5”。事实并非如此。
因为子查询不是一个相关的子查询——这是一个我有时称之为“固定”或（有点含糊不清）“常量”子查询的例子，Oracle可以执行它一次，然后使用生成的行源来决定是否调用主查询。在这种情况下（如果您没有意识到该计划由两个独立的查询块组成），您会说Oracle首先调用了第二个子操作。
为了证明这一点，我设置了查询的最后一个变体——“failed subquery”版本，其中select from dual不返回行。检查执行计划每一行STARTS列显示的数值：
============================================
select max() with failing existence subquery
============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
|* 2 | FILTER | | 1 | | | 0 |00:00:00.01 | 3 |
| 3 | FIRST ROW | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 0 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$2 / DUAL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
5 - filter("DUMMY" IS NULL)
ID为2处的FILTER调用ID为5的操作–针对dual的查询–运行1次，但未返回行。ID为4的，针对t1_i1的MIN/MAX扫描并未运行。所以，应该认定在ID为4的操作之前调用了ID为5的操作。
最后
让我们回到问题——为什么有人会运行这样一个奇怪的查询。
也许答案是，这只是一个更复杂查询的一个部分的演示，而我们正在做的这些是表明：“如果某个记录存在于控制表中，那么会包含来自表X的一些信息”。
原文链接：https://jonathanlewis.wordpress.com/2020/07/13/min-max-costing/
原文内容如下：
Filed under: Oracle,Performance,subqueries,Tuning — Jonathan Lewis @ 1:07 pm BST Jul 13,2020
A question about the min/max index scan appeared on the Oracle Developer Community forum recently. The query supplied in the thread was a little odd – you might ask why anyone would run it as it stands – and I’ve modified it to make it even stranger to demonstrate a range of details.
I’ll start with a simple data set, not bothering to collect stats because that will be done automatically on create for my versions:
rem
rem Script: min_max_cost_bug.sql
rem Author: Jonathan Lewis
rem Dated: Jul 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem 12.2.0.1
rem
create table t1
as
select *
from all_objects
where rownum <= 50000 -- > comment to avoid wordpress format issue
;
create index t1_i1 on t1(object_name);
Now a few simple queries – for which I’ll capture and display the in-memory execution plans a little further on:
set linesize 156
set pagesize 60
set trimspool on
set serveroutput off
alter session set statistics_level = all;
prompt =====================
prompt Baseline select max()
prompt =====================
select max(object_name) from t1;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt ============================
prompt select max() with dummy join
prompt ============================
select max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt =============================================
prompt select max() with dummy join and index() hint
prompt =============================================
select /*+ index(t1) */ max(object_name) from t1, dual where dummy is not null;
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
prompt ============================================
prompt select max() with dummy join and inline view
prompt ============================================
select obj
from (
select max(object_name) obj
from t1
),
dual
where dummy is not null
/
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
prompt ====================================
prompt select max() with existence subquery
prompt ====================================
select max(object_name) from t1 where exists (select null from dual where dummy is not null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
prompt ============================================
prompt select max() with failing existence subquery
prompt ============================================
select max(object_name) from t1 where exists (select null from dual where dummy is null);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last alias'));
With 50,000 rows and the appropriate index to allow Oracle to find the maximum value very quickly we expect the optimizer to invoke the “index full scan (min/max)” operation, visiting only the extreme leaf block of the index – and, indeed, we are not disappointed, that’s exactly what the baseline query shows us:
=====================
Baseline select max()
=====================
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
| 2 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
However, when we introduce the (as yet unjustified) join to dual something very different happens – the optimizer forgets all about the min/max optimisation and does an index fast full scan of the t1_i1 index, passing all 50,000 rows up to the parent operation.
============================
select max() with dummy join
============================
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 (100)| 1 |00:00:00.02 | 360 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.02 | 360 |
| 2 | NESTED LOOPS | | 1 | 50000 | 50 (6)| 50000 |00:00:00.01 | 360 |
|* 3 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 4 | INDEX FAST FULL SCAN| T1_I1 | 1 | 50000 | 48 (7)| 50000 |00:00:00.01 | 357 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DUMMY" IS NOT NULL)
We could, of course, try hinting an index range (full) scan to see what happens – and the result is even more surprising: Oracle takes the hint, uses the min/max optimisation, and shows us that it didn’t take that path by default because it had “forgotten” how to cost it correctly.
Note the cost of 354 at operation 5 when the original min/max cost was 3, note also that the optimizer thinks we have to visit all 50,000 index entries even though, at run-time, Oracle correctly uses a path that visits only one index entry:
=============================================
select max() with dummy join and index() hint
=============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 356 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 50000 | 356 (2)| 1 |00:00:00.01 | 6 |
|* 3 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 4 | FIRST ROW | | 1 | 50000 | 354 (2)| 1 |00:00:00.01 | 3 |
| 5 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 50000 | 354 (2)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DUMMY" IS NOT NULL)
Of course we could recognise that the t1 access and the access to dual could be de-coupled – and hope that the optimizer doesn’t try to use complex view merging (maybe we should have included a /*+ no_merge */ hint) to fall back to a simple join. Fortunately the optimizer doesn’t try merging the two query blocks, so it optimises the max(object_name) query block correctly, giving us the benefit of the min/max optimisation. I’ve included the ‘alias’ format option in this call to dbms_xplan() so that we can see the two query blocks that are optimised separately.
============================================
select max() with dummy join and inline view
============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 1 | 5 (0)| 1 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
| 3 | VIEW | | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
| 4 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
| 5 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DUAL@SEL$1
3 - SEL$2 / from$_subquery$_001@SEL$1
4 - SEL$2
5 - SEL$2 / T1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DUMMY" IS NOT NULL)
There is a maxim (or guideline, or rule of thumb) that if the from clause of a query includes tables that don’t get referenced in the select list then those tables should (probably) appear in subqueries. Of course this guideline sometimes turns out to be a very bad idea, and sometimes it just means the optimizer unnests the subqueries and recreates the joins we started with, but let’s try the approach with this query. I’ve included the ‘alias’ option again so that you can see that this plan is optimised as two query blocks, allowing the max(object_name) query block to find the min/max strategy.
====================================
select max() with existence subquery
====================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 6 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 6 |
|* 2 | FILTER | | 1 | | | 1 |00:00:00.01 | 6 |
| 3 | FIRST ROW | | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 1 | 1 | 3 (0)| 1 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$2 / DUAL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
5 - filter("DUMMY" IS NOT NULL)
There’s a very important detail in the execution plan above. At first sight it looks like the optimizer has a plan using a simple filter subquery operation – which means you might be fooled into reading it as “for each row returned by operation 3 call operation 5”. This is not the case.
Because the subquery is not a correlated subquery – it’s an example that I sometimes call a “fixed” or (slightly ambiguously) “constant” subquery – Oracle can execute it once and use the resulting rowsource to decide whether or not to call the main query. It’s a case where (if you didn’t realise the plan consisted of two separate query blocks) you would say that Oracle was calling the second child first.
To prove this point I’ve set up one last variation of the query – the “failed subquery” version – where my select from dual returns no rows. Check the numbers of Starts shown for each line of the plan:
============================================
select max() with failing existence subquery
============================================
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 3 |
|* 2 | FILTER | | 1 | | | 0 |00:00:00.01 | 3 |
| 3 | FIRST ROW | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T1_I1 | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL | DUAL | 1 | 1 | 2 (0)| 0 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / T1@SEL$1
5 - SEL$2 / DUAL@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
5 - filter("DUMMY" IS NULL)
The filter at operation 3 calls operation 5 – the query against dual – which runs once returning no rows. The min/max scan of t1_i1 at operation 4 doesn’t run. Operation 5 was called before operation 4 was considered.
Finally
This brings us back to the question – why would anyone run a strange query like this.
Perhaps the answer is that it’s just a demonstration of one part of a more complex query and what we’re trying to do is say: “if a certain record exists in a control table then include some information from table X”.
This note tells us that if there’s a possibility of a min/max optimisation to find the data then we should avoid using a join, instead we should use a “fixed subquery” to check the control table, and maybe we’ll also have to write the part of our query that collects (or isn’t required to collect) the interesting bit of data as an inline view.
