首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

子查询无法展开的模拟和几个案例

作者：yngbzl_165 | 来源：互联网 | 2018-06-12 00:57
之前生产环境有一部分sql执行出现较大的性能问题，都是由于应该走hashjoin的走的是filter，而这个问题困扰了小鱼很大一阵子，小鱼是想在sql优化上深入学习的，而为什么子查询没有展开选择走的filter而不是性能更好的hashjoin了，下面我们先测试，然后再来

之前生产环境有一部分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<(select max(created) from table01 a w
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"<(SELECT /*+ */ MAX("CREATED") FROM "TABLE01"
"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 <(SELECT MAX (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"<"VW_COL_1")

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 <( SELECT MAX (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"<(SELECT MAX("CREATED") FROM "TABLE01" "A" WHERE
"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！

原文地址：子查询无法展开的模拟和几个案例, 感谢原作者分享。

推荐阅读
  • go

    每天收获一点点Hadoop概述

    一、Hadoop来历Hadoop的思想来源于Google在做搜索引擎的时候出现一个很大的问题就是这么多网页我如何才能以最快的速度来搜索到，由于这个问题Google发明 ... [详细]
  • list

    学习SLAM的女生，很酷

    学习SLAM的女生，很酷
    本文介绍了学习SLAM的女生的故事，她们选择SLAM作为研究方向，面临各种学习挑战，但坚持不懈，最终获得成功。文章鼓励未来想走科研道路的女生勇敢追求自己的梦想，同时提到了一位正在英国攻读硕士学位的女生与SLAM结缘的经历。 ... [详细]
  • int

    【机器学习】生成式对抗网络模型综述

    生成式对抗网络模型综述摘要生成式对抗网络模型(GAN)是基于深度学习的一种强大的生成模型，可以应用于计算机视觉、自然语言处理、半监督学习等重要领域。生成式对抗网络 ... [详细]
  • int

    云原生边缘计算之KubeEdge简介及功能特点

    云原生边缘计算之KubeEdge简介及功能特点
    本文介绍了云原生边缘计算中的KubeEdge系统，该系统是一个开源系统，用于将容器化应用程序编排功能扩展到Edge的主机。它基于Kubernetes构建，并为网络应用程序提供基础架构支持。同时，KubeEdge具有离线模式、基于Kubernetes的节点、群集、应用程序和设备管理、资源优化等特点。此外，KubeEdge还支持跨平台工作，在私有、公共和混合云中都可以运行。同时，KubeEdge还提供数据管理和数据分析管道引擎的支持。最后，本文还介绍了KubeEdge系统生成证书的方法。 ... [详细]
  • int

    2020年AI产业报告：100个岗位抢1个人，计算机视觉成最大缺口

    “你永远都不知道明天和‘公司的意外’哪个先来。”疫情期间，这是我们最战战兢兢的心情。但是显然，有些人体会不了。这份行业数据，让笔者“柠檬” ... [详细]
  • int

    生成对抗式网络GAN及其衍生CGAN、DCGAN、WGAN、LSGAN、BEGAN介绍

    生成对抗式网络GAN及其衍生CGAN、DCGAN、WGAN、LSGAN、BEGAN介绍
    一、GAN原理介绍学习GAN的第一篇论文当然由是IanGoodfellow于2014年发表的GenerativeAdversarialNetworks（论文下载链接arxiv：[h ... [详细]
  • int

    [译]技术公司十年经验的职场生涯回顾

    [译]技术公司十年经验的职场生涯回顾
    本文是一位在技术公司工作十年的职场人士对自己职业生涯的总结回顾。她的职业规划与众不同，令人深思又有趣。其中涉及到的内容有机器学习、创新创业以及引用了女性主义者在TED演讲中的部分讲义。文章表达了对职业生涯的愿望和希望，认为人类有能力不断改善自己。 ... [详细]
  • int

    WinPythonHadoop在Win10上安装教程

    本文介绍了在Win10上安装WinPythonHadoop的详细步骤，包括安装Python环境、安装JDK8、安装pyspark、安装Hadoop和Spark、设置环境变量、下载winutils.exe等。同时提醒注意Hadoop版本与pyspark版本的一致性，并建议重启电脑以确保安装成功。 ... [详细]
  • int

    Oracle分析函数first_value()和last_value()的用法及原理

    Oracle分析函数first_value()和last_value()的用法及原理
    本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]
  • int

    解决Cydia数据库错误：could not open file /var/lib/dpkg/status 的方法

    解决Cydia数据库错误：could not open file /var/lib/dpkg/status 的方法
    本文介绍了解决iOS系统中Cydia数据库错误的方法。通过使用苹果电脑上的Impactor工具和NewTerm软件，以及ifunbox工具和终端命令，可以解决该问题。具体步骤包括下载所需工具、连接手机到电脑、安装NewTerm、下载ifunbox并注册Dropbox账号、下载并解压lib.zip文件、将lib文件夹拖入Books文件夹中，并将lib文件夹拷贝到/var/目录下。以上方法适用于已经越狱且出现Cydia数据库错误的iPhone手机。 ... [详细]
  • int

    ASP.NET Tips: 获取插入记录的ID的方法详解

    本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]
  • int

    VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

    本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • select

    FileNotFoundException: File does not exist

    ubuntu用sqoop将数据从hive导入mysql时，命令： ... [详细]
  • list

    Postgresql备份和恢复的方法及命令行操作步骤

    Postgresql备份和恢复的方法及命令行操作步骤
    本文介绍了使用Postgresql进行备份和恢复的方法及命令行操作步骤。通过使用pg_dump命令进行备份，pg_restore命令进行恢复，并设置-h localhost选项，可以完成数据的备份和恢复操作。此外，本文还提供了参考链接以获取更多详细信息。 ... [详细]
  • 数组

    读书的目的及学习算法的重要性

    本文讨论了读书的目的以及学习算法的重要性，并介绍了两个算法：除法速算和约瑟夫环的数学算法。同时，通过具体的例子和推理，解释了为什么x=x+k序列中的第一个人的位置为k，以及序列2和序列3的关系。通过学习算法，可以提高思维能力和解决问题的能力。 ... [详细]
author-avatar
yngbzl_165
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有