2.使用非唯一索引的嵌套连接的执行计划该如何理解?
测试数据与1中描述的一样。
查询语句：
select /*+ index(t2) */ c1,c2,d1 from t1 inner join t2 on c1=d1;
对应的执行计划：
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (RANGE SCAN) OF 'IT2D1' (INDEX) (Cost=1 Card=1 Byt
es=13)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (RANGE SCAN) OF 'IT2D1' (INDEX) (Cost=1 Card=1 Byt
es=13)
使用explain plan对应的执行计划：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2841753667
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IT2D1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C1"="D1") //此处的access表示使用值3去命中索引IT2D1对应的B树。
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
对于该查询语句的执行，如果用代码可以描述成这样：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 in t2’s first record that match c1=d1; d1=c1; rec2=rec->next)
{
put result(rec1.c1,rec1.c2，rec2.d1) into result set;
}
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2841753667
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IT2D1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C1"="D1") //此处的access表示使用值3去命中索引IT2D1对应的B树。
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
对于该查询语句的执行，如果用代码可以描述成这样：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 in t2’s first record that match c1=d1; d1=c1; rec2=rec->next)
{
put result(rec1.c1,rec1.c2，rec2.d1) into result set;
}
