索引组织表(IOT)与复合索引选型：CROSSWALK表查询场景分析

你创建的表结构如下：

Create table CROSSWALK
(
OtherTable_ID NUMBER(20) NOT NULL,
OtherTableName VARCHAR(20) NOT NULL,
MyTable_ID NUMBER(20) NOT NULL,
MyTableTYPE VARCHAR(20) NOT NULL,
PRIMARY KEY (OtherTable_ID, OtherTableName,MyTable_ID,MyTableTYPE)
)

结合你的查询场景——仅按「OtherTable_ID+OtherTableName」或「MyTable_ID+MyTableTYPE」单独查询，从不使用完整复合主键查询，给出以下建议：

不建议使用索引组织表(IOT) #

IOT的核心是将表数据与主键索引绑定存储，数据的物理顺序完全遵循主键的排序规则。你的主键顺序是OtherTable_ID, OtherTableName, MyTable_ID, MyTableTYPE，这仅能优化以Other开头的查询；但当你按MyTable_ID+MyTableTYPE查询时，由于这两个字段在主键顺序的末尾，无法利用主键索引快速定位数据，相当于做全表扫描，查询效率会非常差，完全不匹配你的双向查询需求。

推荐方案：创建两个针对性的复合覆盖索引 #

1. 针对「OtherTable_ID+OtherTableName」的查询，创建复合索引并覆盖需要返回的字段：

   CREATE INDEX idx_crosswalk_other ON CROSSWALK(OtherTable_ID, OtherTableName) INCLUDE (MyTable_ID, MyTableTYPE);
   

   这样查询时直接从索引就能获取结果，不需要回表查询原数据，效率更高。

2. 针对「MyTable_ID+MyTableTYPE」的查询，同样创建复合覆盖索引：

   CREATE INDEX idx_crosswalk_my ON CROSSWALK(MyTable_ID, MyTableTYPE) INCLUDE (OtherTable_ID, OtherTableName);
   

   确保双向查询都能利用索引快速获取数据。

另外，原有的复合主键可以保留，它的作用是保证表中不会出现重复的关联记录，维护数据的唯一性。

内容的提问来源于stack exchange，提问作者coloradoman