4 Replies Latest reply: Apr 4, 2013 8:12 AM by B Hall RSS

    Tune oracle spatial index

    Albin Joseph Paul
      Hi everyone,

      how to tune spatial index in oracle? Pls advice

      Thanks in Advance
        • 1. Re: Tune oracle spatial index
          John O'Toole
          Hi Albin,

          Oracle Spatial uses RTree indexes - these don't require any tuning. In the past Quadtree indexes required a bit of tuning, but they've been deprecated in favour of RTree indexes.

          There are a few parameters that are worth setting - check this link: http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_objindex.htm#i78196
          "layer_gtype" should be set where possible - it ensures that only the specified geometry type can be added to the indexed column and it has performance benefits for point data.
          The other index parameters start to become important once you move towards very large data sets.

          • 2. Re: Tune oracle spatial index
            Albin Joseph Paul
            so how can we improve the performance of spatial queries?
            • 3. Re: Tune oracle spatial index
              John O'Toole
              That's a very open ended question. When posting questions on this forum you'll get better results if you have very specific questions. So do you have a specific spatial query which isn't performing well? If so then post as much information as you can about it.
              - Exactly what version of Oracle database?
              - What is the SQL query you're running - if possible include the explain plan
              - What is your expected response time for the query?
              - How much data is in the table and what kind of data is it
              - Is possible provide some information about the use case behind the query you're running.

              There are various techniques available to make spatial queries perform as you'd expect, but I'm not going to waste my time going through them here until you provide some useful information.

              I'd recommend having a look at this presentation for some advice:
              Also have a look at this:
              • 4. Re: Tune oracle spatial index
                B Hall

                Unless you do a lot of full-view reads of your data, or don't use to - you should apply the work-around highlighted in this thread. Otherwise, you will see very slow response to spatial queries.

                Re: to upgrade = SLOW sdo_geometry return