什么是优化仅返回符合我的条件的三条记录的查询的最佳方法？

huangapple
go评论93阅读模式
英文:

What is the best way to optimize a query that returns only three records that match my criteria?

问题

在Oracle中，我有一个表空间，记录了许多汽车的位置。每个记录都有里程、时间、经度和纬度。表空间有五列：'PLATENO'、'SENDTIME'、'MILEAGE'以及'LONGITUDE'和'LATITUDE'。它们分别表示车辆的车牌号码、位置发送时间、里程和车辆的两个坐标。'SENDTIME'列中的值采用日期时间格式。此外，我还有一些建筑物的坐标。我想找出轨迹在距离建筑物200米以内的车辆。因此，我创建了一个名为"BUILDINGS"的表来记录建筑物，类似于以下内容：

  1. CREATE TABLE BUILDINGS (
  2.   NAME VARCHAR2(50) NOT NULL,
  3.   ADDR VARCHAR2(50) NOT NULL,
  4.   LONGITUDE NUMBER NOT NULL,
  5.   LATITUDE NUMBER NOT NULL
  6. );
  8. INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
  9.                       '北京市大兴区永兴路7号院1号楼1F2F局部内装修工程', '北京市大兴区永兴路7号院1号楼',
  10.                       116.314497, 39.685536);
  11. INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
  12.                       '北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目', '北京市大兴区华佗路50号院',
  13.                       116.282965, 39.668402);

然后，我使用"SDO_GEOM.SDO_DISTANCE"函数来计算距离，以找到符合我的要求的记录。

  1. SELECT g.PLATENO, g.SENDTIME, g.MILEAGE, g.LONGITUDE, g.LATITUDE, 
  2. b.NAME AS BUILDING_NAME, b.ADDR, b.LONGITUDE, b.LATITUDE
  3. FROM GPSINFO g, BUILDINGS b
  4. WHERE SDO_GEOM.SDO_DISTANCE (
  5.   SDO_GEOMETRY (2001, 4326, SDO_POINT_TYPE (g.LONGITUDE, g.LATITUDE, NULL), NULL, NULL),
  6.   SDO_GEOMETRY (2001, 4326, SDO_POINT_TYPE (b.LONGITUDE, b.LATITUDE, NULL), NULL, NULL),
  7.   0.01,
  8.   'unit=M'
  9. ) <= 200;

但是查询运行速度较慢。我只需要符合我的条件的三条记录。如何优化我的代码？我尝试使用"FETCH FIRST"，但它不起作用：

  1. SELECT v.PLATENO, v.SENDTIME, v.MILEAGE, v.LONGITUDE, v.LATITUDE, b.NAME AS BUILDING_NAME
  2. FROM VEHICLES v, BUILDINGS b
  3. WHERE SDO_GEOM.SDO_DISTANCE (
  4.   SDO_GEOMETRY (2001, 4326, SDO_POINT_TYPE (v.LONGITUDE, v.LATITUDE, NULL), NULL, NULL),
  5.   SDO_GEOMETRY (2001, 4326, SDO_POINT_TYPE (b.LONGITUDE, b.LATITUDE, NULL), NULL, NULL),
  6.   0.01,
  7.   'unit=M'
  8. ) <= 50
  9. AND HAVERSINE(v.LONGITUDE, v.LATITUDE, b.LONGITUDE, b.LATITUDE) <= 50
  10. ORDER BY v.SENDTIME DESC
  11. FETCH FIRST 3 ROWS ONLY;
英文:

