oracle11g - 在 inside in 语句中使用 inside 时，如果不使用索引，则无法评估 SDO_NN

Question

如果我运行以下查询:

select B3.bid as id ,B3.bshape as shape 
from Buildings B3 
where B3.bid in 
(
   select distinct B1.bid from Buildings B1, 
   (
     select * from Buildings B where B.bname in (select BOF.bname from Buildings_On_Fire BOF)
   ) B2 where sdo_nn(B1.bshape, B2.bshape, 'distance=100') = 'TRUE' and B1.bname != b2.bname
)

我收到以下错误:

ERROR at line 1:
ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9

但是，如果只运行以下子查询:

select distinct B1.bid from Buildings B1, 
(
   select * from Buildings B  where B.bname in (select BOF.bname from Buildings_On_Fire BOF)
) B2 where sdo_nn(B1.bshape, B2.bshape, 'distance=100') = 'TRUE' and B1.bname != b2.bname

这执行得很好。我已经验证了空间索引，它们似乎是有效的。
我是 oracle 的新手，不知道下一步该做什么。请帮忙。

如果有不需要更改上述查询的解决方案，那将是最好的。

Answer 1

回答有点晚了，但是...

你得到的错误是因为优化器没有使用空间索引来解决 SDO_NN 算子。与其他空间运算符(SDO_RELATE、SDO_WIHIN_DISTANCE)相反，如果没有索引的帮助，SDO_NN 无法解析。

然后我再次怀疑您的查询表述不正确。如果我理解正确的话，你想要做的是找到距离任何着火的建筑物 100(什么？米？)以内的所有建筑物。为此，请使用 SDO_WITHIN_DISTANCE 运算符。

假设您的表格是这样的:

建筑物(投标编号，bname varchar2(30)，bshape sdo_geometry)

buildings_on_fire(标号，bname varchar2(30))

然后选择将是这样的:

select b1.bid as id, b1.bshape as shape
from   buildings b1, 
       buildings b2, 
       buildings_on_fire bof
where  b2.bname = bof.bname
and    b1.bname <> b2.bname
and    sdo_within_distance (
         b1.bshape, b2.bshape, 
         'distance=100 unit=meter'
       ) = 'TRUE';