I am leaning towards my second method as it is far easier to code for all cases.
But, the example I gave you has mistakes in the inner select statement that I let John know about. Could you modify your query and try again? I think it will look like this:
ELECT /*+ ORDERED*/ b.*
FROM MUB.REGIONAL a,
( SELECT rowid,"OBJECTID",sysdate,systimestamp,current_timestamp,t.SHAPE as SHAPE
FROM MUB.REGIONAL t
WHERE SDO_FILTER(t.SHAPE,
MDSYS.SDO_GEOMETRY(2003,82301,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)),'querytype=WINDOW') = 'TRUE'
) b
WHERE MDSYS.SDO_NN(a.SHAPE,MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),'sdo_batch_size=0',1) = 'TRUE'
AND SDO_NN_DISTANCE(1) < 533
AND b.rowid = a.rowid
ORDER BY sdo_nn_distance(1);
I have contacts in Oracle but it is an endemic problem they need to fix. They are all working on 3.0 and I am still on 2.1 so they probably don't really want to hear. The forum isn't much help either. Anyway, we are looking into how to work more closely with SQL Developer's NLS settings.
Finally, I am preparing a release candidate for our next release: would you be willing to test it for me as it contains the SDO_NN fixes? I will send you the download link if you send your email address to simon at spatialdbadvisor dot com
regards
Simon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, I would like to try the release candidate of GeoRaptor. I will send an email to you!
Here is my task:
SELECT /*+ ORDERED*/ b.*
FROM MUB.REGIONAL a,
( SELECT rowid,"OBJECTID",sysdate,systimestamp,current_timestamp,t.SHAPE as
SHAPE
FROM MUB.REGIONAL t
WHERE SDO_FILTER(t.SHAPE,
MDSYS.SDO_GEOMETRY(2003,82301,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)),'querytype=WINDOW')
= 'TRUE'
) b
WHERE MDSYS.SDO_NN(a.SHAPE,MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),'sdo_batch_size=0',1) = 'TRUE'
AND SDO_NN_DISTANCE(1) < 533
AND b.rowid = a.rowid
ORDER BY sdo_nn_distance(1);
I made the change you suggested, but still got the error. Note that this is on 9.2.0.1 so could be some weird bug on that release. Can't find any clues on metalink though.
John
das@dastest> SELECT /*+ ORDERED*/ b.*
2 FROM DAS.ST_LR_PLAN a,
3 ( SELECT /*+LEADING*/ rowid,"ID", sysdate, systimestamp, current_timestamp,t.geometry as geom
4 FROM DAS.ST_LR_PLAN t
5 WHERE SDO_FILTER(t.geometry,
6 MDSYS.SDO_GEOMETRY(2003,262152,NULL,
7 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
8 MDSYS.SDO_ORDINATE_ARRAY(672401,675364,673358,675663)),'querytype=WINDOW') = 'TRUE'
9 ) b
10 WHERE MDSYS.SDO_NN(a.geometry,
11 MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL),
12 'sdo_batch_size=0',1) = 'TRUE'
13 AND SDO_NN_DISTANCE(1) < 5.372495
14 AND b.rowid = a.rowid
15 ORDER BY sdo_nn_distance(1);
SELECT /*+ ORDERED*/ b.*
*
ERROR at line 1:
ORA-29903: error in executing ODCIIndexFetch() routine
ORA-22160: element at index does not exist
Elapsed: 00:01:51.99
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
ROWID OBJECTID SDO_NN_DISTANCE(1) SYSDATE SYSTIMESTAMP CURRENT_TIMESTAMP SHAPE
-------------------------------------------------------------------------------------------------------------
AAAVE7AAMAAAAKbAAI 9 0 07-FEB-11 07-FEB-11 12.02.01.213000000 PM -02:00 07-FEB-11 12.02.01.213000000 PM AMERICA/SAO_PAULO MDSYS.SDO_GEOMETRY(2003,82301,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(603325.3825,7801421.2539,...,603325.3825,7801421.2539))
Sorry! Just to know what is codify text!
Sandro
Sandro,
Ahhh, we are getting somewhere!!!
I am leaning towards my second method as it is far easier to code for all cases.
But, the example I gave you has mistakes in the inner select statement that I let John know about. Could you modify your query and try again? I think it will look like this:
I have contacts in Oracle but it is an endemic problem they need to fix. They are all working on 3.0 and I am still on 2.1 so they probably don't really want to hear. The forum isn't much help either. Anyway, we are looking into how to work more closely with SQL Developer's NLS settings.
Finally, I am preparing a release candidate for our next release: would you be willing to test it for me as it contains the SDO_NN fixes? I will send you the download link if you send your email address to simon at spatialdbadvisor dot com
regards
Simon
Simon,
Yes, I would like to try the release candidate of GeoRaptor. I will send an email to you!
Here is my task:
The result:
Sandro.
Simon,
I made the change you suggested, but still got the error. Note that this is on 9.2.0.1 so could be some weird bug on that release. Can't find any clues on metalink though.
John
das@dastest> SELECT /*+ ORDERED*/ b.*
2 FROM DAS.ST_LR_PLAN a,
3 ( SELECT /*+LEADING*/ rowid,"ID", sysdate, systimestamp, current_timestamp,t.geometry as geom
4 FROM DAS.ST_LR_PLAN t
5 WHERE SDO_FILTER(t.geometry,
6 MDSYS.SDO_GEOMETRY(2003,262152,NULL,
7 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
8 MDSYS.SDO_ORDINATE_ARRAY(672401,675364,673358,675663)),'querytype=WINDOW') = 'TRUE'
9 ) b
10 WHERE MDSYS.SDO_NN(a.geometry,
11 MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL),
12 'sdo_batch_size=0',1) = 'TRUE'
13 AND SDO_NN_DISTANCE(1) < 5.372495
14 AND b.rowid = a.rowid
15 ORDER BY sdo_nn_distance(1);
SELECT /*+ ORDERED*/ b.*
*
ERROR at line 1:
ORA-29903: error in executing ODCIIndexFetch() routine
ORA-22160: element at index does not exist
Elapsed: 00:01:51.99