oracle地理编码查询真的很慢，我如何优化动态字段？
我有一个 Oracle 表 12K 记录/健身房，下面的查询大约需要大约 0.3 秒：
SELECT (acos(sin(41.922682*0.017453293) *
sin(to_number(LATITUDE)*0.017453293) + cos(41.922682*0.017453293) *
cos(to_number(LATITUDE)*0.017453293) * cos(to_number(LONGITUDE)*0.017453293 -
(-87.65432*0.017453293)))*3959) as distance
FROM gym
但是，我想返回距离 <= 10 的所有记录，一旦我运行以下查询，我的查询执行时间跃升至约 5.0 秒：
SELECT * from (SELECT (acos(sin(41.922682*0.017453293) *
sin(to_number(LATITUDE)*0.017453293) + cos(41.922682*0.017453293) *
cos(to_number(LATITUDE)*0.017453293) * cos(to_number(LONGITUDE)*0.017453293 -
(-87.65432*0.017453293)))*3959)
as distance FROM gym)
WHERE distance <= 10
ORDER BY distance asc
知道如何在 Oracle 中优化它吗？
I have an Oracle table 12K records/gyms, and the query below takes approximately ~0.3s:
SELECT (acos(sin(41.922682*0.017453293) *
sin(to_number(LATITUDE)*0.017453293) + cos(41.922682*0.017453293) *
cos(to_number(LATITUDE)*0.017453293) * cos(to_number(LONGITUDE)*0.017453293 -
(-87.65432*0.017453293)))*3959) as distance
FROM gym
However, I would like to return all of the records where distance <= 10, and as soon as I run the following query, my query execution time jumps up to ~5.0s:
SELECT * from (SELECT (acos(sin(41.922682*0.017453293) *
sin(to_number(LATITUDE)*0.017453293) + cos(41.922682*0.017453293) *
cos(to_number(LATITUDE)*0.017453293) * cos(to_number(LONGITUDE)*0.017453293 -
(-87.65432*0.017453293)))*3959)
as distance FROM gym)
WHERE distance <= 10
ORDER BY distance asc
Any idea how I can optimize this in Oracle?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
最重要的是：
作为一个非常粗略的近似值，您通常可以使用 0.1 度，赤道处为 11 公里，其他地方则更少
所以添加
（如果您使用嵌套查询，请将其添加到最深的级别）
其他事情：
Most important:
as an and very rough approximation you could use 0.1 degree as a rule, this is 11km at the equator,and less elsewhere
so add
(If you use nested queries, add this to the deepest level)
Other things:
有钱。具体来说，是 Oracle Spatial。
With money. Specifically, Oracle Spatial.
1) 你如何测量第一个查询的 0.3 秒？我敢打赌，您正在测量获取第一行所需的时间，而不是获取最后一行所需的时间。如果可能的话，大多数客户端工具都会在数据库完成生成结果之前很久就开始显示结果（如果没有
ORDER BY，则几乎肯定是这样）。因此，您可能正在测量第一个查询计算到前 50 个或 500 个健身房的距离所需的时间，以及最后一个查询计算到所有 12,000 个健身房的距离所需的时间。2) Oracle Locator 是所有Oracle 数据库的多个版本，包括使用空间索引的功能并提供计算距离的内置方法。它不如 Oracle Spatial 强大，但对于您在这里讨论的内容来说应该绰绰有余。
3）如果你想推出自己的，我会赞同johanvdw的建议，即使用边界框 。
1) How are you measuring 0.3 seconds for the first query? I'll wager that you are measuring the time required to fetch the first row rather than the time required to fetch the last row. Most client tools will start displaying results long before the database has finished producing them if that is possible (which it almost certainly is if there is no
ORDER BY). So you're probably measuring the time required by the first query to calculate the distance to the first 50 or 500 gyms against the time required by the last query to calculate the distance to all 12,000 gyms.2) Oracle Locator is a feature that comes with all editions of the Oracle database that includes the ability to use spatial indexes and that provides built-in methods for computing distance. It's not nearly as powerful as Oracle Spatial but it should be more than sufficient for what you're discussing here.
3) If you want to roll your own, I'd second johanvdw's suggestion of using a bounding box.