外键在索引中的用处（二）

在上一个问题：外键在索引中的用处（一）中，外键的消除表功能只能消掉一个，我们暂且理解为表中引用了非外键列之外的值，于是这次改为各表都只含一列，作实验如下：

SQL> DROP TABLE d;
Table dropped
SQL> DROP TABLE c;
Table dropped
SQL> DROP TABLE b;
Table dropped
SQL> DROP TABLE a;
Table dropped
SQL> create table a as select level as a1 from dual connect by level <=10;
Table created
SQL> alter table a add constraint pk_a primary key(a1);
Table altered
SQL> create table b as select a1 from a;
Table created
SQL> alter table b add constraint pk_b primary key(a1);
Table altered
SQL> alter table b add constraint fk_b foreign key(a1) references a(a1);
Table altered
SQL> create table c as select a1 from a;
Table created
SQL> alter table c add constraint pk_c primary key(a1);
Table altered
SQL> alter table c add constraint fk_c foreign key(a1) references b(a1);
Table altered
SQL> create table d as select a1 from a;
Table created
SQL> alter table d add constraint pk_d primary key(a1);
Table altered
SQL> alter table d add constraint fk_d foreign key(a1) references c(a1);
Table altered
SQL> explain plan for select d.a1 FROM d INNER JOIN c ON c.a1 = d.a1 inner join b on b.a1 = c.a1 inner join a on a.a1 = b.a1;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3346118423
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   260 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    10 |   260 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | PK_D |    10 |   130 |     1   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_B |     1 |    13 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."A1"="D"."A1")
Note
-----
   - dynamic sampling used for this statement (level=2)
19 rows selected

SQL> 

形成了一个更有意思的plan，这儿消掉了表C与表A，而条件也直接变为了 3 - access("B"."A1"="D"."A1")