Re: ORA-13249

From: ennio <ennioj_at_libero.it>
Date: 4 Oct 2006 08:57:40 -0700
Message-ID: <1159977459.966834.195140@k70g2000cwa.googlegroups.com>

Robbert van der Hoorn wrote:
> "ennio" <ennioj_at_libero.it> schreef in bericht
> news:1159976363.652126.212580_at_m73g2000cwd.googlegroups.com...
> >
> > Robbert van der Hoorn wrote:
> >> "ennio" <ennioj_at_libero.it> schreef in bericht
> >> news:1159975931.086175.32010_at_i3g2000cwc.googlegroups.com...
> >> >
> >> > Robbert van der Hoorn wrote:
> >> >> "ennio" <ennioj_at_libero.it> schreef in bericht
> >> >> news:1159974315.069540.137610_at_e3g2000cwe.googlegroups.com...
> >> >> >
> >> >> > Robbert van der Hoorn wrote:
> >> >> >> "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
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > So i must drop the non spatial index that are in the table montane?
> >> >> > and yes i want to find all pairs close to each other,
> >> >> > It's only a trial to try the operator
> >> >> >
> >> >>
> >> >> NOOOOOOOOO! Don't drop it! Just don't use a hint with it!
> >> >>
> >> >> Robbert
> >> >
> >> >
> >> > But don't work also i if don't use a hint
> >> >
> >>
> >> Did you 1) put in the ordered hint 2) swap your parameters?
> >
> > swapping the parameteres it work.......thanks.....very thanks
> > I'm checking if it work in the right way.......
> >

>

> Yippie!
> Note that there may also be problems if your geometries have errors like
> duplicate vertices, or are self crossing. Spatial tends to be very buggy
> (depending on OS and platform). Performing the same query twice may give
> different results! If so, flush the SGA.

>

> Have a spatial day!

>

> Greetings,

>

> Robbert van der Hoorn
> OSA it BV
> The Netherlands

the DAta and the geometries are tested on a other database so i thinks should be no problem .........thanks Received on Wed Oct 04 2006 - 10:57:40 CDT