This discussion is archived
8 Replies Latest reply: Sep 25, 2012 7:51 AM by karel prech RSS

Spatial Index usage with joins.

FlyingGuy Explorer
Currently Being Moderated
So here is my conundrum....

How does one ( if it can be done ) deal with joining in rows from another table when your select is based upon a spatial query.

Oracle EE running on Oracle Iinux running on a 24 core machine, 17 TB's of sas drives, 48 gigs of ram latest everything.

The spatial query:
select
id,
length,
speed_limit,
beg_node_id,
from links
where
sdo_anyinteract(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'
There a 69 million rows in the links table and the query runs very fast ( .04 seconds ) to return 1535 rows so I assume it is using the spatial index. Change the above query to:
select * from links ...
and we are into a full table scan. change the select to include:
select  * /*+ index ( links sidx_links_geom) */ from links ...
and we are back to using the spatial index and things are fast again. So now there is a table of nodes with an ID column that is indexed. I want to join the nodes table in to I can get the nodes that are on each end of the links as:
select
/*+ index (links sidx_links_geom) */
l.id,
l.speed_limit,
l.length,
n.id
from links l
join nodes n on (n.id = l.beg_node_id )
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'
NODES.ID is an indexed column and is the exact same type as LINKS.BEG_NODE_ID. So this leaves me a bit perplexed. The spatial query selects the appropriate set of rows with blinding speed. But try and do a join and we are in the tank speed wise. Any suggestions?
  • 1. Re: Spatial Index usage with joins.
    yhu Journeyer
    Currently Being Moderated
    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)
  • 2. Re: Spatial Index usage with joins.
    FlyingGuy Explorer
    Currently Being Moderated
    Hey there,

    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.

    Best.
  • 3. Re: Spatial Index usage with joins.
    Rick J Newbie
    Currently Being Moderated
    Are the statistics current on the LINK and NODE tables?
  • 4. Re: Spatial Index usage with joins.
    yhu Journeyer
    Currently Being Moderated
    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);
    select l.id, l.speed_limit, l.length, n.id
    from links2 l, node  n 
    where n.id = l.beg_node_id;
    If the last two queries are slow, they will be not spatial-query-related. And
    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.
  • 5. Re: Spatial Index usage with joins.
    FlyingGuy Explorer
    Currently Being Moderated
    Yes Statistics are up to date.
  • 6. Re: Spatial Index usage with joins.
    FlyingGuy Explorer
    Currently Being Moderated
    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!?
  • 7. Re: Spatial Index usage with joins.
    FlyingGuy Explorer
    Currently Being Moderated
    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.
  • 8. Re: Spatial Index usage with joins.
    karel prech Newbie
    Currently Being Moderated
    Hi,

    do you try hint /*+ ordered */ with spatial condition as the first term?

    If you look in Spatial documentation, you find "small" notice about it. :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points