Oracle表三种连接方式(sql优化）(二)
me = b.table_name;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 3579965632
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 280 | 4 (0)| 00:00:01
| 1 | NESTED LOOPS | | 8 | 280 | 4 (0)| 00:00:01
| 2 | INDEX FAST FULL SCAN| INDEX_T | 1921 | 34578 | 4 (0)| 00:00:01
|* 3 | INDEX RANGE SCAN | INDEX_T1 | 1 | 17 | 0 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
------------------------------------------------------
假定我们利用提示改变的表的连接顺序
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
SQL> select /*+ordered */ a.table_name,b.table_name from t1 a,t b
2 where a.table_name=b.table_name;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1123105028
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 280 | 6 (17)| 00:00:
|* 1 | HASH JOIN | | 8 | 280 | 6 (17)| 00:00:
| 2 | INDEX FULL SCAN | INDEX_T1 | 8 | 136 | 1 (0)| 00:00:
| 3 | INDEX FAST FULL SCAN| INDEX_T | 1921 | 34578 | 4 (0)| 00:00:
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
2. HASH JOIN
hash join是CBO 做大数据集连接时的常用方式