it looks like the query will first get all the records that satisfy the where clause and then apply the spatial filter on this resultsetWHich WHERE-clause are you talking about? Your query only has an SDO_RELATE in it's WHERE-clause, and SDO_RELATE will indeed first create a subset, using the MBR's of your records, then have a closer look at the filtering geometry you give it. [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_index_query.htm#i1005329]That is how Oracle´s spatial querying works.
You are using a custom SRID (1010101 - it does not exist in my DB). Is the table using the same SRID? If not, transformation takes place and will use up time.
SDO_GEOMETRY(2003,1010101,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(180514.7833451195,1663508.6932339652, 180514.84948680276,1663504.0633161366, 180517.6274375,1663503.9971744534, 180517.69357918325,1663508.4948089155, 180514.7833451195,1663508.6932339652))
I did some tests using SQL Developer (SQL Profiler) and it was possible to create a better execution plan to this query. The time was reduced to 4 seconds.But the SQL Profile seems to have effect only to the query analyzed. I mean, if a change the sdo_geometry parameter, the query takes 7 minutes again.>
Luis A. Paolini wrote:Yes that is correct. If you query a view, you only query the records that the view is giving you. You can't change that, unless you create a query directly on the tables that view is getting it's data from. Which is entirely possible, of course (in this case at least).
1. When I say that the query will first get all the records that satisfy the where clause, i mean the where clause in the View.
Thus, the view is executed and all the records that satisfy the where clause are returned and then the spatial query is executed upon these records.
Am I right?
3. About SQL Developer -> SQL Profiler, what I did was select the query and ask to SQL Developer to run the SQL Tuning Advisor.Do you have access to the view? I mean, can you change it? You may try to use UNION ALL or a JOIN, that might improve the performance of the view. Also: do the columns from the where-clause have the correct and valid indexes on them?
Applying the SQL Profiler generated by the Tuning Advisor, the query time is improved.
Tje point is that the Tuning Advisor just shows the new execution plan versus the original one. It does not show how to change the query to
have the optimized execution plan.
4. I did not tried the SDO_FILTER, but I'll give it a try.Be aware that SDO_FILTER does not give as accurate results as SDO_RELATE. SDO_FILTER only looks at the MBR's of the records, not at the actual geometry. This can sometimes cause surprising results, but it is much faster.