我的查询存在性能问题。请提出建议
MERGE INTO Analysis a
USING ( SELECT * FROM Data ) b
ON ( a.User_Id = b.User_Id AND a.Upgrade_Flag = 0 )
WHEN MATCHED THEN
UPDATE SET Status = NVL(a.Status, 'ACTIVATE');
上面的查询工作正常。但是当存在数百万条记录时，这可能会导致性能问题。上面的查询是否有任何替代方案以获得更好的性能。请对此提出建议。感谢您的时间
MERGE INTO Analysis a
USING ( SELECT * FROM Data ) b
ON ( a.User_Id = b.User_Id AND a.Upgrade_Flag = 0 )
WHEN MATCHED THEN
UPDATE SET Status = NVL(a.Status, 'ACTIVATE');
The above query works fine.But when millions of records are present this may lead to performance issue.Is there any alternative for the above query to get a better performace.Please suggest me on this.Thanks for your time
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果没有看到执行计划，我无法预测替代方案是否会更有效。但我注意到您没有在更新中使用合并源，这表明这可以重写为一个简单的更新语句：
与这些事情的情况一样，您可以选择使用
IN子句，如我所示，或带有相关子查询的EXISTS子句。通常，在尝试调整性能时值得测试这两个选项，尽管至少在某些情况下优化器会自行尝试该转换。Without seeing the execution plans I can't predict whether an alternative would be any more efficient. But I note that you are not using the merge source in the update, which indicates that this could rewritten as a simple update statement:
As is always the case with these things, you have a choice between using an
INclause, as I have shown, or anEXISTSclause with a correlated subquery. Usually it's worth testing both options when trying to tune performance, although in at least some cases the optimizer will try that transformation on its own.这个查询对我来说看起来很好。也许你应该创建一些索引？
如果尚未创建，则为 Analysis.User_Id 创建索引，为 Data.User_Id 创建索引（主键自动创建索引）。
或者也可以为包含 User_Id 和 Upgrade_Flag 列的 Analysis 创建索引。
The query looks fine to me. Maybe you should create some indexes?
Create index for Analysis.User_Id and index for Data.User_Id if not yet created (primary key creates index automatically).
Or maybe also create index for Analysis containing both columns User_Id and Upgrade_Flag.
为什么使用 MERGE 而不是简单的 UPDATE 真的一点也不明显。
假设 DATA.USER_ID 已建立索引，则仅从 DATA 中选择 USER_ID 而不是整个记录可以加快速度。
It's really not at all obvious why you are using MERGE instead of a simple UPDATE.
Selecting only the USER_ID from DATA instead of the entire record could speed things up, presuming DATA.USER_ID is indexed.