Assuming the single "window" geometry is in table1 and the target geometry is in table2, then I'd suggest you write the query as follows:
select /*+ ordered */ t2.id from table1 t1, table2 t2 where sdo_anyinteract(t2.geom, t1.geom) = 'TRUE';
If you're comparing a single geometry with lots of other geometries, then I'd be surprised if this query takes more than a few seconds - obviously that depends on your data, your environment, etc.
What version of table are you on? How many rows does the query return? Are these very large and complex features?
John O'Toole wrote:
What version of table database are you on?
John O'Toole wrote:
Are these very large and complex features?
If they are, a simple SDO_FILTER might speed things up. Depending on the accuracy of the comparison needed, of course, but since OP is using anyinteract I'd say the primary filter would be enough, if performance is such an issue.
the table1 has too many objects (rivers), but I do select only one object (geometry).
the table2 has also too many regions/lands.
Using your solution (the order of tables in the from-clause as recommended in the reference), it takes 4 min !
but as i said, if I put the table2 (lands) at first, i get about 2 min! so as SDO_Relate exactly.
The database is 10g.
I deceided to use the following>
select B.someAttr from lands B, rivers A where sdo_relate(A.line,B.geom, 'mask=anyintract') ='True'
it gives me 58 seconds.
Here i followed the recommendation of putting B at first in From-clause. But without the ordered hint which involved 6 mints!!!
Also to be mentioned, the qwery window is now multi-geometries.
58 seconds is still ridiculously long for such a simple query. Can you give us your table descriptions, indexes, and your full exact query, and maybe some sample data so that we can reproduce your situation? Just a few records will do.
Because the way you are doing it here is, in my opinion, not the correct way. It looks like you want to get some attribute from all lands that are crossed by a certain river, correct? I would query that as follows:
select /*+ ORDERED */ L.countryname from rivers R, lands L where R.rivername='Rhine' and sdo_relate(L.geom, R.line, 'mask=anyinteract')='TRUE';
Also: if your select clause on the second geometry (A.ID='value', in your example) only returns one row, it will speed up the spatial selection. Does that ID-column have an index?
first, yes correct. Iknow that my Solution seems to be not the best but it is till now '!, However, it was the last Experiment i did.
This is why i put this post, cus every thing i read in the Spatial reference of Oracle relating Performance optimization seems to be incorrect. !
Your solution was the first i've tried (it is listed in the reference) -> it takes 4 min. What is more, no matter i use ordered hint or not, it always 4 min!
lastly, sorry i can't describe my data cus it is not mine. but the result is abot 1000 rows from thousands of rows.
1 person found this helpful
Well, I apply your query to the follwoing tables:
Table2 has about 15ooo ploygons.
The so-called qwery window is a line geometry from a table contains 1000 line.
The probed line interacts topologically with 300 Polygons.
select '/*+ ORDERED */' count(P.name)
from lines L, polygons P
where L.name='' and
sdo_relate(P.geom,L.geom,'mask=anyintract')= 'True'2 min !!!
Now using Count(), this is really the best solution. all other orders(Solutions) take more and some of took 10 min.
Well, if you can't or won't give us some more information about your tables, indices etc there's not much we can do I'm afraid.
I do know that those response times are ridiculous, so there must be something not right. I've worked with tables that had 9-15 million records in them and have had response times of seconds. 15.000 records is nothing for Oracle. Can you post an explain plan of your query? That might give us some indication at least.