oracle rownum left join,oracle外连接 (+)和left join 有效率区别吗

SQL> create table t1 as select * from all_objects;

SQL> create table t2 as select * from all_objects where rownum<=10000;

SQL> set autotrace on;

SQL> select count(1)

2  from t1 a

3  left join t2 b on a.object_id=b.object_id

4  ;

COUNT(1)

----------

40705

执行计划

----------------------------------------------------------

Plan hash value: 3306442619

-------------------------------------------------------------------------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     1 |    26 |   168   (3)| 00:00:03 |

|   1 |  SORT AGGREGATE        |      |     1 |    26 |            |          |

|*  2 |   HASH JOIN RIGHT OUTER|      | 41866 |  1063K|   168   (3)| 00:00:03 |

|   3 |    TABLE ACCESS FULL   | T2   | 10907 |   138K|    35   (3)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | T1   | 41866 |   531K|   132   (2)| 00:00:02 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

712  consistent gets

0  physical reads

0  redo size

410  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select count(1)

2  from t1 a,t2 b

3  where  a.object_id=b.object_id(+)

4  ;

COUNT(1)

----------

40705

执行计划

----------------------------------------------------------

Plan hash value: 3306442619

-------------------------------------------------------------------------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     1 |    26 |   168   (3)| 00:00:03 |

|   1 |  SORT AGGREGATE        |      |     1 |    26 |            |          |

|*  2 |   HASH JOIN RIGHT OUTER|      | 41866 |  1063K|   168   (3)| 00:00:03 |

|   3 |    TABLE ACCESS FULL   | T2   | 10907 |   138K|    35   (3)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | T1   | 41866 |   531K|   132   (2)| 00:00:02 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

712  consistent gets

0  physical reads

0  redo size

410  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>