Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
Given the following two datasets
CREATE TYPE Tweet AS OPEN {
tid: bigint,
area_code: string,
text: string,
location: point,
hateful_rating: int,
timestamp: datetime
};
CREATE DATASET Tweets(Tweet) PRIMARY KEY tid;
CREATE TYPE OfficerLocation AS OPEN {
oid: bigint,
location: point
};
CREATE DATASET OfficerLocations(OfficerLocation) PRIMARY KEY oid;
CREATE INDEX s_location ON Tweets(location) type RTREE;
CREATE INDEX o_location ON OfficerLocations(location) type RTREE;
The 1st query won't be able to utilize index, and a user would have to explicitly create a new variable like in the 2nd query to be able to use it. The reason is in IntroduceJoinAccessMethodRule, the case in which one of the parameters is function call is ignored when considering utilize indexes.
SELECT t
FROM Tweets t, OfficerLocations o
WHERE /*+ indexnl */ spatial_intersect(create_circle(t.location, 100.0), o.location);
SELECT t
FROM Tweets t, OfficerLocations o
LET area = create_circle(t.location, 100.0)
WHERE /*+ indexnl */ spatial_intersect(area, o.location);