2 Replies Latest reply: Oct 13, 2013 3:54 AM by Jonathan Lewis RSS

    SDO_DISTANCE performance

    Rinne

      Table A_SPATIAL has 15 million records with different longitude and latitude values. I also have the column SHAPE SDO_GEOMETRY, which stores SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL), NULL, NULL).

       

      I created the function and index below:

      create or replace function get_long_lat_pt(longitude in number,
                                                 latitude in number)
      return SDO_GEOMETRY deterministic is
      begin
           return sdo_geometry(2001, 8307,
                      sdo_point_type(longitude, latitude, NULL),NULL, NULL);
      end;
      /
      
      INSERT INTO user_sdo_geom_metadata VALUES(
         'A_SPATIAL', -- table
         'SHAPE', -- function
         mdsys.sdo_dim_array(
            mdsys.sdo_dim_element('LONGITUDE', -180, 180, 0.005),
            mdsys.sdo_dim_element('LATITUDE', -90, 90, 0.005)
         ),
         8307  -- SRID
          );
      commit;
      
      CREATE INDEX A_SPATIAL_SHAPE_IDX
      ON A_SPATIAL(Shape)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;
      
      
      

       

      Then when I execute the query below, it does a full table scan. Even if I have the index above, the index wouldn't get used because I am using SDO_DISTANCE to wrap around it I suppose. The longitude and latitude provided to the function get_long_lat_pt below are user input and they all vary.

       

      select * from A_SPATIAL where
      SDO_GEOM.SDO_DISTANCE(SHAPE, get_long_lat_pt(95.224, 31.601), 1, 'unit=MILE') < 20;
      
      
      

       

      Two questions:

      1. What is the most optimal way of running such query to perform the best? I guess the full table scan is obvious since I need to calculate the distance, but is there a way to improve performance? 

      2. Is the index above even useful at all?

       

      Thank you.