openquery 的订单提示？
我需要定期在计划作业中执行以下 SQL (SQL Server 2008)。查询计划显示，从 Oracle 服务器提取数据后，排序 的成本为 53%。不过，我已经在 openquery 中订购了数据。如何强制查询在合并连接时不排序？
merge target as t
using (select * from openquery(oracle, '
select * from t1 where UpdateTime > ''....'' order by k1, k2')
) as s on s.k1=t.k1 and s.k2=t.K2 -- the clustered PK of "target" is K1,k2
when matched then ......
when not matched then ......
是否有类似批量插入的“with (order( { column [ ASC | DESC ] } [ ,...n ] ))”？如果存在的话，是否有助于改进merge语句的查询计划？
如果oracle表已经在K1，K2上有PK，那么使用oracle.db.owner.tablename作为目标会更好吗？ （SQL Server会从oracle元信息中找出索引吗？）
或者我能做的最好的事情就是将oracle数据存储在本地临时表中并在K1，k2上创建聚集主键？我试图避免创建临时表，因为有时返回的 openquery 数据集可能很大。
I need to execute the following SQL (SQL Server 2008) in a scheduled job periodically. The Query plan shows 53% cost is sort after the data is pulled from the oracle server. However, I've ordered the data in the openquery. How to force the query not to sort when merge joining?
merge target as t
using (select * from openquery(oracle, '
select * from t1 where UpdateTime > ''....'' order by k1, k2')
) as s on s.k1=t.k1 and s.k2=t.K2 -- the clustered PK of "target" is K1,k2
when matched then ......
when not matched then ......
Is there something like bulk insert's "with (order( { column [ ASC | DESC ] } [ ,...n ] ))"? will it help improve the query plan of the merge statement if it exists?
If the oracle table already have PK on K1,K2, will just using oracle.db.owner.tablename as target better? (will SQL Server figure out the index from oracle meta information?)
Or the best I can do is stored the oracle data in a local temp table and create a clustered primary key on K1,k2? I am trying to avoid to create a temp table because sometime the returned openquery data set can be large.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为表是最好的方法，因为这样您就可以创建所需的任何索引，但没有理由它应该是临时的；为什么不创建永久暂存表？使用本地索引的本地联接可能比远程查询结果的联接更有效，尽管唯一确定的方法是测试并查看。
如果您担心大量行，您可以考虑仅复制新行或更改的行。如果 Oracle 表已经具有用于行创建和更新时间的列，那将非常容易。
或者，您可以考虑使用 SSIS 而不是计划作业。我知道，如果您还没有使用 SSIS，您可能不想投入时间来学习它，但它是一个非常强大的工具，并且专为将大量数据移动到 MSSQL 中而设计。您可以使用以下工作流程创建包：
I think a table is the best way to go because then you can create whatever indexes you need, but there's no reason why it should be temporary; why not create a permanent staging table? A local join using local indexes will probably be much more efficient than a join on the results of a remote query, although the only way to know for sure is to test it and see.
If you're worried about the large number of rows, you can look into only copying over new or changed rows. If the Oracle table already has columns for row creation and update times, that would be quite easy.
Alternatively, you could consider using SSIS instead of a scheduled job. I understand that if you're not already using SSIS you may not want to invest time in learning it, but it's a very powerful tool and it's designed for moving large amounts of data into MSSQL. You would create a package with the following workflow: