不同的連線方式效能對比！
表與表之間錯誤的連線方式會對效能產生巨大影響。
[@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部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1011847/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 3種主要表連線方式對比
- merge into三種表連線方式的效能比較(一)
- Redis 不同插入方法的效能對比Redis
- 不同Framework下StringBuilder和String的效能對比，及不同Framework效能比（附Demo）FrameworkUI
- JavaScript 字串連線效能比較JavaScript字串
- 資料泵不同工作方式效能比較（六）
- 資料泵不同工作方式效能比較（五）
- 資料泵不同工作方式效能比較（四）
- 資料泵不同工作方式效能比較（三）
- 資料泵不同工作方式效能比較（二）
- 資料泵不同工作方式效能比較（一）
- Laravel redis 連不同的連線LaravelRedis
- Java深度拷貝方式和效能對比Java
- 【SQL 效能優化】表的三種連線方式SQL優化
- 幀動畫的多種實現方式與效能對比動畫
- 【SQL 效能最佳化】表的三種連線方式SQL
- 各種表連線方式對比分析
- mysql 的連線方式MySql
- 輪詢、長輪詢、短連線、長連線區別對比
- 深度人臉識別中不同損失函式的效能對比函式
- py連線mysql常用驅動的兩種對比MySql
- insert的不同場景效能比較
- 遊標資料不同方式讀取、提交效能對比分析
- hive表連線和oracle測試對比HiveOracle
- C#例項化物件的三種方式及效能對比C#物件
- ORACLE 連線方式Oracle
- Oracle連線方式Oracle
- 最近論壇對我比較有用的連線歸類
- iOS中保證執行緒安全的幾種方式與效能對比iOS執行緒
- Kubernetes 幾種儲存方式效能對比 （轉載）
- 不同平臺上mysql的對比(轉)MySql
- java排序方式對比Java排序
- Oracle 的 hash join連線方式Oracle
- proxool連線池如何使用SSL方式連線？
- ORACLE 表連線方式Oracle
- oracle表連線方式Oracle
- exists和連線方式
- Oracle 表連線方式詳解（外連結、內連線、自連線）Oracle