Hello,
if your data are mostly static and you have a few mains objects,
maybe you can have some gain while defining conditional indexes for those plus one for the rest
and then slicing the query:
create index o_1x on X (start,end,id) where object_id = 1
create index o_2x on X (start,end,id) where object_id = 2
create index o_3x on X (start,end,id) where object_id = 3
create index o_4x on X (start,end,id) where object_id = 4
...
create index o_4x on X (start,end,id) where object_id not in (1,2,3,4..)
I'm not sure that putting all in one index and using the BETWEEN clause
as in my example is the best method though.
Marc Mamin
SELECT
l1.id AS id1,
l2.id AS id2
FROM
location l1,
location l2
WHERE l1.objectid = 1
AND (l2.start BETWEEN l1.start AND l1.end
OR
l1.start BETWEEN l2.start AND l2.end
)
l1.start
AND l2.start <> l2.start -- if required
AND l2.start <> l2.end -- if required
AND l1.id <> l2.id
UNION ALL
...
WHERE l1.objectid = 2
...
UNION ALL
...
WHERE l1.objectid not in (1,2,3,4..)