英文:
Why does the query that uses Remote View take too long to execute?
问题
我有一个简单的查询（Oracle 19）
SELECT T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE T.ID = 'some ID'
执行计划
| Id | 操作 | 名称 | 行数 | 字节 | 成本 | 时间 |
|---|---|---|---|---|---|---|
| 0 | SELECT 语句 | 1 | 280 | 23 | 00:00:01 | |
| * 1 | 散列连接 | 1 | 280 | 23 | 00:00:01 | |
| 2 | 索引行表访问 | TABLE1 | 1 | 57 | 1 | 00:00:01 |
| * 3 | 唯一索引扫描 | PK_TABLE1_ID | 1 | 0 | 00:00:01 | |
| 4 | 远程 | SOME_VIEW | 2 | 446 | 22 | 00:00:01 |
运行时间太长。TABLE1少于100行，字段ID是主键。
但是以下查询
SELECT T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE R.ID = 'some PID'
| Id | 操作 | 名称 | 行数 | 字节 | 成本 | 时间 |
|---|---|---|---|---|---|---|
| 0 | SELECT 语句 | 1 | 274 | 25 | 00:00:01 | |
| * 1 | 散列连接 | 1 | 274 | 25 | 00:00:01 | |
| 2 | 远程 | SOME_VIEW | 1 | 223 | 22 | 00:00:01 |
| * 3 | 表完全访问 | TABLE1 | 1 | 51 | 3 | 00:00:01 |
运行得很好。我不明白为什么？
英文:
I have a simple query (Oracle 19)
SELECT T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE T.ID = 'some ID'
execution plan
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 280 | 23 | 00:00:01 | |
| * 1 | HASH JOIN | 1 | 280 | 23 | 00:00:01 | |
| 2 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 57 | 1 | 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | PK_TABLE1_ID | 1 | 0 | 00:00:01 | |
| 4 | REMOTE | SOME_VIEW | 2 | 446 | 22 | 00:00:01 |
It runs too long. TABLE1 has less than 100 rows and field ID is the primary key.
But the query
SELECT T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE R.ID = 'some PID'
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|---|---|---|---|---|---|
| 0 | SELECT STATEMENT | 1 | 274 | 25 | 00:00:01 | |
| * 1 | HASH JOIN | 1 | 274 | 25 | 00:00:01 | |
| 2 | REMOTE | SOME_VIEW | 1 | 223 | 22 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | TABLE1 | 1 | 51 | 3 | 00:00:01 |
works perfectly.
I don't understand why?
答案1
得分: 2
在你的第一个查询中，你从本地表返回一行数据，然后将其与整个远程视图进行比较。你无法了解该视图是如何构建的，远程数据库的工作负荷或资源，或者在远程数据库上处理多少数据以及由于网络传输引起的延迟。
在你的第二个查询中，你只从远程视图中返回了一行数据，显然处理速度很快，然后将其与本地表进行比较，本地表也能够快速访问。我不能确定，因为你没有提供更多细节，但我怀疑当处理多行数据时，远程视图的性能可能不太好；也许它包含了许多嵌入的内联SQL函数？
英文:
In your first query, you are returning one row from the local table and comparing it to the entire remote view. You have no visibility into how that view is constructed, the workload or resources of the remote database, or how much data is being processed there with all of the latency that comes with pushing that over the network.
In your second query, you are only returning a single row from the remote view, which is apparently processed very quickly, and comparing that to the local table, which is also accessed quickly. I can't say for sure since you haven't included any more details, but I suspect that the remote view doesn't scale well when dealing with multiple rows of data; perhaps it has a lot of embedded inline SQL functions?
答案2
得分: 1
你的本地数据库选择在本地和远程表上使用散列连接。由于你的第一个查询没有将谓词应用于远程表，它必须处理其100%的数据并将其通过网络返回到本地数据库。然后才会进行散列连接，由于本地表上的谓词过滤，你最终得到你想要的结果。
第二个查询将谓词应用于远程表。这将使谓词推入视图，并允许该视图更高效地获取你想要的数据，仅返回感兴趣的部分。
如果你始终只处理来自TABLE1的单行记录，那么你可能希望强制使用嵌套循环：
SELECT /*+ LEADING(t) USE_NL(R) */ T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE T.ID = 'some ID';
英文:
Your local database is choosing to use a hash join on the local and remote tables. Since your first query doesn't apply any predicate to the remote table, it has to process 100% of it's data and return it over the network to the local database. Only then does the hash join take place and due to the predicate filter on the local table you get filtered down to the results you want.
The second query is applying a predicate to the remote table. That is getting predicate pushed into the view and allowing that view to much more efficiently get just the data you want and return only what's of interest.
If you are always going to be working with a single row from TABLE1, then you may want to force a nested loops:
SELECT /*+ LEADING(t) USE_NL(R) */ T.ID, T.PID, R.MESS, R.REMOTE_USR, R.COMMENT
FROM TABLE1 T
JOIN SOME_VIEW@REMOTE R ON R.ID = T.PID
WHERE T.ID = 'some ID'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论