SQL> create table tab_big as select * from dba_objects where rownum<=30000;
create table tab_small as select * from dba_objects where rownum<=10;
Table created.
Elapsed: 00:00:00.34
SQL>
Table created.
Elapsed: 00:00:00.06
SQL> set autotrace traceonly
set linesize 1000
set timing on SQL> SQL>
SQL>
SQL> select count(*) from tab_big,tab_small where tab_big.object_id=tab_small.object_id;
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_BIG"."OBJECT_ID"="TAB_SMALL"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
497 consistent gets
422 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_BIG"."OBJECT_ID"="TAB_SMALL"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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(*) from tab_big,tab_small where tab_small.object_id=tab_big.object_id;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_SMALL"."OBJECT_ID"="TAB_BIG"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
491 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_SMALL"."OBJECT_ID"="TAB_BIG"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+rule*/ count(*) from tab_big,tab_small ;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_SMALL |
| 4 | TABLE ACCESS FULL| TAB_BIG |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4213 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_SMALL |
| 4 | TABLE ACCESS FULL| TAB_BIG |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4213 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+rule*/ count(*) from tab_small,tab_big ;
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 2127005654
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_BIG |
| 4 | TABLE ACCESS FULL| TAB_SMALL |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
90421 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 2127005654
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_BIG |
| 4 | TABLE ACCESS FULL| TAB_SMALL |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
90421 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----连接方式 嵌套循环连接(NL)
SQL> select /*+USE_NL(tab_big) */ count(*) from tab_big,tab_small
2 ;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1179 (1)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | NESTED LOOPS | | 287K| 1179 (1)| 00:00:15 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 118 (1)| 00:00:02 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4280 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+USE_NL(tab_small) */ count(*) from tab_big,tab_small ;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 177389953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1179 (1)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN| | 287K| 1179 (1)| 00:00:15 |
| 3 | TABLE ACCESS FULL | TAB_SMALL | 10 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 28743 | 1176 (1)| 00:00:15 |
| 5 | TABLE ACCESS FULL | TAB_BIG | 28743 | 118 (1)| 00:00:02 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
491 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
结论:原来表连接顺序的说法早就过时了,那是基于规则的时代,现在我们是基于代价的。hash 连接方式要比nl 、 MERGE要好
create table tab_small as select * from dba_objects where rownum<=10;
Table created.
Elapsed: 00:00:00.34
SQL>
Table created.
Elapsed: 00:00:00.06
SQL> set autotrace traceonly
set linesize 1000
set timing on SQL> SQL>
SQL>
SQL> select count(*) from tab_big,tab_small where tab_big.object_id=tab_small.object_id;
Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_BIG"."OBJECT_ID"="TAB_SMALL"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
497 consistent gets
422 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_BIG"."OBJECT_ID"="TAB_SMALL"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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(*) from tab_big,tab_small where tab_small.object_id=tab_big.object_id;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_SMALL"."OBJECT_ID"="TAB_BIG"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
491 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3126178707
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 122 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10 | 260 | 122 (0)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 130 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 364K| 119 (0)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TAB_SMALL"."OBJECT_ID"="TAB_BIG"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+rule*/ count(*) from tab_big,tab_small ;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_SMALL |
| 4 | TABLE ACCESS FULL| TAB_BIG |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4213 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_SMALL |
| 4 | TABLE ACCESS FULL| TAB_BIG |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4213 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+rule*/ count(*) from tab_small,tab_big ;
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 2127005654
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_BIG |
| 4 | TABLE ACCESS FULL| TAB_SMALL |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
90421 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 2127005654
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| TAB_BIG |
| 4 | TABLE ACCESS FULL| TAB_SMALL |
-----------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
90421 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-----连接方式 嵌套循环连接(NL)
SQL> select /*+USE_NL(tab_big) */ count(*) from tab_big,tab_small
2 ;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 41936691
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1179 (1)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | NESTED LOOPS | | 287K| 1179 (1)| 00:00:15 |
| 3 | TABLE ACCESS FULL| TAB_SMALL | 10 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TAB_BIG | 28743 | 118 (1)| 00:00:02 |
-------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
4280 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 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 /*+USE_NL(tab_small) */ count(*) from tab_big,tab_small ;
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 177389953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1179 (1)| 00:00:15 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN| | 287K| 1179 (1)| 00:00:15 |
| 3 | TABLE ACCESS FULL | TAB_SMALL | 10 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 28743 | 1176 (1)| 00:00:15 |
| 5 | TABLE ACCESS FULL | TAB_BIG | 28743 | 118 (1)| 00:00:02 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
491 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
结论:原来表连接顺序的说法早就过时了,那是基于规则的时代,现在我们是基于代价的。hash 连接方式要比nl 、 MERGE要好
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30345407/viewspace-2150361/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30345407/viewspace-2150361/