This content has been marked as final. Show 8 replies
How about the following query?
with lv as ( select/*+ materialize index (l sidx_links_geom) */ l.id, l.speed_limit, l.length from links l where sdo_anyinteract(l.geom, sdo_geometry(2003,8307,null, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(-122.228065,37.848629 ,-122.161031,37.825718))) = 'TRUE') select lv.id, lv.speed_limit, lv.length, n.id from lv join nodes n on (n.id = lv.beg_node_id)
Fixed your minor typo ( you forgot to add beg_node_id to the with (...) statement ) and even then the plan reported 4.5 hours. Thanks for the thought though. Even more interesting is the fact from from select f1...fn to select * throws the sql parser into a table scan.
But its Monday morning so it will be interesting to see what other opinions come.
Are the statistics current on the LINK and NODE tables?
Please measure the three queries?
create table links2 as select/*+ index (l sidx_links_geom) */ * from links l where sdo_anyinteract(l.geom, sdo_geometry(2003,8307,null, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(-122.228065,37.848629 ,-122.161031,37.825718))) = 'TRUE';
select l.id, l.speed_limit, l.length, n.id from links2 l join nodes n on (n.id = l.beg_node_id);
If the last two queries are slow, they will be not spatial-query-related. And
select l.id, l.speed_limit, l.length, n.id from links2 l, node n where n.id = l.beg_node_id;
it means that either node.id is not indexed, or the join plan is not right.
You can create an index on node(id), and try the following hint /*+ leading(l n) USE_NL_WITH_INDEX(l node_id_idx) */
if it helps.
Yes Statistics are up to date.
Ok, So I must ave been half asleep when I read you post because I swear I don;t remember seeing that hint in there. Did you modify it?
At any rate, that query with the join now runs in .6 seconds!?
All the appropriate columns are indexed. I will try your idea though. I ran Yuh's suggested query with the materialized index hint and the query no runs in .6 seconds. I have to figure out what that hint is doing.
do you try hint /*+ ordered */ with spatial condition as the first term?
If you look in Spatial documentation, you find "small" notice about it. :)