Re: ORA-13249

"Robbert van der Hoorn" <reply_at_forum.only> schreef in bericht news:4523adcf$0$4526$e4fe514c_at_news.xs4all.nl...
>
> "Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> schreef in
> bericht news:1159769948.535945.67090_at_b28g2000cwb.googlegroups.com...
>>
>> ennio wrote:
>>> hi I have occured in this error ;
>>>
>>>
>>> SQL> SELECT/*+ INDEX (montane, mont_idx)*/ A.id id,B.id id FROM montane
>>> A,montan
>>> e B where SDO_NN (a.geom,b.geom,'sdo_num_res=1',1)='TRUE';
>>> SELECT/*+ INDEX (montane, mont_idx)*/ A.id id,B.id id FROM montane
>>> A,montane B w
>>> here SDO_NN (a.geom,b.geom,'sdo_num_res=1',1)='TRUE'
>>> *
>>> ERRORE alla riga 1:
>>> ORA-13249: SDO_NN cannot be evaluated without using index
>>> ORA-06512: a "MDSYS.MD", line 1723
>>> ORA-06512: a "MDSYS.MDERR", line 17
>>> ORA-06512: a "MDSYS.PRVT_IDX", line 9
>>>
>>>
>>> but montane table has a spatial index .....
>>> also similar function like SDO_WITHIN regulary works
>>> ......
>>> I can do?
>>
>> No Oracle version/patchset level, no platform information, no DDL for
>> the table nor index... It's hard to tell what might be causing this
>> error without additional detail.
>>
>> Regards,
>> Vladimir M. Zakharychev
>> N-Networks, makers of Dynamic PSP(tm)
>> http://www.dynamicpsp.com
>>
>
> To ensure the correct results, NO non-spatial indexes should be used on
> the table that contains the search column. (you force your statement
> not to use the spatial index)
>
> And I think you should use the +ORDERED hint as well.
>
> Did you register your index in the indexes-view?
>
> Robbert van der Hoorn
> OSA it BV
> The Netherlands
>

By the way, your A and B colums are in wrong order. The spatial index should be on the SECOND parameter (since you use the same table twice, that's always correct) but also on the FIRST table in the where clause, so either switch your params or your where clause.

SELECT
/*+ ORDERED*/
A.id id,B.id id FROM montane A,montane B where SDO_NN (b.geom,a.geom,'sdo_num_res=1',1)='TRUE'

And what are you trying to do here? Find all pairs close to each other?

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Wed Oct 04 2006 - 08:15:51 CDT