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

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

SQL> DROP TABLE d;Table droppedSQL> DROP TABLE c;Table droppedSQL> DROP TABLE b;Table droppedSQL> DROP TABLE a;Table droppedSQL> create table a as select level as a1 from dual connect by level <=10;Table createdSQL> alter table a add constraint pk_a primary key(a1);Table alteredSQL> create table b as select a1 from a;Table createdSQL> alter table b add constraint pk_b primary key(a1);Table alteredSQL> alter table b add constraint fk_b foreign key(a1) references a(a1);Table alteredSQL> create table c as select a1 from a;Table createdSQL> alter table c add constraint pk_c primary key(a1);Table alteredSQL> alter table c add constraint fk_c foreign key(a1) references b(a1);Table alteredSQL> create table d as select a1 from a;Table createdSQL> alter table d add constraint pk_d primary key(a1);Table alteredSQL> alter table d add constraint fk_d foreign key(a1) references c(a1);Table alteredSQL> 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;ExplainedSQL> 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 selectedSQL> 

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