oracle将计算结果当做查询条件如何提高性能
0
悬赏园豆：100
[已解决问题]
解决于 2020-01-21 10:11
我现在有一张表有300多万条数据，需求是从这些记录中随机抽取一条和余下的所有记录通过经纬度进行距离的计算，并且筛选出距离小于8km的距离数值
我已经用sql实现 但是性能特别差 需要1分10秒左右 领导的要求最好是在10s之内，希望大佬们指点一下小弟，以下是我的sql
select distance from (
select
--计算两点距离的公式
trunc(SQRT(
power(((TB_GC_COMPONENTINFO.ZB_X - random_tc.zb_x) * 3.14159265358979323 * 6371229 *cos((TB_GC_COMPONENTINFO.ZB_Y
- random_tc.zb_y) / 2 * 3.14159265358979323 / 180) / 180),2)
- power(((TB_GC_COMPONENTINFO.ZB_Y - random_tc.zb_y) * 3.14159265358979323 * 6371229 / 180),2))
/ 1000,2)
as distance
from TB_GC_COMPONENTINFO,
--随机抽取一条记录
(select zb_x,zb_y,bzm from TB_GC_COMPONENTINFO sample(10) where rownum<2) random_tc
where TB_GC_COMPONENTINFO.bzm!=random_tc.bzm
)
where distance<8
最佳答案
0
没太看明白你的sql，但是貌似你是300万数据都参与计算距离了，谬矣。
我想到一个方法可以加快你的计算速度。你选定了一个点，先根据这个点先选择出那些以这个点为中心，边长为16km的正方形内的点，然后再从这些点里过滤出需要的点（正方形的内切圆内的点）。这样就不用遍历数据库里所有的数据了，过滤出正方形内的数据很简单：
select * from t where x >= random.x - 8 and x <= random.x + 8 and y >= random.y - 8 and y <= random.y + 8。如果x，y字段上有索引的话，查询很很快的。
别忘了结贴，把园豆给我。
收获园豆：100
这个算距离算的是两位经纬度之间的距离，公式必须是上述的公式 至于索引我早就加上了 其实最浪费时间的是cos这个函数 光是它就耗费了50s
我重新改写一下sql:
select trunc(SQRT(
power(((TB_GC_COMPONENTINFO.ZB_X - random_tc.zb_x) * 3.14159265358979323 * 6371229 *cos((TB_GC_COMPONENTINFO.ZB_Y
- random_tc.zb_y) / 2 * 3.14159265358979323 / 180) / 180),2)
power(((TB_GC_COMPONENTINFO.ZB_Y - random_tc.zb_y) * 3.14159265358979323 * 6371229 / 180),2))
/ 1000,2)
as distancefrom TB_GC_COMPONENTINFO,(select zb_x,zb_y,bzm from TB_GC_COMPONENTINFO sample(10) where rownum<2) random_tc
where TB_GC_COMPONENTINFO.bzm!=random_tc.bzm
and
power(((TB_GC_COMPONENTINFO.ZB_X - random_tc.zb_x) * 111198.92344854* cos((TB_GC_COMPONENTINFO.ZB_Y + random_tc.zb_y) / 114.59155902616)),2)power(((TB_GC_COMPONENTINFO.ZB_Y - random_tc.zb_y) * 111198.92344854),2)
<640000
这样快了3s左右 但是还是太慢了 那个cos很浪费时间
@那一年，我二十二: 哦，就是和我们平时算公式不一样，我们平时算的是平面上，你们算的是球面上。那也应该有个办法先把不可能是范围内的数据过滤掉吧，不用300万数据都参与计算距离。
@会长: 这就是问题的关键 怎么过滤？ 无非还是得每条算出距离和8000比较一下 不行的pass掉 我这个sql的目的就是说白了就是为了过滤8000以外的数据 我现在在分析那个公式的原理 看看能不能把cos转换为斜边比对边 这样的话应该会提高性能了
@那一年，我二十二: 不知道我有没有表达清楚，我的意识是这300万数据中大部分数据需要参与距离计算，可以想一个办法把不可能是8km距离之内的点全部1去掉，如果是平面上，就用我说的那个方法，但是现在你们是在球面上，这样的话你找找有什么办法吧，也可以近似地认为是平面，把正方形的边长想象地大于8km一些，也能去掉大部分不需要参与计算的数据。
@那一年，我二十二: 可以简单得对比横坐标和纵坐标啊，比如横坐标和欲求点相差了10km，那么它肯定不用参与计算了，肯定远于8km了。
@会长: 有道理 我试试 先把经纬度转换为坐标 比较过滤一下
@那一年，我二十二: 不过如果你按照平面的方法肯定不行的，因为是球面。可以近似地认为是平面，把边长设置的长一些，本来是16km，但是算的时候按20km算，这样就不会因为是球面而少算点吧
其他回答(1)
0
在sql里面用函数肯定慢撒