In Oracle: I have a tablespace that records the positions of many cars. Each record has a mileage, time, LONGITUDE and LATITUDE. The tablespace has five columns: 'PLATENO' ‘SENDTIME’, ‘MILEAGE’, and 'LONGITUDE' 'LATITUDE'. They represent the plate number of cars, positon send time, mileage and two coordinates of cars respectively. The values in the ‘SENDTIME’ column are in the datetime format. ALSO, i have some coordinates of buildings. I want to find out the vehicles whose trajectory passes 200 meters near the buildings. So i create a table named buildings to record the buildings, something like this:

  1. CREATE TABLE BUILDINGS (
  2.   NAME VARCHAR2(50) NOT NULL,
  3.   ADDR VARCHAR2(50) NOT NULL,
  4.   LONGITUDE NUMBER NOT NULL,
  5.   LATITUDE NUMBER NOT NULL
  6. );
  9. INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
  10.                       &#39;北京市大兴区永兴路7号院1号楼1F2F局部内装修工程&#39;, &#39;北京市大兴区永兴路7号院1号楼&#39;,
  11.                       116.314497, 39.685536);
  12. INSERT INTO BUILDINGS (NAME, ADDR, LONGITUDE, LATITUDE) VALUES (
  13.                       &#39;北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目&#39;, &#39;北京市大兴区华佗路50号院&#39;,
  14.                       116.282965, 39.668402);

Then I use sdo_geom.sdo_distance function to calculate distance to find the reords to meet my requirements.

  1. select g.PLATENO, g.SENDTIME, g.MILEAGE, g.LONGITUDE, g.LATITUDE, 
  2. b.NAME as BUILDING_NAME, b.ADDR, b. LONGITUDE, b.LATITUDE
  3. from GPSINFO g, BUILDINGS b
  4. where sdo_geom.sdo_distance (
  5.   sdo_geometry (2001, 4326, sdo_point_type (g.LONGITUDE, g.LATITUDE, null), null, null),
  6.   sdo_geometry (2001, 4326, sdo_point_type (b.LONGITUDE, b.LATITUDE, null), null, null),
  7.   0.01,
  8.   &#39;unit=M&#39;
  9. ) &lt;= 200;

But the query runs rather slowly. I only need three records that match my criteria. How to optimize my code? i tried FETCH FIRST, but it doesn't work：

  1. select v.PLATENO, v.SENDTIME, v.MILEAGE, v.LONGITUDE, v.LATITUDE, b.NAME as BUILDING_NAME
  2. from VEHICLES v, BUILDINGS b
  3. where sdo_geom.sdo_distance (
  4.   sdo_geometry (2001, 4326, sdo_point_type (v.LONGITUDE, v.LATITUDE, null), null, null),
  5.   sdo_geometry (2001, 4326, sdo_point_type (b.LONGITUDE, b.LATITUDE, null), null, null),
  6.   0.01,
  7.   &#39;unit=M&#39;
  8. ) &lt;= 50
  9. and HAVERSINE(v.LONGITUDE, v.LATITUDE, b.LONGITUDE, b.LATITUDE) &lt;= 50
  10. ORDER BY v.SENDTIME DESC
  11. FETCH FIRST 3 ROWS ONLY;

答案1

得分: 4

