如何使Oracle视图在添加WHERE条件时利用索引？

你有一个用于Appian低代码场景的视图VIEW_A，它包含多表LEFT JOIN但不带WHERE子句，方便开发者后续添加过滤条件。随着数据库数据量增加，查询性能明显下降，执行计划显示完全没用到索引。

视图VIEW_A定义 #

SELECT
  <columns>   (无复杂字段转换)
FROM A
LEFT JOIN R on R.id=A.id_type1
LEFT JOIN R on R.id=A.id_type2
LEFT JOIN R on R.id=A.id_type3
LEFT JOIN U on U.id=A.id_user     -- U表数据量约500
LEFT JOIN C on D.id=A.id_customer -- C表数据量约50000
LEFT JOIN P on P.id=A.id_prestati -- P表数据量约100000

Appian后续添加的WHERE条件 #

WHERE A.DATE_ACTION <  to_date('2022-10-12 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND A.DATE_ACTION >= to_date('2022-10-08 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND A.USER_ACTION = 'miwem6'

性能差异 #

执行SELECT * FROM VIEW_A WHERE <上述条件>的成本约为6000，但直接把WHERE条件嵌入视图定义里执行的成本仅为30。现在想知道：有没有Oracle提示（hint）能让数据库在后续给视图加WHERE条件时自动用上索引？

1. 可用的Oracle提示 #

可以用PUSH_PRED提示强制优化器把外部的WHERE条件下推到基表A上，触发索引使用，有两种添加方式：

• 修改视图定义：在基表A的FROM语句后加提示，这样所有查询该视图的请求都会自动应用这个逻辑：

SELECT
  <columns>
FROM /*+ PUSH_PRED(A) */ A
LEFT JOIN R on R.id=A.id_type1
LEFT JOIN R on R.id=A.id_type2
LEFT JOIN R on R.id=A.id_type3
LEFT JOIN U on U.id=A.id_user
LEFT JOIN C on D.id=A.id_customer
LEFT JOIN P on P.id=A.id_prestati

• 查询视图时加提示：如果不能修改视图定义，就在Appian生成的查询语句里加提示：

SELECT /*+ PUSH_PRED(VIEW_A) */ * FROM VIEW_A WHERE <条件>

PUSH_PRED的作用就是让优化器把外部查询的过滤条件下推到视图的基表中，这样基表A就能利用DATE_ACTION和USER_ACTION上的索引，和直接执行嵌入条件的SQL效果一致。

2. 其他优化方向 #

• 确认索引有效性：先确保基表A上存在(USER_ACTION, DATE_ACTION)的复合索引，这是索引能被使用的前提。
• 实体化视图方案：如果视图的JOIN逻辑基本固定，查询频率远高于数据更新频率，可以创建实体化视图并刷新，同时在实体化视图上创建对应索引，不过会增加存储和维护成本。
• 内联视图替代：如果Appian支持，直接用内联视图的形式（把VIEW_A的定义写在查询里再加条件），但这会失去低代码复用的便利性。

内容的提问来源于stack exchange，提问作者J. Chomel