Oracle Table连接方式分析
12:16:16 SYS@ prod>create table sgtb as select * from dba_segments where owner='SYS';Table created.
Elapsed: 00:00:00.73
12:17:05 SYS@ prod>create table obtb as select * from dba_objects where owner='SYS';
Table created.
Elapsed: 00:00:01.02
12:17:30 SYS@ prod>SELECT count(*) from sgtb;
COUNT(*)
----------
2312
Elapsed: 00:00:00.02
12:17:41 SYS@ prod>SELECT count(*) from obtb;
COUNT(*)
----------
30928
Elapsed: 00:00:00.04
12:17:51 SYS@ prod>
12:17:51 SYS@ prod>create index seg_name_ind on sgtb (segment_name);
Index created.
Elapsed: 00:00:00.27
12:19:00 SYS@ prod>create index ob_name_ind on obtb(object_name);
Index created.
Elapsed: 00:00:00.32
12:19:29 SYS@ prod>
12:19:29 SYS@ prod>exec dbms_stats.gather_table_stats(user,'SGTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
12:20:49 SYS@ prod>exec dbms_stats.gather_table_stats(user,'OBTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.33
HASH JOIN:
12:21:03 SYS@ prod>SET autotrace trace
12:21:32 SYS@ prod>select * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1028776806
---------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2401 | 515K| 134 (1)| 00:00:02 |
|*1 |HASH JOIN | |2401 | 515K| 134 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL| SGTB |2312 | 279K| 13 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OBTB | 30928 |2899K| 121 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
650consistent gets
0physical reads
0redo size
223156bytes sent via SQL*Net to client
2371bytes received via SQL*Net from client
170SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2528rows processed
NETSTED LOOP:
12:22:41 SYS@ prod>select /*+ use_nl(a b) */ * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2080873268
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2401 | 515K|4638 (1)| 00:00:56 |
| 1 |NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | |2401 | 515K|4638 (1)| 00:00:56 |
| 3 | TABLE ACCESS FULL | SGTB |2312 | 279K| 13 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | OB_NAME_IND | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| OBTB | 1 | 96 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
3065consistent gets
0physical reads
0redo size
213135bytes sent via SQL*Net to client
2371bytes received via SQL*Net from client
170SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
2528rows processed
SORT MERGE JOIN:
12:24:30 SYS@ prod>select /*+ use_merge(a b) */ * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 2191280214
------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |2401 | 515K| | 883 (1)| 00:00:11 |
| 1 |MERGE JOIN | |2401 | 515K| | 883 (1)| 00:00:11 |
| 2 | SORT JOIN | |2312 | 279K| 840K| 80 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| SGTB |2312 | 279K| | 13 (0)| 00:00:01 |
|*4 | SORT JOIN | | 30928 |2899K|8136K| 803 (1)| 00:00:10 |
| 5 | TABLE ACCESS FULL| OBTB | 30928 |2899K| | 121 (1)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
filter("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
485consistent gets
0physical reads
0redo size
235884bytes sent via SQL*Net to client
2371bytes received via SQL*Net from client
170SQL*Net roundtrips to/from client
2sorts (memory)
0sorts (disk)
2528rows processed
页:
[1]