Oracle 索引和SP性能
我正在尝试针对 Oracle 视图优化一些旧版 SQL SP，该视图由 6 个表构建，每个表都由相同的字段（数字 ID）连接。视图中的某些表具有仅此 ID 字段的索引，而其他表则没有。
如果我仅使用该字段在视图中的其余表上创建索引，然后使用该字段作为唯一参数执行实际的选择查询，是否会显着提高性能？如果有必要，我可以发布 s.proc，因为 SP 中可能存在其他缺陷，这些缺陷可能无法仅通过索引来解决。所讨论的查询大约需要 6 秒才能返回 1 行，没有一个表包含大量记录，无论如何也不超过 100,000 条记录。
预先感谢，
斯科特
Am trying to optimize some legacy SQL SPs against an Oracle view which is built from 6 tables, each joined by the same field, a numeric ID. Some of the tables in the view have an index which is solely this ID field, others do not.
If I create an index on the remaining tables in the view using this field only, and then perform the actual select query using this field as the sole parameter, will it improve performance notably? I can post the s.proc if its necessary as there may be other flaws in the SP which may not be solved by indexing alone. The query in question takes around 6 seconds to return 1 row, none of the tables contain a large amount of records, nothing over 100,000 records anyway.
Thanks in advance,
Scott
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
确保视图中的每个表都有一个以 id 字段开头的索引。索引可以包含更多字段，只要 id 字段在前即可。
如果这对性能没有帮助，请发布选择语句和解释计划。
Make sure every table in the view has an index that starts with the id field. The index can conatain more fields as long as the id field is first.
If that doesn't help performance please post the select statement and the explain plan.
如果 ID 字段是索引中的第一列（或唯一列），则在返回少量行时，将索引添加到需要它们的其余表的 ID 列将改进查询。
If the ID field is the first column in the indexes (or the only column) then adding indexes to the ID column of those remaining tables that need them will improve the query if you are returning a small number of rows.