Oracle spatial index takes no effect

Question

I am using hibernate spatial 4.0 with hibernate core 4.3, and we are using the oracle 11g with spatial extension.

This is the entity and dao:

@Entity
public class Pts{
    @Type(type = "org.hibernate.spatial.GeometryType")
    private Geometry Shape;
    ......
}


@Repository
public class PtsDao {
    private GeometryFactory gf = new GeometryFactory(new PrecisionModel(), 8307);


    @PersistenceContext
    private EntityManager em;

    @Transactional
    public void save(Pts Pts) {
        em.persist(Pts);
    }

    public List<Pts> query(double x, double y, double radius) {
        Geometry point = gf.createPoint(new Coordinate(x, y));
        Query query = em.createQuery("select b from Pts b where distance(b.Shape,:filter) < :distance", Pts.class);
        query.setParameter("filter", point);
        query.setParameter("distance", radius);
        return query.getResultList();
    }
}

The test:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"/spring/application-config.xml"})
public class PtsTest {
    private GeometryFactory gf = new GeometryFactory(new PrecisionModel(), 8307);

    @Autowired
    private PtsDao dao;

    @Before
    public void setup() {
        Pts b1 = new Pts();
        b1.setShape(gf.createPoint(new Coordinate(21.401829098, 29.0819022380)));
        dao.save(b1);
        ...
        //save only five points to the db
    }
    @Test
    public void testQuery() {
        long st = System.currentTimeMillis();
        List<Pts> re = dao.query(20.82123, 30.12342, 10000);
        System.out.println("-------test distance get result " + re.size() + " " + (System.currentTimeMillis() - st));
    }
}

I found that the query will take almost 400 million seconds, I think this is unacceptable for only five points.

I think this maybe caused by the index, so I create index in oracle manually:

CREATE INDEX pts_sidx ON pts(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX

However once I run the test again, it still cost 400+ ms.

What's going on?

---

Since we are using the ORM framework, so the table are generated by hibernate, and this is what we got in the pl/sql developer:

-- Create table

create table PTS
(
  objectid   NUMBER(10) not null,
  address    VARCHAR2(255 CHAR),
  shape      SDO_GEOMETRY,
)
alter table PTS
  add primary key (OBJECTID)
  using index 
  tablespace SPRING
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

And when we insert data to the database, we save nothing except the objectid(generated) and the geometry, all other column are empty.

And we do not create any query plan in the database.

When I tried to query use the native sql:

 select objectid from pts p where mdsys.sdo_geom.sdo_distance(
       p.shape,
       sdo_geometry(2001, 8307, null, sdo_elem_info_array(1,1,1), 
          sdo_ordinate_array(20.82123, 30.12342)),
       0.5
) <10000 ;

It cost me 300+ ms.

Answer 1

Your query, as written, forces a full table scan, since an expensive distance calculation must be performed on every row to determine which rows meet the constraint clause. If you had used the SDO_GEOM.WITHIN_DISTANCE operator, then an index could be consulted.

There seem to be a number of possible uses, but something like this might utilize the index:

 SELECT  objectid 
 FROM    pts p 
 WHERE   mdsys.sdo_geom.within_distance(
             p.shape,
             sdo_geometry(2001, 8307, null, sdo_elem_info_array(1,1,1), 
                 sdo_ordinate_array(20.82123, 30.12342)), 
             'distance=10 UNIT=KM') = 'TRUE';

I expect there are a number of optimizations you can perform, and I might not have the parameter order right, and the query itself will be calculated with spherical geometry, not spheroidal, but those can be left as an exercise.

I would caution you that latitude is Y, so if you didn't intend to generate a query circle in Libya (vice Sudan), then you'll need to flip the order of coordinates in your constructors.