包含union all的view子查询无法展开表连接的模拟
之前小鱼写过一些关于子查询无法展开的文章，其中主要涉及到以下几点： 1关联的列没有not null约束，在子查询中使用not in去查找（oracle 10g无法展开为子查询，oracle 11g由于有新的hash join算法已经可以），如下 SQL> select a.* from ta01 a where a.obj
之前小鱼写过一些关于子查询无法展开的文章，其中主要涉及到以下几点：<br>
1关联的列没有not null约束，在子查询中使用not in去查找（oracle 10g无法展开为子查询，oracle 11g由于有新的hash join算法已经可以），如下
SQL> select a.* from ta01 a where a.object_id not in (select b.object_id from ta02 b)
Ta01和ta02表的object_id列上没有not null的约束
2 子查询中包含主表的关联列的谓词过滤（oracle 10g也无法展开为子查询，oracle 11g可以展开为子查询），如下
SQL> select a.* from ta01 a where a.object_id in （select b.object_id from ta02 b where a.object_type=’TABLE’）；
3 子查询中包含有下列形式的or的查询（oracle 10g同样无法展开为子查询，oracle 11g可以展开）
SQL> select a.* from ta01 a where a.object_id in (select b.object_id from ta02 b
where object_type='TABLE') or a.object_id in (select c.object_id from ta02 c wh
ere c.object_type='INDEX');
4 子查询包含有主表和子表列的like关联，比如下列sql语句（oracle 10g和11g都无法展开为子查询）
SQL> select a.* from ta01 a where a.object_id in (select b.object_id from ta02 b
where a.data_object_id like b.data_object_id);
http://www.eygle.com/archives/2013/04/2013_oracle_dtcc_dbsnake.html
eygle这篇文章里面又提到了一个比较常见的子查询无法展开的case，借鉴作者的案例进行一点简单的模拟来进行验证。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table ta01 as select * from dba_objects;
Table created.
SQL> create table ta02 as select * from dba_objects;
Table created.
SQL> create table tb01 as select * from dba_objects;
Table created.
SQL> create table tb02 as select * from dba_objects;
Table created.
SQL> create view ta_view as
2 select object_id,data_object_id,object_type from ta01 union all
3 select object_id,data_object_id,object_type from ta02;
View created.
SQL> create view tb_view as
2 select object_id,data_object_id,object_type from tb01 union all
3 select object_id,data_object_id,object_type from tb02;
View created.
SQL> select a.* from ta_view a where a.object_id in (select b.object_id from tb_
view b where b.object_type='TABLE');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 3623909176
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3738K| 131M| 617 (1)| 00:00:08 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | TA_VIEW | 88401 | 3194K| 308 (1)| 00:00:04 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS FULL| TA01 | 42509 | 1535K| 154 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL| TA02 | 45892 | 1658K| 154 (1)| 00:00:02 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
| 6 | VIEW | TB_VIEW | 42 | 1008 | 309 (1)| 00:00:04 |
| 7 | UNION-ALL | | | | | |
|* 8 | TABLE ACCESS FULL| TB01 | 17 | 408 | 154 (1)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| TB02 | 25 | 600 | 154 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM ( (SELECT /*+ */
"OBJECT_ID" "OBJECT_ID","DATA_OBJECT_ID" "DATA_OBJECT_ID","OBJECT_
TYPE"
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
"OBJECT_TYPE" FROM SYS."TB01" "TB01" WHERE "OBJECT_ID"=:B1 AND
"OBJECT_TYPE"='TABLE') UNION ALL (SELECT /*+ */ "OBJECT_ID"
"OBJECT_ID","DATA_OBJECT_ID" "DATA_OBJECT_ID","OBJECT_TYPE"
"OBJECT_TYPE" FROM SYS."TB02" "TB02" WHERE "OBJECT_ID"=:B2 AND
"OBJECT_TYPE"='TABLE')) "B"))
8 - filter("OBJECT_ID"=:B1 AND "OBJECT_TYPE"='TABLE')
9 - filter("OBJECT_ID"=:B1 AND "OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
33 rows selected.
Statistics
----------------------------------------------------------
146 recursive calls
0 db block gets
136371691 consistent gets
0 physical reads
0 redo size
83259 bytes sent via SQL*Net to client
2868 bytes received via SQL*Net from client
218 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3247 rows processed
这里由于主查询和子查询的表都是两个表相关联的视图，这个导致in之后的括号内的子查询并不是一个常量的集合，所以oracle这里没有对该子查询展开为表连接。
而我们可以改写为下列的sql语句，但是这里好像结果集不一样，这个是因为我们选取的表不是1对1引起的，这个不在我们考虑的范围内。
SQL> SELECT a.*
2 FROM ta_view a,
3 (SELECT object_id
4 FROM tb_view
5 WHERE object_type = 'TABLE') b
6 WHERE a.object_id = b.object_id;
6494 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 48097912
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3328K| 158M| 640 (5)| 00:00:08 |
|* 1 | HASH JOIN | | 3328K| 158M| 640 (5)| 00:00:08 |
| 2 | VIEW | TB_VIEW | 3306 | 42978 | 309 (1)| 00:00:04 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| TB01 | 1756 | 24584 | 154 (1)| 00:00:02 |
|* 5 | TABLE ACCESS FULL| TB02 | 1550 | 21700 | 154 (1)| 00:00:02 |
| 6 | VIEW | TA_VIEW | 100K| 3637K| 308 (1)| 00:00:04 |
| 7 | UNION-ALL | | | | | |
| 8 | TABLE ACCESS FULL| TA01 | 50333 | 786K| 154 (1)| 00:00:02 |
| 9 | TABLE ACCESS FULL| TA02 | 50334 | 786K| 154 (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="OBJECT_ID")
4 - filter("OBJECT_TYPE"='TABLE')
5 - filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1083 recursive calls
0 db block gets
3412 consistent gets
2782 physical reads
196 redo size
139022 bytes sent via SQL*Net to client
5244 bytes received via SQL*Net from client
434 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
6494 rows processed
当然如果业务也是1对多，那么这个改写是不符合业务逻辑的，那么是否就不能进行改写了，其实也不是这样的，我们只需要在构造的这个表中加上一个distinct去掉b表的重复的值，这样不就是类似为半连接了吗。
SQL> SELECT a.*
2 FROM ta_view a,
3 (SELECT distinct object_id
4 FROM tb_view
5 WHERE object_type = 'TABLE') b
6 WHERE a.object_id = b.object_id;
3247 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1607119383
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 3328K| 158M| 642 (5)| 00:00:08
|
|* 1 | HASH JOIN | | 3328K| 158M| 642 (5)| 00:00:08
|
| 2 | VIEW | | 3306 | 42978 | 310 (2)| 00:00:04
|
| 3 | HASH UNIQUE | | 3306 | 42978 | 310 (2)| 00:00:04
|
| 4 | VIEW | TB_VIEW | 3306 | 42978 | 309 (1)| 00:00:04
|
| 5 | UNION-ALL | | | | |
|
|* 6 | TABLE ACCESS FULL| TB01 | 1756 | 24584 | 154 (1)| 00:00:02
|
|* 7 | TABLE ACCESS FULL| TB02 | 1550 | 21700 | 154 (1)| 00:00:02
|
| 8 | VIEW | TA_VIEW | 100K| 3637K| 308 (1)| 00:00:04
|
| 9 | UNION-ALL | | | | |
|
| 10 | TABLE ACCESS FULL | TA01 | 50333 | 786K| 154 (1)| 00:00:02
|
| 11 | TABLE ACCESS FULL | TA02 | 50334 | 786K| 154 (1)| 00:00:02
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
6 - filter("OBJECT_TYPE"='TABLE')
7 - filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
3007 consistent gets
0 physical reads
0 redo size
83259 bytes sent via SQL*Net to client
2868 bytes received via SQL*Net from client
218 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3247 rows processed
关于我们常见的select a.* from a,b where a.id=b.id和select a.* from a where a.id in (select b.id from b)这两种查询，在业务层面有不同的是如果b表没有重复的id，那么这个查询业务逻辑是相同的，而如果b表有重复的，子查询是hash join的半连接方式，会对b.id进行去重，所以如果b.id存在多个重复值，可能会引起查询结果不一致。
并不是说包含union all的veiw会引起子查询无法展开为表连接，如上单个union all的view是不会影响子查询展开为表连接的。
SQL> select a.* from ta01 a where a.object_id in (select b.object_id from tb_vie
w b where b.object_type='TABLE');
1623 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1785931674
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 190 | 464 (2)| 00:00:06
|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 190 | 464 (2)| 00:00:06
|
| 2 | VIEW | VW_NSO_1 | 4229 | 54977 | 309 (1)| 00:00:04
|
| 3 | VIEW | TB_VIEW | 4229 | 99K| 309 (1)| 00:00:04
|
| 4 | UNION-ALL | | | | |
|
|* 5 | TABLE ACCESS FULL| TB01 | 1692 | 40608 | 154 (1)| 00:00:02
|
|* 6 | TABLE ACCESS FULL| TB02 | 2537 | 60888 | 154 (1)| 00:00:02
|
| 7 | TABLE ACCESS FULL | TA01 | 42509 | 7347K| 154 (1)| 00:00:02
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="$nso_col_1")
5 - filter("OBJECT_TYPE"='TABLE')
6 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
53 recursive calls
0 db block gets
3166 consistent gets
0 physical reads
0 redo size
85130 bytes sent via SQL*Net to client
1680 bytes received via SQL*Net from client
110 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1623 rows processed
而如果我们换到oracle 11.2.0.1的版本，再来看看包含两个union all的view的子查询是否能够展开
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> select a.* from ta_view a where a.object_id in (select b.object_id from tb_view b where b.object_type='TABLE');
7629 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1656093151
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6779K| 323M| 1206 (4)| 00:00:15 |
|* 1 | HASH JOIN | | 6779K| 323M| 1206 (4)| 00:00:15 |
| 2 | VIEW | VW_NSO_1 | 4191 | 54483 | 586 (1)| 00:00:08 |
| 3 | HASH UNIQUE | | 4191 | 54483 | 586 (1)| 00:00:08 |
| 4 | VIEW | TB_VIEW | 4191 | 54483 | 585 (1)| 00:00:08 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL| TB01 | 2036 | 48864 | 293 (1)| 00:00:04 |
|* 7 | TABLE ACCESS FULL| TB02 | 2155 | 51720 | 292 (1)| 00:00:04 |
| 8 | VIEW | TA_VIEW | 161K| 5845K| 585 (1)| 00:00:08 |
| 9 | UNION-ALL | | | | | |
| 10 | TABLE ACCESS FULL | TA01 | 82194 | 2969K| 292 (1)| 00:00:04 |
| 11 | TABLE ACCESS FULL | TA02 | 79581 | 2875K| 292 (1)| 00:00:04 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="OBJECT_ID")
6 - filter("OBJECT_TYPE"='TABLE')
7 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
63 recursive calls
0 db block gets
5917 consistent gets
3072 physical reads
0 redo size
200965 bytes sent via SQL*Net to client
6108 bytes received via SQL*Net from client
510 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7629 rows processed
这里oracle选择了将子查询展开为了表连接，优化器确实在做着很多的改变，这些改变需要我们通过文档 测试用例和实际的案例来验证，可以这么说脱离了oracle的版本来谈性能优化 sql调优其实往往是不严谨的。
最近注册了朋友介绍的一个论坛 http://www.bi168.cn/forum.php ，之前也注册了很多论坛比如itpub、otn、ask maclean等等，都没有怎么过多的去关注论坛的东西，记得maclean liu说过每天能遇见的案例是有限的，但是网络的同行遇见的case确是很多的，如果能够将别人遇见的模拟 理解 解决那么这个就是自己的。
原文地址：包含union all的view子查询无法展开表连接的模拟, 感谢原作者分享。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
keep體脂秤怎麼連接
Mar 07, 2024 pm 04:50 PM
keep體脂秤怎麼連接?keep是有專門設計的體脂秤，但是多數的用戶不知道如何連接keep的體脂秤，接下來就是小編為用戶帶來的keep體脂秤連接方法圖文教程，有興趣的用戶快來一起看看吧！ keep體脂秤怎麼連接1、先打開keep軟體，進入到主頁點選右下角【我的】選擇其中的【智慧硬體】;2、然後在我的智慧型裝置頁面，中間【新增裝置】按鈕;3 、之後在選擇你要加入的設備介面，選擇【智慧體脂/體重計】;4、接著在選擇設備型號頁面，點選【keep體脂秤】選項;5、最後在下圖所示的介面，最底部的【立即添加】
12306怎麼查詢歷史購票紀錄 查看歷史購票紀錄的方法
Mar 28, 2024 pm 03:11 PM
12306訂票app下載最新版是一款大家非常滿意的出行購票軟體，想去哪裡就去那裡非常方便，軟體內提供的票源非常多，只需要通過實名認證就能在線購票，所有用戶的出行車票機票都可以輕鬆買到，享受不同的優惠折扣。還能提前開啟預約搶票，預約飯店、專車接送都是可以的，有了它想去哪裡就去那裡一鍵購票，出行更加簡單方便，讓大家的出行體驗更舒服，現在小編在線詳細為12306用戶帶來查看歷史購票記錄的方法。 1.打開鐵路12306，點擊右下角我的，點擊我的訂單 2.在訂單頁面點擊已支付。 3.在已支付頁
修復Edge你的連線不是專用連線的三種方法
Mar 13, 2024 pm 01:30 PM
大家在使用Edge瀏覽器訪問網頁的時候，有遇到提示你的連接不是專用連接，導致網頁瀏覽失敗的情況嗎？這是怎麼回事？很多朋友遇到這種問題都不知道該如何處理，可以看看下面三個解決方法。 方法一（簡單粗暴）：在edge瀏覽器中，您可以透過進入設定並關閉安全性功能，然後在網站權限中阻止位置權限來嘗試解決原先報錯的網站無法存取的問題。需要注意的是，這種方法的有效性和持續時間可能會有所不同，無法確定特定的效果。重新啟動瀏覽器後，您可以嘗試造訪網站，看看是否有問題解決。 方法二： 調整鍵盤為英文輸
學信網如何查詢自己的學歷
Mar 28, 2024 pm 04:31 PM
學信網如何查詢自己的學歷？在學信網中是可以查詢到自己的學歷，很多用戶都不知道如何在學信網中查詢到自己的學歷，接下來就是小編為用戶帶來的學信網查詢自己學歷方法圖文教程，感興趣的用戶快來一起看看吧！學信網使用教程學信網如何查詢自己的學歷一、學信網入口：https://www.chsi.com.cn/二、網站查詢：第一步：點選上方學信網位址，進入首頁點選【學歷查詢】；第二步：在最新的網頁中點選如下圖箭頭所示的【查詢】；第三步：之後在新頁面點選【的登陸學信檔案】；第四步：在登陸頁面輸入資料點選【登陸】；
一加手錶怎麼連接藍牙耳機_一加手錶連接藍牙耳機的方法
Mar 23, 2024 pm 01:16 PM
1、將耳機放在耳機盒中並保持蓋子打開，長按盒子上的按鍵使耳機進入進入配對狀態。 2.開啟手錶音樂功能並選擇藍牙耳機，或在手錶設定功能選擇藍牙耳機。 3.在手錶選擇該耳機即可配對成功。
MySQL與PL/SQL的異同比較
Mar 16, 2024 am 11:15 AM
MySQL與PL/SQL是兩種不同的資料庫管理系統，分別代表了關係型資料庫和過程化語言的特性。本文將比較MySQL和PL/SQL的異同點，並附帶具體的程式碼範例進行說明。 MySQL是一種流行的關聯式資料庫管理系統，採用結構化查詢語言(SQL)來管理和操作資料庫。而PL/SQL是Oracle資料庫特有的過程化語言，用於編寫預存程序、觸發器和函數等資料庫物件。相同
悟飯遊戲廳連結遊戲手把方法步驟
Mar 19, 2024 pm 03:55 PM
悟飯遊戲廳如何連接藍牙手把？悟飯遊戲廳是許多手機遊戲玩家在使用的一款遊戲盒子，裡面有著海量熱門遊戲資源，以及豐富的遊戲相關功能，下面小編帶來遊戲手把連接方法介紹，各位玩家們來看看吧。 1.首先來到悟飯遊戲廳APP首頁，再點選首頁右下角「我的」選項；2、在我的頁面中找到【手把】功能，位置下圖所示，點選前往設定；3、選擇開啟手機藍牙功能，確認手把電源是開啟狀態；4、最後依照手把說明書進行配對連接，連接成功即可使用手遊體驗各種遊戲了。
MySQL連線數對資料庫效能的影響分析
Mar 16, 2024 am 10:09 AM
MySQL連線數對資料庫效能的影響分析隨著網路應用的不斷發展，資料庫成為了支援應用系統重要的資料儲存和管理工具。在資料庫系統中，連線數是一個重要的概念，它直接關係到資料庫系統的效能和穩定性。本文將從MySQL資料庫的角度出發，探討連線數對資料庫效能的影響，並透過具體的程式碼範例進行分析。一、連線數是什麼？連線數指的是資料庫系統同時支援的客戶端連線數，也可以理
