从查询计划看Oracle连接查询性能误解

内连接：
SQL/86

select *
from products p, product_types pt
where p.product_type_id = pt.product_type_id;

SQL/92

select *
from products p
inner join product_types using(product_type_id);

左外连接：
SQL/86

select *
from products p, product_types pt
where p.product_type_id = pt.product_type_id(+);

SQL/92

select *
from products
left outer join product_types using(product_type_id);

右外连接：
SQL/86

select *
from products p, product_types pt
where p.product_type_id (+) = pt.product_type_id;

SQL/92

select *
from products p
right outer join product_types pt using(product_type_id);

全外连接：
SQL/86
无对应语法
SQL/92

select *
from products p
full outer join product_types pt using(product_type_id);

笛卡尔积：
SQL/86

select *
from products p, product_types pt

SQL/92

select *
from products p
cross join product_types

误解一：SQL/86语句比SQL/92快
以内连接为例，
SQL/86的Plan:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 605 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 605 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT_TYPES | 5 | 40 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PRODUCT_TYPES_PK | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 517 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 11 | 517 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL/92的Plan:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 605 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 11 | 605 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT_TYPES | 5 | 40 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PRODUCT_TYPES_PK | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 517 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 11 | 517 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
是的，两者的结果是一样的。所以SQL/86的效率其实与SQL/92的等价语句是一样的，只是形式的不同，在后台它们执行的是相同的操作。曾经碰到过有位经验丰富的前辈在做SQL调优的时候就搬出了SQL/86的语法来替代原来有的Join操作，当时还十分钦佩，现在看来只是个习惯问题，并不能带来实质上性能的提升。其他的连接也可以得出同样的结论。

误解二：left join 和right join是连接不同方向上的等价语句。
left join的Plan:
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 660 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 12 | 660 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PRODUCTS | 12 | 564 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| PRODUCT_TYPES | 5 | 40 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
right join的Plan:
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 605 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 11 | 605 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT_TYPES | 5 | 40 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PRODUCT_TYPES_PK | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 11 | 517 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 11 | 517 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
由此可见,left join 只要扫描两个表，然后使用hash join就可以完成，而right join则要执行与inner join类似的操作。所以在同等的情况下，使用left join 总是要比right join好。

误解三：全连接比笛卡尔积快。
显然全连接的记录数量要比笛卡尔积少的多，但是记录少并不代表一定就快。
full join的Plan:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1170 | 13 (16)| 00:00:01 |
| 1 | VIEW | | 13 | 1170 | 13 (16)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 12 | 660 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | PRODUCTS | 12 | 564 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | PRODUCT_TYPES | 5 | 40 | 3 (0)| 00:00:01 |
| 6 | MERGE JOIN ANTI | | 1 | 11 | 6 (17)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_TYPES | 5 | 40 | 2 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PRODUCT_TYPES_PK | 5 | | 1 (0)| 00:00:01 |
|* 9 | SORT UNIQUE | | 11 | 33 | 4 (25)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | PRODUCTS | 11 | 33 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
cross join的Plan:
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 3300 | 10 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 60 | 3300 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PRODUCT_TYPES | 5 | 40 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 12 | 564 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | PRODUCTS | 12 | 564 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
可以看到事实上查询笛卡尔积要比查询全外连接快得多。