不必将longitude和latitude存储为NUMBER数据类型，然后在查询中转换为SDO_GEOMETRY，您可以将它们存储为SDO_GEOMETRY类型并添加空间索引：

  1. CREATE TABLE BUILDINGS (
  2.   NAME      VARCHAR2(200) NOT NULL,
  3.   ADDR      VARCHAR2(200) NOT NULL,
  4.   LOCATION  SDO_GEOMETRY NOT NULL
  5. );
  7. INSERT INTO USER_SDO_GEOM_METADATA (
  8.   TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
  9. ) VALUES (
  10.   'BUILDINGS',
  11.   'LOCATION', 
  12.   SDO_DIM_ARRAY(
  13.     SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.0001), 
  14.     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.0001)
  15.   ), 
  16.   4326
  17. );
  19. CREATE INDEX Buildings_SIDX ON Buildings( location ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

然后，您可以插入数据：

  1. INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
  2. VALUES (
  3.   '北京市大兴区永兴路7号院1号楼1F2F局部内装修工程',
  4.   '北京市大兴区永兴路7号院1号楼',
  5.   sdo_geometry(2001, 4326, sdo_point_type (116.314497, 39.685536, null), null, null)
  6. );
  8. INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
  9. VALUES (
  10.   '北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目',
  11.   '北京市大兴区华佗路50号院',
  12.   sdo_geometry(2001, 4326, sdo_point_type (116.282965, 39.668402, null), null, null)
  13. );

如果您想获取位置的各个组件，可以使用嵌套对象（注意：您需要确保从表别名开始）：

  1. SELECT b.name,
  2.        b.addr,
  3.        b.location.sdo_point.x AS longitude,
  4.        b.location.sdo_point.y AS latitude
  5. FROM   buildings b

输出如下：

NAME ADDR LONGITUDE LATITUDE
北京市大兴区永兴路7号院1号楼1F2F局部内装修工程 北京市大兴区永兴路7号院1号楼 116.314497 39.685536
北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目 北京市大兴区华佗路50号院 116.282965 39.668402

您可以对GPSINFO表进行相同的更改，然后您的查询将不需要在运行时生成大量对象，并且可以利用索引。

您还不需要同时使用空间比较和HAVERSINE函数。

英文:

Rather than storing the longitude and latitude as NUMBER data types and then converting to SDO_GEOMETRY in the query, you can store them as SDO_GEOMETRY types and add a spatial index:

  1. CREATE TABLE BUILDINGS (
  2.   NAME      VARCHAR2(200) NOT NULL,
  3.   ADDR      VARCHAR2(200) NOT NULL,
  4.   LOCATION  SDO_GEOMETRY NOT NULL
  5. );
  7. INSERT INTO USER_SDO_GEOM_METADATA (
  8.   TABLE_NAME, COLUMN_NAME, DIMINFO, SRID
  9. ) VALUES (
  10.   &#39;BUILDINGS&#39;,
  11.   &#39;LOCATION&#39;, 
  12.   SDO_DIM_ARRAY(
  13.     SDO_DIM_ELEMENT(&#39;LONG&#39;, -180.0, 180.0, 0.0001), 
  14.     SDO_DIM_ELEMENT(&#39;LAT&#39;, -90.0, 90.0, 0.0001)
  15.   ), 
  16.   4326
  17. );
  19. CREATE INDEX Buildings_SIDX ON Buildings( location ) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Then you can insert the data:

  1. INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
  2. VALUES (
  3.   &#39;北京市大兴区永兴路7号院1号楼1F2F局部内装修工程&#39;,
  4.   &#39;北京市大兴区永兴路7号院1号楼&#39;,
  5.   sdo_geometry(2001, 4326, sdo_point_type (116.314497, 39.685536, null), null, null)
  6. );
  8. INSERT INTO BUILDINGS (NAME, ADDR, LOCATION)
  9. VALUES (
  10.   &#39;北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目&#39;,
  11.   &#39;北京市大兴区华佗路50号院&#39;,
  12.   sdo_geometry(2001, 4326, sdo_point_type (116.282965, 39.668402, null), null, null)
  13. );

If you want to get the components of the location then you can use the nested objects (note: you need to ensure you start with the table alias):

  1. SELECT b.name,
  2.        b.addr,
  3.        b.location.sdo_point.x AS longitude,
  4.        b.location.sdo_point.y AS latitude
  5. FROM   buildings b

Which outputs:

NAME ADDR LONGITUDE LATITUDE
北京市大兴区永兴路7号院1号楼1F2F局部内装修工程 北京市大兴区永兴路7号院1号楼 116.314497 39.685536
北京金沃夫生物工程科技有限公司23号楼诊断试剂GMP生产车间及配套实验室、研发实验室建设项目 北京市大兴区华佗路50号院 116.282965 39.668402

You can make the same change to the GPSINFO table and then your queries will not need to generate lots of objects at run-time and you can take advantage of the indexes.

You also do not need to use both spatial comparisons and your HAVERSINE function.

fiddle

huangapple
  • 本文由 发表于 2023年6月19日 17:00:31
  • 转载请务必保留本文链接：https://go.coder-hub.com/76505126.html
  • database-performance
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定