SQL执行计划优化问题：强制索引仍无法避免TABLE ACCESS FULL
我遇到一个棘手的问题：想在查询执行计划里避免TABLE ACCESS FULL，但即使加了/* index( ) */强制索引提示也完全没效果。
我的查询语句如下：
SELECT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux FROM stockage st INNER JOIN allotissement_flux af ON EXISTS ( SELECT * FROM signature sig WHERE st.id_flux = sig.id_flux AND af.ID = sig.id_flux AND sig.statut_signature = 'SIGNE' AND sig.nb_appel_service_signature < 4 AND sig.date_statut_signature >= sysdate - 1000 ) WHERE st.statut_stockage = 'OUI' AND st.date_statut_stockage >= sysdate - 1000
我已经给每个表的每个字段都单独建了索引，但当前执行计划还是全表扫描：
Plan hash value: 2782848463 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 40M | 8376M | | 1594K (1) | 00:01:03 | |* 1 | HASH JOIN | | 40M | 8376M | 4284M | 1594K (1) | 00:01:03 | |* 2 | HASH JOIN | | 40M | 3821M | 1505M | 543K (1) | 00:00:22 | | 3 | SORT UNIQUE | | 40M | 1042M | | 146K (1) | 00:00:06 | |* 4 | TABLE ACCESS FULL | SIGNATURE | 40M | 1042M | | 146K (1) | 00:00:06 | |* 5 | TABLE ACCESS FULL | STOCKAGE | 48M | 3322M | | 130K (2) | 00:00:06 | | 6 | TABLE ACCESS FULL | ALLOTISSEMENT_FLUX | 49M | 5527M | | 536K (1) | 00:00:21 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("AF"."ID"="SIG"."ID_FLUX") 2 - access("ST"."ID_FLUX"="SIG"."ID_FLUX") 4 - filter("SIG"."NB_APPEL_SERVICE_SIGNATURE"<4 AND "SIG"."STATUT_SIGNATURE"='SIGNE' AND "SIG"."DATE_STATUT_SIGNATURE">=SYSDATE@!-1000) 5 - filter("ST"."STATUT_STOCKAGE"='OUI' AND "ST"."DATE_STATUT_STOCKAGE">=SYSDATE@!-1000)
另外还存在Table access rowid无法生效的问题，想请教各位怎么解决？
1. 索引提示语法错误，根本没生效
你写的/* index( ) */是无效的——Oracle的提示语法需要加+号，而且必须明确指定表别名和索引名称，否则优化器根本识别不到。正确的写法应该是这样（假设signature表的复合索引叫idx_sig_combined）：
SELECT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux FROM stockage st INNER JOIN allotissement_flux af ON EXISTS ( SELECT * FROM signature sig /*+ index(sig idx_sig_combined) */ WHERE st.id_flux = sig.id_flux AND af.ID = sig.id_flux AND sig.statut_signature = 'SIGNE' AND sig.nb_appel_service_signature < 4 AND sig.date_statut_signature >= sysdate - 1000 ) WHERE st.statut_stockage = 'OUI' AND st.date_statut_stockage >= sysdate - 1000
2. 数据量太大，全表扫描反而更高效
从执行计划看，signature表返回了40M行，stockage返回48M行，allotissement_flux返回49M行——这些数据量几乎接近全表了。当过滤条件（比如sysdate-1000）返回的行数超过全表的10%-20%时，Oracle会认为全表扫描的成本比走索引更低（因为索引需要回表取数据，IO成本更高），所以就算你加了正确的提示，它也可能忽略。
这种情况下，你需要重新评估业务需求：是否真的需要查询1000天内的所有数据？如果可以缩小时间范围，过滤后的行数减少，索引自然会被优化器优先选择。
3. 单字段索引没用，要建复合覆盖索引
你给每个字段单独建了索引，但这种单字段索引在多条件过滤+关联的场景下几乎没用。比如signature表的过滤条件是statut_signature='SIGNE'、nb_appel_service_signature<4、date_statut_signature>=sysdate-1000，还要关联id_flux，你应该建一个复合覆盖索引：
CREATE INDEX idx_sig_combined ON signature (statut_signature, nb_appel_service_signature, date_statut_signature, id_flux);
这个索引包含了所有过滤条件和关联字段，优化器可以直接通过索引获取需要的数据，不需要回表（也就不会出现TABLE ACCESS ROWID的需求了）。
同理，stockage表可以建复合索引：
CREATE INDEX idx_stock_combined ON stockage (statut_stockage, date_statut_stockage, id_flux);
4. 统计信息过时，优化器判断错误
如果你的表数据有大量插入/更新，但很久没更新统计信息，Oracle会基于旧的统计数据判断执行计划，导致错误选择全表扫描。你可以手动更新统计信息：
-- 更新单表统计信息 DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'SIGNATURE', CASCADE => TRUE); DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'STOCKAGE', CASCADE => TRUE); DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'ALLOTISSEMENT_FLUX', CASCADE => TRUE);
5. 子查询写法可以优化，让优化器更好识别索引
把EXISTS子查询改成直接JOIN的形式，可能让优化器更容易评估索引的使用价值：
SELECT DISTINCT af.ID, af.nom_flux, st.chemin_stockage, af.hash_flux FROM stockage st JOIN signature sig ON st.id_flux = sig.id_flux JOIN allotissement_flux af ON af.ID = sig.id_flux WHERE sig.statut_signature = 'SIGNE' AND sig.nb_appel_service_signature < 4 AND sig.date_statut_signature >= sysdate - 1000 AND st.statut_stockage = 'OUI' AND st.date_statut_stockage >= sysdate - 1000;
这里用DISTINCT代替原来EXISTS的去重逻辑，优化器可能会选择走上面建的复合索引。
内容的提问来源于stack exchange，提问作者Ipromise
