Prev: Strange ODBC error
Next: OCI :memory leak
From: Robbert van der Hoorn on 4 Oct 2006 11:17 "ennio" <ennioj(a)libero.it> schreef in bericht news:1159974315.069540.137610(a)e3g2000cwe.googlegroups.com... > > Robbert van der Hoorn wrote: >> "Robbert van der Hoorn" <reply(a)forum.only> schreef in bericht >> news:4523adcf$0$4526$e4fe514c(a)news.xs4all.nl... >> > >> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)gmail.com> schreef in >> > bericht news:1159769948.535945.67090(a)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
From: ennio on 4 Oct 2006 11:32 Robbert van der Hoorn wrote: > "ennio" <ennioj(a)libero.it> schreef in bericht > news:1159974315.069540.137610(a)e3g2000cwe.googlegroups.com... > > > > Robbert van der Hoorn wrote: > >> "Robbert van der Hoorn" <reply(a)forum.only> schreef in bericht > >> news:4523adcf$0$4526$e4fe514c(a)news.xs4all.nl... > >> > > >> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)gmail.com> schreef in > >> > bericht news:1159769948.535945.67090(a)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
From: Robbert van der Hoorn on 4 Oct 2006 11:33 "ennio" <ennioj(a)libero.it> schreef in bericht news:1159975931.086175.32010(a)i3g2000cwc.googlegroups.com... > > Robbert van der Hoorn wrote: >> "ennio" <ennioj(a)libero.it> schreef in bericht >> news:1159974315.069540.137610(a)e3g2000cwe.googlegroups.com... >> > >> > Robbert van der Hoorn wrote: >> >> "Robbert van der Hoorn" <reply(a)forum.only> schreef in bericht >> >> news:4523adcf$0$4526$e4fe514c(a)news.xs4all.nl... >> >> > >> >> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)gmail.com> schreef >> >> > in >> >> > bericht news:1159769948.535945.67090(a)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?
From: ennio on 4 Oct 2006 11:39 Robbert van der Hoorn wrote: > "ennio" <ennioj(a)libero.it> schreef in bericht > news:1159975931.086175.32010(a)i3g2000cwc.googlegroups.com... > > > > Robbert van der Hoorn wrote: > >> "ennio" <ennioj(a)libero.it> schreef in bericht > >> news:1159974315.069540.137610(a)e3g2000cwe.googlegroups.com... > >> > > >> > Robbert van der Hoorn wrote: > >> >> "Robbert van der Hoorn" <reply(a)forum.only> schreef in bericht > >> >> news:4523adcf$0$4526$e4fe514c(a)news.xs4all.nl... > >> >> > > >> >> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)gmail.com> schreef > >> >> > in > >> >> > bericht news:1159769948.535945.67090(a)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.......
From: Robbert van der Hoorn on 4 Oct 2006 11:44
"ennio" <ennioj(a)libero.it> schreef in bericht news:1159976363.652126.212580(a)m73g2000cwd.googlegroups.com... > > Robbert van der Hoorn wrote: >> "ennio" <ennioj(a)libero.it> schreef in bericht >> news:1159975931.086175.32010(a)i3g2000cwc.googlegroups.com... >> > >> > Robbert van der Hoorn wrote: >> >> "ennio" <ennioj(a)libero.it> schreef in bericht >> >> news:1159974315.069540.137610(a)e3g2000cwe.googlegroups.com... >> >> > >> >> > Robbert van der Hoorn wrote: >> >> >> "Robbert van der Hoorn" <reply(a)forum.only> schreef in bericht >> >> >> news:4523adcf$0$4526$e4fe514c(a)news.xs4all.nl... >> >> >> > >> >> >> > "Vladimir M. Zakharychev" <vladimir.zakharychev(a)gmail.com> >> >> >> > schreef >> >> >> > in >> >> >> > bericht >> >> >> > news:1159769948.535945.67090(a)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 |