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

s海若

浏览: 18056 次
性别:
来自: 珠海

最近访客更多访客>>

sb33060418

zitoo_24

renzhe0626

doney_dongxiang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

博客分类：

Oracle

Oracle SQL Access

内连接：
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 |
--------------------------------------------------------------------------------------
可以看到事实上查询笛卡尔积要比查询全外连接快得多。

分享到：