提示oracle在子查询上使用索引——Oracle SQl
我有一个查询如下
select *
from
( select id,sum(amt) amt from table_t group by id
) t inner join table_v v on (v.id = t.id)
order by t.amt desc;
table_t 没有索引，有 738,000 行，table_v 在 id 上有索引，有 158,000 行。
目前，该查询将在 10 秒内获取结果。
解释查询计划显示全表扫描。如何提高这里的性能？
如果我在 table_t 的 id 上添加索引会有帮助。因为我在子查询中使用它？
I have a query as follows
select *
from
( select id,sum(amt) amt from table_t group by id
) t inner join table_v v on (v.id = t.id)
order by t.amt desc;
table_t has no index and has 738,000 rows and table_v has an index on id and has 158,000 rows.
The query currently fetches the results in 10 seconds.
The explain query plan shows a full table scan.. How can I improve the performance here ?
If I add an index on id for table_t will it help. Because I am using it in a subquery ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您在
(id,amt)上有索引，您将最大限度地减少分组/求和过程中的工作（因为它可以读取索引）。如果两列都可以为空，那么您可能需要添加“where id is not null”，以便它将使用索引。 [这是由id上的稍后连接暗示的，但优化器可能无法推断出这一点。]下一步是使用物化视图进行求和，可能在
(amt ,id)（它可以用来避免排序）。但它会在提交时、根据请求或按计划的时间间隔刷新。如果您需要在事务中执行此查询，那么它没有帮助。索引和物化视图都会增加表上的插入/更新/删除工作，但会节省此查询中的工作。
If you have an index on
(id,amt)you would minimise the work in the group by/summation process (as it could read the index). If both columns are nullable then you may need to add a "where id is not null" so it will use the index. [That's implied by the later join onid, but may not get inferred by the optimizer.]Next step would be to use a materialized view for the summation, maybe with an index on
(amt,id)(which it could use to avoid the sort). But that is refreshed either at a commit or on request or at scheduled intervals. It doesn't help if you need to do this query as part of a transaction.Both the index and the materialized view would add work to inserts/updates/deletes on the table but save work in this query.