表与表之间错误的连接方式会对性能产生巨大影响。
[@more@]SQL> create table t(id int , name char(10));
表已创建。
SQL> create table tt(id int , name char(2000));
表已创建。
SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i,'test');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10000 loop
3 insert into tt values(i,'test1');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats('TEST','T');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats('TEST','TT');
PL/SQL 过程已成功完成。
SQL>
SQL> set autotrace traceonly
SQL> set time on
22:19:10 SQL> select t.id,tt.name from t , tt where t.id=tt.id;
已选择10000行。
执行计划
----------------------------------------------------------
Plan hash value: 2424494595
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 755 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 10000 | 19M| 755 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4069 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
22:19:17 SQL> select /*+ use_hash(t tt) */ t.id,tt.name from t , tt where t.id=t
t.id;
已选择10000行。
执行计划
----------------------------------------------------------
Plan hash value: 2424494595
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 755 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 10000 | 19M| 755 (1)| 00:00:10 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4069 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
22:20:07 SQL>
22:20:59 SQL> select /*+ use_hash(t tt) leading(tt) */ t.id,tt.name from t , tt
where t.id=tt.id;
已选择10000行。
执行计划
----------------------------------------------------------
Plan hash value: 3792434616
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 10000 | 19M| | 1717 (1)| 00:00:2
1 |
|* 1 | HASH JOIN | | 10000 | 19M| 19M| 1717 (1)| 00:00:2
1 |
| 2 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:0
9 |
| 3 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
3527 consistent gets
2821 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
22:21:17 SQL>
22:23:40 SQL> select /*+ use_nl(t tt) */ t.id,tt.name from t , tt where t.id=tt.
id;
已选择10000行。
执行计划
----------------------------------------------------------
Plan hash value: 2329402909
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 63237 (2)| 00:12:39 |
| 1 | NESTED LOOPS | | 10000 | 19M| 63237 (2)| 00:12:39 |
| 2 | TABLE ACCESS FULL| TT | 10000 | 19M| 746 (1)| 00:00:09 |
|* 3 | TABLE ACCESS FULL| T | 1 | 4 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
314705 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
22:24:09 SQL>
22:24:09 SQL> select /*+ use_nl(t tt) leading(t) */ t.id,tt.name from t , tt whe
re t.id=tt.id;
已选择10000行。
执行计划
----------------------------------------------------------
Plan hash value: 3115954764
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19M| 7443K (1)| 24:48:40 |
| 1 | NESTED LOOPS | | 10000 | 19M| 7443K (1)| 24:48:40 |
| 2 | TABLE ACCESS FULL| T | 10000 | 40000 | 8 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TT | 1 | 2004 | 744 (1)| 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
33711291 consistent gets
0 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
22:25:48 SQL>
22:38:00 SQL> set timing on
22:38:12 SQL> select /*+ use_merge(t tt) */ t.id,tt.name from t , tt where t.id=
tt.id;
已选择10000行。
已用时间: 00: 00: 09.10
执行计划
----------------------------------------------------------
Plan hash value: 10480094
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 2 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:
01 |
| 3 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:
01 |
|* 4 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:
00 |
| 5 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:
09 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
21 recursive calls
15 db block gets
3402 consistent gets
7797 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10000 rows processed
22:38:23 SQL>
SQL> connect/ as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 10月 13 22:43:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set autotrace traceonly
SQL> set timing on
SQL> select /*+ use_merge(t tt) leading(tt) */ t.id,tt.name from t , tt where t.
id=tt.id;
已选择10000行。
已用时间: 00: 00: 06.98
执行计划
----------------------------------------------------------
Plan hash value: 4287342720
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 2 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:
00 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:
09 |
|* 4 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:
01 |
| 5 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
470 recursive calls
7 db block gets
3457 consistent gets
5875 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
10000 rows processed
SQL>
SQL> select /*+ use_merge(t tt) leading(tt) */ t.id,tt.name from t , tt where t.
id=tt.id;
已选择10000行。
已用时间: 00: 00: 05.35
执行计划
----------------------------------------------------------
Plan hash value: 4287342720
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 1 | MERGE JOIN | | 10000 | 19M| | 4975 (1)| 00:01:
00 |
| 2 | SORT JOIN | | 10000 | 19M| 39M| 4934 (1)| 00:01:
00 |
| 3 | TABLE ACCESS FULL| TT | 10000 | 19M| | 746 (1)| 00:00:
09 |
|* 4 | SORT JOIN | | 10000 | 40000 | 248K| 41 (5)| 00:00:
01 |
| 5 | TABLE ACCESS FULL| T | 10000 | 40000 | | 8 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."ID"="TT"."ID")
filter("T"."ID"="TT"."ID")
统计信息
----------------------------------------------------------
20 recursive calls
7 db block gets
3402 consistent gets
2480 physical reads
0 redo size
176872 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10000 rows processed
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/19602/viewspace-1011847/ ，如需转载，请注明出处，否则将追究法律责任。
