SQL> create table t1 as select * from user_tables;
Table created.
SQL> create table t2 as select * from user_indexes;
Table created.
SQL> select count(*) from t1
2 ;
COUNT(*)
----------
704
SQL> select count(*) from t2;
COUNT(*)
----------
810
SQL> set autot on exp
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;
COUNT(*)
----------
787
Execution Plan
----------------------------------------------------------
Plan hash value: 446739472
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 14 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
|* 2 | HASH JOIN | | 810 | 27540 | 14 (8)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 704 | 11968 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 810 | 13770 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> create index index_tn_t1 on t1(table_name);
Index created.
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;
COUNT(*)
----------
787
Execution Plan
----------------------------------------------------------
Plan hash value: 962536480
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 34 | 11 (10)| 00:0
0:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
|
|* 2 | HASH JOIN | | 810 | 27540 | 11 (10)| 00:0
0:01 |
| 3 | INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |
| 4 | TABLE ACCESS FULL | T2 | 810 | 13770 | 7 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> create index index_tn_t2 on t2(table_name);
Index created.
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;
COUNT(*)
----------
787
Execution Plan
----------------------------------------------------------
Plan hash value: 1205552978
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 34 | 7 (15)| 00:0
0:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
|
|* 2 | HASH JOIN | | 810 | 27540 | 7 (15)| 00:0
0:01 |
| 3 | INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |
| 4 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> drop index index_tn_t1;
Index dropped.
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;
COUNT(*)
----------
787
Execution Plan
----------------------------------------------------------
Plan hash value: 1730698804
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 34 | 10 (10)| 00:0
0:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
|
|* 2 | HASH JOIN | | 810 | 27540 | 10 (10)| 00:0
0:01 |
| 3 | TABLE ACCESS FULL | T1 | 704 | 11968 | 6 (0)| 00:0
0:01 |
| 4 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> alter table t1 add constraint pk_t1 primary key (table_name);
Table altered.
SQL> select count(*) from t1,t2 where t1.table_name=t2.table_name;
COUNT(*)
----------
787
Execution Plan
----------------------------------------------------------
Plan hash value: 1260911036
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 34 | 3 (0)| 00:0
0:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | |
|
| 2 | NESTED LOOPS | | 810 | 27540 | 3 (0)| 00:0
0:01 |
| 3 | INDEX FAST FULL SCAN| INDEX_TN_T2 | 810 | 13770 | 3 (0)| 00:0
0:01 |
|* 4 | INDEX UNIQUE SCAN | PK_T1 | 1 | 17 | 0 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL>
结果:因为开始是普通index所以可能存在重复的key吧，你看一个explain的rows是700多一个是800左右。返回记录是700多，那oracle 认为你返回的基本就是记录集里的大部分记录了所以走hash jion
最后个因为你建立了唯一索引，所以oracle知道nest loop join的话out loop来驱动inner loop
而out的row只可能在inner里找到唯一记录（inner是是唯一索引返回的记录集）
所以扫描唯一索引后的rows是1
虽然前面还是800左右并返回700多的记录
row=1让 oracle认为你返回的内容是整个rowset中的小部分所以用了nest loop join
