【SQL 學習】表連線--natural join 的一個bug
自然連線（NATURAL JOIN）是一種特殊的等價連線，它將表中具有相同名稱的列自動進行記錄匹配。自然連線不必指定任何同等連線條件。這篇文章講的一個關於natural join 的bug！（由 dingjun123 提示！）
SQL> conn store/yang
已連線。
SQL> create table a as select * from all_objects;
表已建立。
SQL> set timing on
SQL> create table b as select * from all_objects;
表已建立。
已用時間: 00: 00: 20.36
SQL> set autot on
SQL> set linesize 100
SQL> select count(*) from a natural join b;
COUNT(*)
----------
0 ---錯誤的結果！
已用時間: 00: 00: 00.04
執行計劃
----------------------------------------------------------
Plan hash value: 1397777030
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | | 1500 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 316 | | | |
|* 2 | HASH JOIN | | 1 | 316 | 8184K| 1500 (1)| 00:00:18 |
| 3 | TABLE ACCESS FULL| A | 49280 | 7603K| | 281 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| B | 66983 | 10M| | 282 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EDITION_NAME"="B"."EDITION_NAME" AND
"A"."NAMESPACE"="B"."NAMESPACE" AND "A"."SECONDARY"="B"."SECONDARY" AND
"A"."GENERATED"="B"."GENERATED" AND "A"."TEMPORARY"="B"."TEMPORARY" AND
"A"."STATUS"="B"."STATUS" AND "A"."TIMESTAMP"="B"."TIMESTAMP" AND
"A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME" AND "A"."CREATED"="B"."CREATED" AND
"A"."OBJECT_TYPE"="B"."OBJECT_TYPE" AND
"A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID" AND
"A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."SUBOBJECT_NAME"="B"."SUBOBJECT_NAME
" AND "A"."OBJECT_NAME"="B"."OBJECT_NAME" AND "A"."OWNER"="B"."OWNER")
Note
-----
- dynamic sampling used for this statement
統計資訊
------------------------------------------------------
1801 recursive calls
0 db block gets
1407 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
SQL> truncate table a;
表被截斷。
已用時間: 00: 00: 00.43
SQL> truncate table b;
表被截斷。
已用時間: 00: 00: 00.04
SQL> drop table a;
表已刪除。
已用時間: 00: 00: 00.57
SQL> drop table b;
表已刪除。
已用時間: 00: 00: 00.06
SQL> create table a (id number ,name varchar2(10)) ;
表已建立。
已用時間: 00: 00: 00.10
SQL> insert into a values (1,'aa');
已建立 1 行。
已用時間: 00: 00: 00.00
SQL> insert into a values (2,'bb');
已建立 1 行。
已用時間: 00: 00: 00.00
SQL> create table b as select * from a ;
表已建立。
已用時間: 00: 00: 00.29
SQL> set autot on
SQL> select count(*) from a natural join b;
COUNT(*)
----------
2 ----結果是正確的
已用時間: 00: 00: 00.03
執行計劃
----------------------------------------------------------
Plan hash value: 1397777030
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | HASH JOIN | | 1 | 40 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 2 | 40 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 2 | 40 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("A"."NAME"="B"."NAME" AND "A"."ID"="B"."ID")
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
103 recursive calls
0 db block gets
34 consistent gets
5 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> truncate table a;
表被截斷。
已用時間: 00: 00: 00.25
SQL> truncate table b;
表被截斷。
已用時間: 00: 00: 00.01
SQL> drop table a;
表已刪除。
已用時間: 00: 00: 00.28
SQL> drop table b;
表已刪除。
已用時間: 00: 00: 00.06
SQL> create table b as select * from all_objects where rownum < 1000;
表已建立。
已用時間: 00: 00: 00.87
SQL> drop table a;
表已刪除。
已用時間: 00: 00: 00.35
SQL> create table a as select * from b; a和b 兩個表是一樣的。
表已建立。
已用時間: 00: 00: 00.25
SQL> select * from a
2 minus
3 select * from b;
未選定行
已用時間: 00: 00: 00.03
SQL> select count(*) from a;
COUNT(*)
----------
999
已用時間: 00: 00: 00.01
SQL> select count(*) from b;
COUNT(*)
----------
999
已用時間: 00: 00: 00.01
SQL> set autot on
SQL> select count(*) from a natural join b;
COUNT(*)
----------
0 ---應該返回999行，但是結果是0 顯然這是一個bug
已用時間: 00: 00: 00.01
執行計劃
----------------------------------------------------------
Plan hash value: 1397777030
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 13 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 316 | | |
|* 2 | HASH JOIN | | 1 | 316 | 13 (8)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A | 999 | 154K| 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| B | 999 | 154K| 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."EDITION_NAME"="B"."EDITION_NAME" AND
"A"."NAMESPACE"="B"."NAMESPACE" AND "A"."SECONDARY"="B"."SECONDARY" AND
"A"."GENERATED"="B"."GENERATED" AND "A"."TEMPORARY"="B"."TEMPORARY" AND
"A"."STATUS"="B"."STATUS" AND "A"."TIMESTAMP"="B"."TIMESTAMP" AND
"A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME" AND "A"."CREATED"="B"."CREATED"
AND "A"."OBJECT_TYPE"="B"."OBJECT_TYPE" AND
"A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID" AND
"A"."OBJECT_ID"="B"."OBJECT_ID" AND
"A"."SUBOBJECT_NAME"="B"."SUBOBJECT_NAME" AND
"A"."OBJECT_NAME"="B"."OBJECT_NAME" AND "A"."OWNER"="B"."OWNER")
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
582 recursive calls
0 db block gets
168 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> conn scott/yang
已連線。
SQL> select ename ,dname from emp natural join dept;
ENAME DNAME
---------- -----------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
JAMES SALES
BLAKE SALES
MARTIN SALES
-------結果是正確的！
已選擇14行。
執行計劃
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
統計資訊
---------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
---貼出執行計劃，只是想看看 natural join 會走什麼樣的計劃。
重點是這個對於all_objects 構造出的表使用natural join 查詢時 沒有給出正確的結果。。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-673258/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL 學習】表連線SQL
- oracle natural joinOracle
- sql 連線查詢例項（left join）三表連線查詢SQL
- SQL語句中不同的連線JOIN及SQL中join的各種用法SQL
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- Oracle表連線操作——Hash Join（雜湊連線）下Oracle
- Oracle表連線操作——Hash Join（雜湊連線）上Oracle
- mysql INNER JOIN、LEFT JOIN、RIGHT JOIN；內連線（等值連線）、左連線、右連線MySql
- 微課sql最佳化(15)、表的連線方法(4)-關於Hash Join(雜湊連線)SQL
- SQL 經典回顧：JOIN 表連線操作不完全指南SQL
- 【SQL】表連線 --半連線SQL
- 表的連線方式:NESTED LOOP、HASH JOIN、SORT MERGE JOIN(轉)OOP
- 表的連線是指在一個SQL語句中通過表與表之間的關連SQL
- SQL裡3個表的連線查詢SQL
- LINQ系列：LINQ to SQL Join連線SQL
- 微課sql最佳化(16)、表的連線方法(5)-關於Merge Join(排序合連線)SQL排序
- Apache Spark SQL的高階Join連線技術ApacheSparkSQL
- 表連線 join和(+)、union和uion allUI
- Oracle(+)連線與Join連線Oracle
- Oracle表連線操作——Merge Sort Join（合併排序連線）Oracle排序
- 【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER JSQL
- SQL 三表連線SQL
- LEFT JOIN 和JOIN 多表連線
- 11g文件學習----sql連線SQL
- 兩種連線的表達 ：left(right) join 和 (+)
- [資料庫][SQL]圖解各種連線join資料庫SQL圖解
- 幾種表的連線方式（SQL）SQL
- T-SQL學習中–內聯接，外連線，交叉連線SQL
- Oracle 的 hash join連線方式Oracle
- SQL左連線攻略--Mysql學習心得(符合sql標準)MySql
- 連線查詢簡析 join 、 left join 、 right join
- 【SQL】表連線七種方式SQL
- SQL表連線方式詳解SQL
- 學習tcl的幾個好網路連線
- 外連線(outer join)示例
- 聊聊CBO的連線排列（Join Permutation）
- 【SQL 學習】一個面試題SQL面試題
- 學習連連看 連線線之謎+道具的使用