子查询无法展开的模拟和几个案例
- WBOYオリジナル
- 2016-06-07 16:36:431150ブラウズ
之前生产环境有一部分sql执行出现较大的性能问题，都是由于应该走hash join的走的是filter，而这个问题困扰了小鱼很大一阵子，小鱼是想在sql优化上深入学习的，而为什么子查询没有展开选择走的filter而不是性能更好的hash join了，下面我们先测试，然后再来
之前生产环境有一部分sql执行出现较大的性能问题，都是由于应该走hash join的走的是filter，而这个问题困扰了小鱼很大一阵子，小鱼是想在sql优化上深入学习的，而为什么子查询没有展开选择走的filter而不是性能更好的hash join了，下面我们先测试，然后再来看下生产环境的案例：
数据库版本是10.2.0.4,建立三个表table01、table02、table03
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 15 14:53:07 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table table01 as select * from dba_objects;
SQL> create table table02 as select * from dba_objects;
SQL> create table table03 as select * from dba_objects;
SQL> select * from table02 b where (object_id in (select object_id from table01
a) or object_id in (select object_id from table03));
50038 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 64347382
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4712 | 814K| 154 (2)| 00:00:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| 154 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | TABLE01 | 512 | 6656 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TABLE03 | 440 | 5720 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TABLE01" "A" WHERE
"OBJECT_ID"=:B1) OR EXISTS (SELECT /*+ */ 0 FROM "TABLE03" "TABLE
03"
WHERE "OBJECT_ID"=:B2))
3 - filter("OBJECT_ID"=:B1)
4 - filter("OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
65 recursive calls
0 db block gets
17166265 consistent gets
687 physical reads
0 redo size
2565005 bytes sent via SQL*Net to client
37177 bytes received via SQL*Net from client
3337 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50038 rows processed
这里看出由于子查询中加了or，cbo只能选择走低性能的filter，而其实这个sql我们改写成如下的形式，此时cbo就能展开这个子查询为表连接
SQL> SELECT *
2 FROM table02 b
3 WHERE object_id IN ( (SELECT object_id
4 FROM table01 a)
5 UNION ALL
6 (SELECT object_id FROM table03));
50038 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3306159213
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 95255 | 17M| | 1008 (1)|
00:00:13 |
|* 1 | HASH JOIN | | 95255 | 17M| 2328K| 1008 (1)|
00:00:13 |
| 2 | VIEW | VW_NSO_1 | 95253 | 1209K| | 306 (1)|
00:00:04 |
| 3 | HASH UNIQUE | | 95253 | 1209K| | 306 (51)|
00:00:04 |
| 4 | UNION-ALL | | | | | |
|
| 5 | TABLE ACCESS FULL| TABLE01 | 51209 | 650K| | 153 (1)|
00:00:02 |
| 6 | TABLE ACCESS FULL| TABLE03 | 44044 | 559K| | 153 (1)|
00:00:02 |
| 7 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| | 154 (2)|
00:00:02 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5373 consistent gets
0 physical reads
0 redo size
2565005 bytes sent via SQL*Net to client
37177 bytes received via SQL*Net from client
3337 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50038 rows processed
我们来看看11.2.0.3下是否也需要改写了，小鱼也没试过，测试下吧：
SQL> select * from table02 b where (object_id in (select object_id from table01
2 a) or object_id in (select object_id from table03));
87289 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1591019701
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 183K| 38M| | 2092 (1)| 00:00:26 |
|* 1 | HASH JOIN | | 183K| 38M| 4472K| 2092 (1)| 00:00:26 |
| 2 | VIEW | VW_NSO_1 | 183K| 2325K| | 677 (1)| 00:00:09 |
| 3 | HASH UNIQUE | | 183K| 2325K| | 677 (51)| 00:00:09 |
| 4 | UNION-ALL | | | | | | |
| 5 | TABLE ACCESS FULL| TABLE03 | 89910 | 1141K| | 338 (1)| 00:00:05 |
| 6 | TABLE ACCESS FULL| TABLE01 | 93256 | 1183K| | 338 (1)| 00:00:05 |
| 7 | TABLE ACCESS FULL | TABLE02 | 82647 | 16M| | 339 (1)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9445 consistent gets
3720 physical reads
0 redo size
4707818 bytes sent via SQL*Net to client
64529 bytes received via SQL*Net from client
5821 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87289 rows processed
11.2.0.3版本的cbo优化器居然自动把我们这个or查询组合为一个union all然后hash unique去重为一个view视图然后去和主表做hash join，优化器确是在不断的进步。
类似的子查询无法展开的问题我们还可以再看下面几个例子
Oracle 10.2.0.4版本：
SQL> select * from table02 b where object_id in (select ob
ject_id from table01 a where b.object_id>5000);
45120 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3332700264
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 5664 | 3182 (1)| 00:00:39 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| 154 (2)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TABLE01 | 512 | 6656 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TABLE01" "A" WHERE
:B1>5000 AND "OBJECT_ID"=:B2))
3 - filter(:B1>5000)
4 - filter("OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17001967 consistent gets
0 physical reads
0 redo size
2335963 bytes sent via SQL*Net to client
33569 bytes received via SQL*Net from client
3009 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
45120 rows processed
其实这里很可能是个笔误引起的，应该是a.object_id>5000,可能开发人员写成了b.object_id>5000,悲剧的走了filter。
Oracle 11.2.0.3版本：
SQL> SELECT *
2 FROM table02 b
3 WHERE object_id IN (SELECT object_id
4 FROM table01 a
5 WHERE b.object_id > 5000);
82290 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1361234999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4132 | 887K| 678 (1)| 00:00:09 |
|* 1 | HASH JOIN SEMI | | 4132 | 887K| 678 (1)| 00:00:09 |
|* 2 | TABLE ACCESS FULL | TABLE02 | 4132 | 835K| 338 (1)| 00:00:05 |
| 3 | VIEW | VW_NSO_1 | 91760 | 1164K| 339 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| TABLE01 | 91760 | 1164K| 339 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
2 - filter("B"."OBJECT_ID">5000)
4 - filter("OBJECT_ID">5000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
8579 consistent gets
2480 physical reads
0 redo size
4466004 bytes sent via SQL*Net to client
60855 bytes received via SQL*Net from client
5487 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
82290 rows processed
可能有朋友提到这里的查询用的是关联列，如果我们用非关联过滤了，其实结果也是一样的
SQL> SELECT *
2 FROM table02 b
3 WHERE object_id IN (SELECT object_id
4 FROM table01 a
5 WHERE b.data_object_id > 5000);
19927 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1361234999
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4132 | 887K| 678 (1)| 00:00:09 |
|* 1 | HASH JOIN SEMI | | 4132 | 887K| 678 (1)| 00:00:09 |
|* 2 | TABLE ACCESS FULL | TABLE02 | 4132 | 835K| 339 (1)| 00:00:05 |
| 3 | VIEW | VW_NSO_1 | 93256 | 1183K| 338 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL| TABLE01 | 93256 | 1183K| 338 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="OBJECT_ID")
2 - filter("B"."DATA_OBJECT_ID">5000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
4491 consistent gets
2480 physical reads
0 redo size
1270866 bytes sent via SQL*Net to client
15128 bytes received via SQL*Net from client
1330 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
19927 rows processed
这个笔误是常有的事情，或者说开发人员往往写出来的代码只要满足业务逻辑需求，比如小鱼前不久碰见的一个案例也是开发人员写的代码，业务逻辑是满足的，具体代码类似这种查询：
SELECT *
FROM table02 b
WHERE object_id IN (SELECT object_id
FROM table02 a
WHERE b.object_id > 5000);
Execution Plan
----------------------------------------------------------
Plan hash value: 3539933929
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 5664 | 3182 (1)| 00:00:39 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| 154 (2)| 00:00:02 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| TABLE02 | 483 | 6279 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TABLE02" "A" WHERE
:B1>5000 AND "OBJECT_ID"=:B2))
3 - filter(:B1>5000)
4 - filter("OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
17001959 consistent gets
0 physical reads
0 redo size
2336165 bytes sent via SQL*Net to client
33580 bytes received via SQL*Net from client
3010 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
45121 rows processed
这里由于table02 a和table02 b是同一个表，所以这里业务逻辑完全符合，刚开始数据量小，
他们就一直这么写着用着，慢慢得数量大了，由于是10.2.0.5数据库，后面走filter效率极低，极大的拉低了数据库性能，导致系统宕机，而同样的情况在oracle 11g下优化器却改善了很多。
再来看看下面这个查询，有时候我们需要连接两个表，然后分组求出满足某个列聚合函数的数据
Oracle 10.2.0.4版本：
SQL> select * from table02 b where created
here a.object_id=b.object_id group by a.object_id);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1461399523
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 2 | 354 | 3185 (1)| 00:00:39
|
|* 1 | FILTER | | | | |
|
| 2 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| 154 (2)| 00:00:02
|
| 3 | SORT GROUP BY NOSORT| | 512 | 11264 | 2 (0)| 00:00:01
|
|* 4 | TABLE ACCESS FULL | TABLE01 | 512 | 11264 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"
"A" WHERE "A"."OBJECT_ID"=:B1 GROUP BY "A"."OBJECT_ID"))
4 - filter("A"."OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
37 recursive calls
0 db block gets
34627266 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
而其实这个sql等价于下面这个sql语句，而走的执行计划是更优秀的hash join
SQL> SELECT *
2 FROM table02 b
3 WHERE created
4 FROM table01 a
5 WHERE a.object_id = b.object_id);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1435252230
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 2560 | 497K| | 827 (1)| 00
:00:10 |
|* 1 | HASH JOIN | | 2560 | 497K| 1704K| 827 (1)| 00
:00:10 |
| 2 | VIEW | VW_SQ_1 | 51209 | 1100K| | 157 (4)| 00
:00:02 |
| 3 | HASH GROUP BY | | 51209 | 1750K| | 157 (4)| 00
:00:02 |
| 4 | TABLE ACCESS FULL| TABLE01 | 51209 | 1750K| | 153 (1)| 00
:00:02 |
| 5 | TABLE ACCESS FULL | TABLE02 | 48330 | 8353K| | 154 (2)| 00
:00:02 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="B"."OBJECT_ID")
filter("CREATED"
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
2212 consistent gets
0 physical reads
0 redo size
1139 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
0 rows processed
oracle 11.2.0.3版本跟10.2.0.4版本也是走的糟糕的filter：
SQL> explain plan for SELECT *
2 FROM table02 b
3 WHERE created
4 FROM table01 a
5 WHERE a.object_id = b.object_id
6 GROUP BY a.object_id);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1461399523
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 5517 (1)| 00:01:07 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TABLE02 | 82647 | 16M| 339 (1)| 00:00:05 |
| 3 | SORT GROUP BY NOSORT| | 933 | 20526 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TABLE01 | 933 | 20526 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"
"A"."OBJECT_ID"=:B1 GROUP BY "A"."OBJECT_ID"))
4 - filter("A"."OBJECT_ID"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
子查询中包含a.col like b.col的查询
Oracle 10.2.0.4版本：
SQL> SELECT /*+use_hash(a b)*/ *
2 FROM table02 b
3 WHERE object_id IN (SELECT object_id
4 FROM table02 a
5 WHERE a.object_type like b.object_type);
50038 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 337184939
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 645K (1)| 02:09:02 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 48330 | 8353K| 154 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| TABLE02 | 24 | 576 | 14 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TABLE02" "A" WHERE
"OBJECT_ID"=:B1 AND "A"."OBJECT_TYPE" LIKE :B2))
3 - filter("OBJECT_ID"=:B1 AND "A"."OBJECT_TYPE" LIKE :B2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
17165439 consistent gets
0 physical reads
0 redo size
2565005 bytes sent via SQL*Net to client
37177 bytes received via SQL*Net from client
3337 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50038 rows processed
Oracle 11.2.0.3版本
SQL> explain plan for SELECT *
2 FROM table02 b
3 WHERE object_id IN (SELECT object_id
4 FROM table02 a
5 WHERE a.object_type like b.object_type);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 337184939
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1447K (1)| 04:49:25 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TABLE02 | 82647 | 16M| 339 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| TABLE02 | 41 | 984 | 18 (0)| 00:00:01 |
------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "TABLE02" "A" WHERE
"OBJECT_ID"=:B1 AND "A"."OBJECT_TYPE" LIKE :B2))
3 - filter("OBJECT_ID"=:B1 AND "A"."OBJECT_TYPE" LIKE :B2)
Note
-----
- dynamic sampling used for this statement (level=2)
21 rows selected.
这里由于有a.object_type like b.object_type，子查询没有展开，那么是否有办法进行优化了，由于hash join算法决定只能用于等值比较，所以这种a.col like b.col是无法转化为hash join的，如果系统里面真有这种业务需求，那么只能采取别的办法来进行优化了。
上面小鱼简单的列出了子查询无法展开的几个现象和现在比较流行的oracle 10gR2和11gR2版本对于子查询的几个变化。下面小鱼用简短的文章来描述下前些天遇见的几个案例：
1 案例开发人员笔误引起的sql性能问题 http://www.dbaxiaoyu.com/archives/1995
小鱼当初没有过分的去分析为什么最后会走filter，而不走hash join，现在通过上面的几个案例测试，想问题原因基本已经知道了，开发人员有个笔误（其实这个sql也是满足业务需求的，对于开发人员可能不叫笔误）
Select * from abstractti0_
Where abstractti0_.TICKET_ID in （（
Select TICKET_ID from tab02 where。。。）
UNION
(SELECT abstractti8_.TICKET_ID
FROM HF_LT_WORKFLOW_ABSTRACTTICKET abstractti8_
WHERE (abstractti0_.CURRENT_STAGE IN
('xqywtj_deal3', 'xqybcgj_deal3'))
AND (abstractti0_.TICKET_ID IN
(SELECT DISTINCT workitemco0_.ENTITY_ID
FROM HF_LTWORKFLOW_ITEM_COMPLETED workitemco0_
WHERE (workitemco0_.BELONG_TO_ID =
'U00000000805')
AND (workitemco0_.WORKFLOWID =
'xqybcgjWorkflow'))))
由于sql较长，接近100多行，这里为了展示方便，就做了一些改写，基本含义就上面这种，子查询中的又出现了主查询的表的过滤条件，而这个数据库版本正是10.2.0.5版本，这个将导致优化器无法展开子查询为表连接，而不得不选择性能较差的filter，最后改掉abstractti0_为abstractti8_，sql基本在几秒内完成，具体大家可以自己看着上面的测试来进行模拟。
2 or 查询导致子查询无法展开 http://www.dbaxiaoyu.com/archives/2048 选择了错误的filter
AND((abstractti0_.TICKET_ID IN
(SELECT abstractti4_.TICKET_ID
FROM HF_LT_WORKFLOW_ABSTRACTTICKET abstractti4_
WHERE (1 =1 )
AND(abstractti4_.TICKET_ID IN
(SELECT DISTINCT workitemco0_.ENTITY_ID
FROM HF_LTWORKFLOW_ITEM_COMPLETED workitemco0_
WHERE (workitemco0_.BELONG_TO_ID='admin' )
AND(workitemco0_.WORKFLOWID ='x qybcgjWorkflow' )
))
AND(abstractti4_.CURRENT_STAGE='ticket_close' )
))
OR(abstractti0_.TICKET_ID IN
(SELECT DISTINCT abstractti6_.TICKET_ID
FROM HF_LTWORKFLOW_ITEM workitem5_,
HF_LT_WORKFLOW_ABSTRACTTICKET abstractti6_,
HF_DM_SAMPLE dmsample7_
WHERE (1 =1 )
AND(abstractti6_.TICKET_ID IN
(SELECT DISTINCT workitemco0_.ENTITY_ID
FROM HF_LTWORKFLOW_ITEM_COMPLETED workitemco0_
WHERE (workitemco0_.BELONG_TO_ID='admin' )
AND(workitemco0_.WORKFLOWID ='xqybcgjWorkflow' )
))
同样由于sql较长，这些报表的sql都是这么长，数据库版本是10.2.0.5版本，由于or的出现，导致了最后也走了filter，对于这类sql的改写小鱼在上面的测试中也提到了改写为union all即可。
关于sql优化需要大量的经验和对cbo的深入理解，而我们当初学习数据库时就知道一个信息，数据库的性能问题80%来源于不合理的sql，希望小鱼自己在sql优化这个路上有一定的建树
Good luck！
原文地址：子查询无法展开的模拟和几个案例, 感谢原作者分享。