自然連接配接（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;
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');
sql> create table b as select * from a ;
已用時間: 00: 00: 00.29
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
34 consistent gets
5 physical reads
0 redo size
419 bytes sent via sql*net to client
4 sorts (memory)
0 sorts (disk)
已用時間: 00: 00: 00.25
已用時間: 00: 00: 00.01
已用時間: 00: 00: 00.28
sql> create table b as select * from all_objects where rownum < 1000;
已用時間: 00: 00: 00.87
已用時間: 00: 00: 00.35
sql> create table a as select * from b; a和b 兩個表是一樣的。
sql> select * from a
2 minus
3 select * from b;
未標明行
已用時間: 00: 00: 00.03
sql> select count(*) from a;
999
sql> select count(*) from b;
0 ---應該傳回999行，但是結果是0 顯然這是一個bug
----------------------------------------------------------------------------
| 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 |
---------------------------------------------------
2 - access("a"."edition_name"="b"."edition_name" and
"a"."last_ddl_time"="b"."last_ddl_time" and "a"."created"="b"."created"
and "a"."object_type"="b"."object_type" 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
-----
582 recursive calls
168 consistent gets
6 sorts (memory)
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
11 consistent gets
0 physical reads
0 redo size
674 bytes sent via sql*net to client
2 sql*net roundtrips to/from client
1 sorts (memory)
14 rows processed
---貼出執行計劃，隻是想看看 natural join 會走什麼樣的計劃。
重點是這個對于all_objects 構造出的表使用natural join 查詢時 沒有給出正确的結果。。
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)