为什么嵌套视图的解释计划与单个合并视图不同？
我有一个视图 V2，它从另一个视图 V1 中进行选择，并添加了几个谓词过滤器。
V2 IS SELECT * FROM V1
WHERE ACTIVE='Y'
AND TYPE = '1';
当我在 V2 和表 FOO 上的列 USER_ID（在 FOO 中索引）上进行联接时，我发现它首先计算 V2 的整个结果，然后再与索引列上的 FOO 连接（也在 V1 的源表中建立索引）。
SELECT * FROM FOO
INNER JOIN V2
ON FOO.USER_ID = V2.USER_ID
WHERE FOO.SCHOOL = '3'
但是，当我在 V1 和 FOO 之间进行联接时，它首先找到 FOO 中的记录，然后使用索引快速检索 FOO 中的行code>V1 使用谓词推送 &索引。
我已将差异缩小到以下事实：V2 是通过谓词构建在 V1 之上的。我将 V2 更改为 V1 的精确副本，但直接将两个附加谓词过滤器放入其中，发现与 FOO< 连接时它的行为方式相同/code> V1 做到了，但现在有了适当的限制。
嵌套视图是否无法将谓词一直推到表中？另外，谓词推送的一致性如何？我有几种观点，如果不推送谓词，就会破坏性能。他们现在工作得很好，但有什么保证他们会继续这样做呢？
我想嵌套/继承视图，而不是重复具有细微差别的完全相同的查询，以保持事物干燥。
I have a view V2 that selects from another view V1 and adds a couple predicate filters.
V2 IS SELECT * FROM V1
WHERE ACTIVE='Y'
AND TYPE = '1';
When I do a join between V2 and a table FOO on the column USER_ID (indexed in FOO) I find that it first calculates the entire results for V2 before joining with FOO on the indexed column (indexed also in the source table of V1).
SELECT * FROM FOO
INNER JOIN V2
ON FOO.USER_ID = V2.USER_ID
WHERE FOO.SCHOOL = '3'
But when I do a join between V1 and FOO it finds the record in FOO first and then uses the index to quickly retrieve the row from V1 using predicate pushing & indexes.
I have narrowed down the difference to the fact that V2 is built on top of V1 with predicates. I changed V2 to be an exact copy of V1 but put the two additional predicate filters in it directly and found that it behaved the same way when joined with FOO that V1 did but with the proper restrictions in place now.
Are nested views unable to push predicates all the way down to the tables? Also, how consistent is predicate pushing? I have several views where if predicates are not pushed it would ruin performance. They work fine now but what guarantee is there they will continue to do so?
I'd like to nest/inherit views rather than duplicating the exact same queries with minor differences to keep things DRY.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您使用什么版本的 Oracle？
参数OPTIMIZER_SECURE_VIEW_MERGING设置为什么？您可以尝试将其设置为 FALSE 或授予用户 MERGE ANY VIEW 权限。
如果这没有帮助，您可以发布两个查询计划吗？
What version of Oracle are you using?
What is the parameter OPTIMIZER_SECURE_VIEW_MERGING set to? You may try setting that to FALSE or granting the user the MERGE ANY VIEW privilege.
If that doesn't help, can you post the two query plans?