8 Replies Latest reply: Sep 25, 2012 9:51 AM by prechk RSS

    Spatial Index usage with joins.

    FlyingGuy
      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.
          Ying Hu-Oracle
          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
            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
              Are the statistics current on the LINK and NODE tables?
              • 4. Re: Spatial Index usage with joins.
                Ying Hu-Oracle
                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
                  Yes Statistics are up to date.
                  • 6. Re: Spatial Index usage with joins.
                    FlyingGuy
                    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
                      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.
                        prechk
                        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. :)