ORACLE数据库优化-HASH JOIN 与HASH JOIN OUTER，id=1 与id(+)=1区别

最新推荐文章于 2022-03-29 17:55:50 发布

执笔画情ora

最新推荐文章于 2022-03-29 17:55:50 发布

阅读量2.3k

点赞数 1

分类专栏： ORACLE数据库性能优化

本文链接：https://blog.csdn.net/oradbm/article/details/93145101

版权

ORACLE数据库性能优化专栏收录该内容

66 篇文章 5 订阅

订阅专栏

create table C1(id1 number,id2 varchar2(10));
create table C2(id1 number,id2 varchar2(10));

INSERT INTO C1 VALUES(1,'A');
INSERT INTO C1 VALUES(2,'B');
INSERT INTO C1 VALUES(3,'C');

INSERT INTO C2 VALUES(1,'A');
INSERT INTO C2 VALUES(2,'B');
INSERT INTO C2 VALUES(3,'C');
INSERT INTO C2 VALUES(4,'D');
INSERT INTO C2 VALUES(5,'F');
COMMIT;

SELECT C1.ID1,C1.ID2,C2.ID2 FROM C1,C2 WHERE C1.ID2(+)=C2.ID2 AND C1.ID1=1;
SELECT C1.ID1,C1.ID2,C2.ID2 FROM C1,C2 WHERE C1.ID2(+)=C2.ID2 AND C1.ID1(+)=1;

SET AUTOTRACE ON
SQL> select * from c1;

ID1 ID2
---------- ----------
   1 A
   2 B
   3 C

SQL> select * from c2;

ID1 ID2
---------- ----------
   1 A
   2 B
   3 C
   4 D
   5 F

SQL> SET AUTOTRACE ON
SQL> SELECT C1.ID1,C1.ID2,C2.ID2 FROM C1,C2 WHERE C1.ID2(+)=C2.ID2 AND C1.ID1=1;

ID1 ID2 ID2
---------- ---------- ----------
1 A A

Execution Plan
----------------------------------------------------------
Plan hash value: 1550765108

---------------------------------------------------------------------------
| Id | Operation   | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |   |   1 | 27 |   4 (0)| 00:00:01 |
|* 1 | HASH JOIN   |   |   1 | 27 |   4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| C1 |   1 | 20 |   2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| C2 |   5 | 35 |   2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("C1"."ID2"="C2"."ID2")
2 - filter("C1"."ID1"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
   65 recursive calls
   0 db block gets
   63 consistent gets
   1 physical reads
   0 redo size
   671 bytes sent via SQL*Net to client
   607 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   5 sorts (memory)
   0 sorts (disk)
   1 rows processed

SQL> SELECT C1.ID1,C1.ID2,C2.ID2 FROM C1,C2 WHERE C1.ID2(+)=C2.ID2 AND C1.ID1(+)=1;

ID1 ID2   ID2
---------- ---------- ----------
   1 A   A
       B
       C
       F
       D

Execution Plan
----------------------------------------------------------
Plan hash value: 2894768380

---------------------------------------------------------------------------
| Id | Operation   | Name | Rows | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |   |   5 | 135 |   4 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER |   |   5 | 135 |   4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| C2 |   5 | 35 |   2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| C1 |   1 | 20 |   2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("C1"."ID2"(+)="C2"."ID2")
3 - filter("C1"."ID1"(+)=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
   7 recursive calls
   0 db block gets
   15 consistent gets
   0 physical reads
   0 redo size
   747 bytes sent via SQL*Net to client
   607 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   2 sorts (memory)
   0 sorts (disk)
   5 rows processed

SQL>