从查询计划看Oracle连接查询性能误解内连接：SQL/86select *from products pcross join product_types误解
从查询计划看Oracle连接查询性能误解
内连接：
SQL/86
select *from products pcross 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 |
--------------------------------------------------
可以看到事实上查询笛卡尔积要比查询全外连接快得多。
