This discussion is archived
14 Replies Latest reply: Oct 17, 2013 11:42 AM by Siva Ravada RSS

SDO_DISTANCE performance

Rinne Newbie
Currently Being Moderated

Table A_SPATIAL has 15 million records with different longitude and latitude values. I also have the column SHAPE SDO_GEOMETRY, which stores SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL), NULL, NULL).

 

I created the function and index below:

create or replace function get_long_lat_pt(longitude in number,  
                                         latitude in number)  
return SDO_GEOMETRY deterministic is  
begin  
return sdo_geometry(2001, 8307,  
                sdo_point_type(longitude, latitude, NULL),NULL, NULL);  
end;  
/ 
INSERT INTO user_sdo_geom_metadata VALUES(  
'A_SPATIAL', -- table  
'SHAPE', -- function  
   mdsys.sdo_dim_array( 
      mdsys.sdo_dim_element('LONGITUDE', -180, 180, 0.005),  
      mdsys.sdo_dim_element('LATITUDE', -90, 90, 0.005)  
   ), 
   8307  -- SRID  
    ); 
commit;  
CREATE INDEX A_SPATIAL_SHAPE_IDX  
ON A_SPATIAL(Shape)  
INDEXTYPE IS MDSYS.SPATIAL_INDEX;  

 

Then when I execute the query below, it does a full table scan. Even if I have the index above, the index wouldn't get used because I am using SDO_DISTANCE to wrap around it I suppose. The longitude and latitude provided to the function get_long_lat_pt below are user input and they all vary.

 

select * from A_SPATIAL where  
SDO_GEOM.SDO_DISTANCE(SHAPE, get_long_lat_pt(95.224, 31.601), 1, 'unit=MILE') < 20;  

 

Questions:

1. What is the most optimal way of running such query to perform the best? I guess the full table scan is obvious since I need to calculate the distance, but is there a way to improve performance?

2. Is the index above even useful at all?

3. I could use SDO_WITHIN_DISTANCE in my case, but it would perform a full table scan anyway because I still need to calculate the distance. Would this perform better than the example above?

 

Thank you.

  • 1. Re: SDO_DISTANCE performance
    _jum Journeyer
    Currently Being Moderated

    I built up a little test case with 10.000 random points:

     

    CREATE OR REPLACE FUNCTION get_long_lat_pt(longitude in number, latitude in number)
      RETURN SDO_GEOMETRY deterministic is
    BEGIN
      RETURN sdo_geometry(2001, 8307, sdo_point_type(longitude, latitude, NULL),NULL, NULL);
    END;
    /
    CREATE TABLE a_spatial
    (id    NUMBER,
      SHAPE SDO_GEOMETRY);
    /
    INSERT INTO a_spatial
    SELECT level,  get_long_lat_pt(DBMS_RANDOM.VALUE(-180,180),  DBMS_RANDOM.VALUE(-90, 90))  FROM dual
    CONNECT BY level<=10000;
    INSERT INTO user_sdo_geom_metadata VALUES(
    'A_SPATIAL', -- table
    'SHAPE', -- function
       mdsys.sdo_dim_array(
          mdsys.sdo_dim_element('LONGITUDE', -180, 180, 0.005),
          mdsys.sdo_dim_element('LATITUDE', -90, 90, 0.005)
       ),
       8307  -- SRID
        );
    COMMIT;
    CREATE INDEX A_SPATIAL_SHAPE_IDX
        ON A_SPATIAL(Shape)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX;
    
    
    
    

     

    SET TIMI ON 

     

    SELECT id, SDO_GEOM.SDO_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),0.001) dist
      FROM a_spatial g
    WHERE SDO_GEOM.SDO_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),0.001) <=250000;
    
    
    
    
    
    

     

            ID       DIST

    ---------- ----------

          2217 140741,629

          2317 143505,364

          4841 131930,291

          8865 229621,355

     

    4 rows selected.

    Elapsed: 00:00:06.17

     

    SELECT id,
           SDO_GEOM.SDO_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),0.001) dist
      FROM a_spatial g
    WHERE SDO_WITHIN_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),'distance=250000') = 'TRUE';
    
    
    
    
    
    
    
    
    

     

    IDDIST

    ---------- ----------

    2217 140741,629
    2317 143505,364
    4841 131930,291
    8865 229621,355

     

    4 rows selected.

    Elapsed: 00:00:00.12

     

    SELECT id, dist FROM
    (SELECT g.*, SDO_NN_DISTANCE(1) dist
        FROM a_spatial g
      WHERE SDO_NN(shape, get_long_lat_pt(95.224, 31.601),'sdo_num_res=200', 1)='TRUE')
    WHERE dist<=250000;
    
    
    
    
    
    
    
    
    

     

    IDDIST

    ---------- ----------

    2217 140741,629
    2317 143505,364
    4841 131930,291
    8865 229621,355

     

    4 rows selected.

    Elapsed: 00:00:00.17

     

    From this test case you should use (as recommended in the manual) better SDO_WITHIN_DISTANCE or SDO_NN.

     

    BTW IMO the SQL syntax highlighting is a mess. Can anybody explain how to get a newline between the command lines ?

  • 2. Re: SDO_DISTANCE performance
    Rinne Newbie
    Currently Being Moderated

    I tested all three scenarios in my query and SDO_GEOM.SDO_DISTANCE ended up being the fastest. Actually, the other two ran for 10 minutes before I canceled the query. The first one only took two minutes.

     

    Regarding SDO_WITHIN_DISTANCE, how can I provide the max_resolution and min_resolution given that I have longitude, latitude, and all I need is a distance from it?

  • 3. Re: SDO_DISTANCE performance
    _jum Journeyer
    Currently Being Moderated

    Tested with ORACLE 10.2.0.5 and ORACLE 11.2.0.3 with nearly the same results.

    Which ORACLE version do you use?
    Does the test case above with 25.000 rows take 2 minutes / more than 10 minutes ?

  • 4. Re: SDO_DISTANCE performance
    Rinne Newbie
    Currently Being Moderated

    I am sorry, you are right. When I ran your test scripts, I get the same results as you. I'm on Oracle 11.2. But...

     

    My partitioned table (A_SPATIAL) has 20 million plus records. When I execute my query, here is what my query looks like (simplified where clause):


    where col1 = 'A' and longitude between :a_long_1 and :a_long_2 and latitude between :b_lat_1 and :b_lat_2
    and SDO_GEOM.SDO_DISTANCE(SHAPE, get_long_lat_pt(:SDO_POINT_LONG, :SDO_POINT_LAT), 1, 'unit=MILE') < :400;
    

    The above executes faster than:

     

    where col1 = 'A' and longitude between :a_long_1 and :a_long_2 and latitude between :b_lat_1 and :b_lat_2
    and SDO_WITHIN_DISTANCE(shape, get_long_lat_pt(:SDO_POINT_LONG, :SDO_POINT_LAT),
    'distance=400, max_resolution=400 , unit=mile') = 'TRUE';
    

    I also realized that the second query doesn't return any records, where the first one returns about 300 records. I also realized the second query is not using the index. What am I missing?

     

    My longitude and latitude values mark the center point of zip codes. So all the records that have the same zip codes will have the same long and lat values. Is there a faster way to process this with the zip codes?

  • 5. Re: SDO_DISTANCE performance
    Stefan Jager Journeyer
    Currently Being Moderated

    Rinne wrote:

    Table A_SPATIAL has 15 million records

     

    My partitioned table (A_SPATIAL) has 20 million plus records

    So which one is it? Not that 5 million records make much of a difference, but the partitioning DOES make a difference. Or does your table grow by 5 million records per 3 days?

    Did you partition your spatial index? You should, because that will make a HUGE difference. Oracle will use partiotion pruning on the root mbr of every partition, and this adds tremendous performance benefits for spatial queries.

     

    Secondly:

    Rinne wrote:

    where longitude between :a_long_1 and :a_long_2 and latitude between :b_lat_1 and :b_lat_2 

    Why this way? Why not use SDO_FILTER? Then again, if you have your table partitioned spatially, this is not even necessary. The spatial query will already do this for you, so it's adding overhead IMHO. I would leave this out, especially if I had partitioned my table and index. Not needed.

     

    Thirdly:

    I would constrain my index to the SDO_POINT type. This may help improve performance. SDO_WITHIN_DISTANCE should give you better performance than using SDO_GEOM.SDO_DISTANCE, but:

    Rinne wrote:

    simplified where clause

    If you do not show is the whole SQL, how can we possibly expect to determine where things go wrong? It may very well be that the optimizer decides to use a FTS because of some other parts of your where-clause, but we can't tell. Also: have you done Explain Plan and such things to see exactly where it goes "wrong"? That should tell you which bit of your query is causing the FTS.

     

    Lastly: How about your tablespaces, especially your temp table space? Is that big enough to hold the sorting that may need to be done? Or does Oracle have to keep swapping because your temporary table space is too small? With tables this size it is also beneficial to use a different tablespace for your index, btw.

     

    HTH,

    Stefan

  • 6. Re: SDO_DISTANCE performance
    _jum Journeyer
    Currently Being Moderated

    The only difference in your (simplified) queries is the additional parameter max_resolution, it is only applied in the second one, but max_resolution=400 is pretty large ?!

    Don't know if it makes a difference if one separates the parameters with ',' I got the same result.

    'distance=400, max_resolution=400 , unit=mile') = 'TRUE';  

     

    'distance=400 max_resolution=400, unit=mile') = 'TRUE';  


    You could try to force ORACLE to use the INDEX and test the execution plan:

    SELECT /*+ INDEX (g a_spatial_shape_idx) */  
           SDO_GEOM.SDO_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),0.001,'unit=mile') dist
      FROM a_spatial g
     WHERE SDO_WITHIN_DISTANCE(shape, get_long_lat_pt(95.224, 31.601),'distance=400 unit=mile') = 'TRUE'
     ORDER BY dist;

     

    If you could use the zip code instead of lat/lon to query the results, of course create an index on the zip code, apply it in the WHERE clause and test the execution plan.


     

  • 7. Re: SDO_DISTANCE performance
    Stefan Jager Journeyer
    Currently Being Moderated

    Jum, we must have been typing at the same time!

  • 8. Re: SDO_DISTANCE performance
    _jum Journeyer
    Currently Being Moderated

    Early birds (Germany) 

  • 9. Re: SDO_DISTANCE performance
    Rinne Newbie
    Currently Being Moderated

    max_resolution=400 is pretty large ?!

    I read the documentation but was a bit confused. Does this mean that it "draws" a square of 400 miles and it would consider data points only inside the 400 square mile? How do I use max_resolution to my advantage? If I'm looking for data points within 400 miles, I know for sure I am only looking inside the 400 square mile. That is what I tried to achieve by providing longitudes and latitudes. I'm basically drawing a square, and have indexes on longitude and latitude so that it only calculates the distance for data points within that range.

     

    Why this way? Why not use SDO_FILTER? Then again, if you have your table partitioned spatially, this is not even necessary. The spatial query will already do this for you, so it's adding overhead IMHO. I would leave this out, especially if I had partitioned my table and index. Not needed.

     

    How do you recommend I partition spatially?

  • 10. Re: SDO_DISTANCE performance
    Rinne Newbie
    Currently Being Moderated

    I tried to simplify the problem but I understand I'm hiding some valuable information. Here is the full query:

     

     

    SELECT SELECT /*+ INDEX (A_SPATIAL_SHAPE_IDX_PP)*/ 
      C.ID,
      C.NAME,
      DIM.DESC,
      VEH.DESC
      FROM A_SPATIAL C,
      V_DIM veh,
      JUNK_DIM dim
      WHERE
      C.V_DIM_ID = veh.V_DIM_ID
      and C.JUNK_DIM_ID = DIM.JUNK_DIM_ID
      and C.A_FLG           =:P_A_FLG
      AND veh.MDL_YR             =:P_MDL_YR
      AND veh.D_ID         =:P_D_ID
      AND veh.MODEL          =:P_MODEL
      AND veh.TRIM          =:P_TRIM
      and veh.V_DETAIL =:P_V_DETAIL
      AND PRICE_S = 'Y'
      and dim.veh_normal = 'Y'
      and odom_plus_0 = 'Y'
      AND C.C_AGE_B     >=to_date(:P_C_AGE_B, 'DD-Mon-YYYY')
      AND (C.VC_LONGITUDE BETWEEN :P_VC_LONGITUDE1 AND :P_VC_LONGITUDE2)
      AND (C.VC_LATITUDE BETWEEN :P_VC_LATITUDE1 AND :P_VC_LATITUDE2)
      and SDO_GEOM.SDO_DISTANCE(SHAPE, get_long_lat_pt(:SDO_POINT_TYPE1, :SDO_POINT_TYPE2), 1, 'unit=MILE') < :SDO_POINT_DISTANCE;
    414 rows selected.
    Execution Plan
    ----------------------------------------------------------                                                                                                                                              
    Plan hash value: 1442383733                                                                                                                                                                             
                                                                                                                                                                                                            
    --------------------------------------------------------------------------------------------------------------------------------------                                                                  
    | Id  | Operation                                                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                  
    --------------------------------------------------------------------------------------------------------------------------------------                                                                  
    |   0 | SELECT STATEMENT                                     |                               |     1 |   246 |   480   (1)| 00:00:06 |       |       |                                                                  
    |*  1 |  FILTER                                                          |                               |       |       |            |          |       |       |                                                                  
    |   2 |   NESTED LOOPS                                            |                              |     1 |   246 |   480   (1)| 00:00:06 |       |       |                                                                  
    |   3 |    NESTED LOOPS                                           |                              |     1 |   209 |   479   (1)| 00:00:06 |       |       |                                                                  
    |*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| A_SPATIAL             |     1 |   194 |   478   (1)| 00:00:06 | ROWID | ROWID |                                                                  
    |*  5 |      INDEX RANGE SCAN                                | LONG_LAT_IDX         | 270 |       |   213   (0)| 00:00:03 |       |       |                                                                  
    |*  6 |     TABLE ACCESS BY INDEX ROWID            | JUNK_DIM                |     1 |    15 |     1   (0)| 00:00:01 |       |       |                                                                  
    |*  7 |      INDEX UNIQUE SCAN                               | SYS_C00149665       |     1 |       |     0   (0)| 00:00:01 |       |       |                                                                  
    |*  8 |    TABLE ACCESS BY INDEX ROWID             | V_DIM                      |     1 |    37 |     1   (0)| 00:00:01 |       |       |                                                                  
    |*  9 |     INDEX UNIQUE SCAN                                | V_DIM_ID_PK           |     1 |       |     0   (0)| 00:00:01 |       |       |                                                                  
    --------------------------------------------------------------------------------------------------------------------------------------                                                                  
                                                                                                                                                                                                            
    Predicate Information (identified by operation id):                                                                                                                                                     
    ---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                            
       1 - filter(TO_NUMBER(:P_VC_LATITUDE1)<=TO_NUMBER(:P_VC_LATITUDE2) AND                                                                                                                    
                  TO_NUMBER(:P_VC_LONGITUDE1)<=TO_NUMBER(:P_VC_LONGITUDE2))                                                                                                                     
       4 - filter("C"."A_FLG"=:P_A_FLG AND "C"."C_AGE_B">=TO_DATE(:P_C_AGE_B,'DD-M                                                                          
                  on-YYYY') AND "SDO_GEOM"."SDO_DISTANCE"("SHAPE","GET_LONG_LAT_PT"(:SDO_POINT_TYPE1,:SDO_POINT_TYPE2),1,'unit=MILE')<TO_NUMBER(                                                            
                  :SDO_POINT_DISTANCE))                                                                                                                                                                     
       5 - access("C"."VC_LONGITUDE">=TO_NUMBER(:P_VC_LONGITUDE1) AND                                                                                                                
                  "C"."VC_LATITUDE">=TO_NUMBER(:P_VC_LATITUDE1) AND                                                                                                                  
                  "C"."VC_LONGITUDE"<=TO_NUMBER(:P_VC_LONGITUDE2) AND                                                                                                                
                  "C"."VC_LATITUDE"<=TO_NUMBER(:P_VC_LATITUDE2))                                                                                                                     
           filter("C"."VC_LATITUDE">=TO_NUMBER(:P_VC_LATITUDE1) AND                                                                                                                  
                  "C"."VC_LATITUDE"<=TO_NUMBER(:P_VC_LATITUDE2))                                                                                                                     
       6 - filter("DIM"."VEH_NORMAL"='Y' AND "PRICE_S"='Y' AND "ODOM_PLUS_0"='Y')                                                                                                                     
       7 - access("C"."JUNK_DIM_ID"="DIM"."JUNK_DIM_ID")                                                                                                                                   
       8 - filter("VEH"."TRIM"=:P_TRIM AND "VEH"."V_DETAIL"=:P_V_DETAIL AND "VEH"."MODEL"=:P_MODEL AND                                                                                      
                  "VEH"."D_ID"=TO_NUMBER(:P_D_ID) AND "VEH"."MDL_YR"=TO_NUMBER(:P_MDL_YR))                                                                                                    
       9 - access("C"."V_DIM_ID"="VEH"."V_DIM_ID")                                                                                                               
    Statistics
    ----------------------------------------------------------                                                                                                                                              
           7434  recursive calls                                                                                                                                                                            
              0  db block gets                                                                                                                                                                              
          35027  consistent gets                                                                                                                                                                            
            495  physical reads                                                                                                                                                                             
          26136  redo size                                                                                                                                                                                  
          56875  bytes sent via SQL*Net to client                                                                                                                                                           
           4373  bytes received via SQL*Net from client                                                                                                                                                     
             29  SQL*Net roundtrips to/from client                                                                                                                                                          
              0  sorts (memory)                                                                                                                                                                             
              0  sorts (disk)                                                                                                                                                                               
            414  rows processed                                                                                                                                                                             

     

    Table A_SPATIAL is partitioned by A_FLG (list) and it only has two values. I realized that I can't create local partitioned index on the SHAPE column because the table is not range partitioned.

     

    This query takes about a minute and was wondering if I can get it to execute in less than 15 seconds.

  • 11. Re: SDO_DISTANCE performance
    Rinne Newbie
    Currently Being Moderated

    Now when I use SDO_WITHIN_DISTANCE without the longitude and latitudes, I get a different query and now it takes 15 minutes to execute.

     

    SELECT SELECT /*+ INDEX (A_SPATIAL_SHAPE_IDX_PP)*/
          C.ID,
          C.NAME,
          DIM.DESC,
          VEH.DESC
        FROM A_SPATIAL C,
          V_DIM veh,
          JUNK_DIM dim
        WHERE
          C.V_DIM_ID = veh.V_DIM_ID
        and C.JUNK_DIM_ID = DIM.JUNK_DIM_ID
        and C.A_FLG           =:P_A_FLG
        AND veh.MDL_YR             =:P_MDL_YR
        AND veh.D_ID         =:P_D_ID
        AND veh.MODEL          =:P_MODEL
        AND veh.TRIM          =:P_TRIM
        and veh.V_DETAIL =:P_V_DETAIL
        AND PRICE_S = 'Y'
        and dim.veh_normal = 'Y'
        and odom_plus_0 = 'Y'
        AND C.C_AGE_B     >=to_date(:P_C_AGE_B, 'DD-Mon-YYYY')
        -- AND (C.VC_LONGITUDE BETWEEN :P_VC_LONGITUDE1 AND :P_VC_LONGITUDE2)
        -- AND (C.VC_LATITUDE BETWEEN :P_VC_LATITUDE1 AND :P_VC_LATITUDE2)
    and SDO_WITHIN_DISTANCE(shape, get_long_lat_pt(:SDO_POINT_TYPE1, :SDO_POINT_TYPE2),
    'distance=1 , unit=MILE') = 'TRUE';
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2116853583
    -------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                                          | Name                                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                      |                                             |     1 |   246 |     4  (25)| 00:00:01 |       |       |
    |   1 |  NESTED LOOPS                                            |                                             |     1 |   246 |     4  (25)| 00:00:01 |       |       |
    |*  2 |   HASH JOIN                                                  |                                            |     1 |   231 |     3  (34)| 00:00:01 |       |       |
    |*  3 |    TABLE ACCESS BY INDEX ROWID             | V_DIM                                 |     1 |    37 |     2   (0)| 00:00:01 |       |       |
    |   4 |     BITMAP CONVERSION TO ROWIDS            |                                            |       |       |            |          |       |       |
    |   5 |      BITMAP AND                                             |                                            |       |       |            |          |       |       |
    |*  6 |       BITMAP INDEX SINGLE VALUE                | BIT_TRIM                             |       |       |            |          |       |       |
    |*  7 |       BITMAP INDEX SINGLE VALUE                 | BIT_V_DETAIL                      |       |       |            |          |       |       |
    |*  8 |    TABLE ACCESS BY GLOBAL INDEX ROWID| A_SPATIAL                           |  3315 |   628K|     0   (0)| 00:00:01 | ROWID | ROWID |
    |*  9 |     DOMAIN INDEX                                         | A_SPATIAL_SHAPE_IDX_PP |       |       |     0   (0)| 00:00:01 |       |       |
    |* 10 |   TABLE ACCESS BY INDEX ROWID               | JUNK_DIM                            |     1 |    15 |     1   (0)| 00:00:01 |       |       |
    |* 11 |    INDEX UNIQUE SCAN                                | SYS_C00149665                   |     1 |       |     0   (0)| 00:00:01 |       |       |
    -------------------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("C"."A_SPATIAL_ID"="VEH"."A_SPATIAL_ID")
       3 - filter("VEH"."MODEL"=:P_MODEL AND "VEH"."D_ID"=TO_NUMBER(:P_D_ID) AND
                  "VEH"."MDL_YR"=TO_NUMBER(:P_MDL_YR))
       6 - access("VEH"."TRIM"=:P_TRIM)
       7 - access("VEH"."V_DETAIL"=:P_V_DETAIL)
       8 - filter("C"."A_FLG"=:P_A_FLG AND "C"."VEH_C_AGE_B">=TO_DATE(:P_C_AGE_B,'DD-
                  Mon-YYYY'))
       9 - access("MDSYS"."SDO_WITHIN_DISTANCE"("SHAPE","GET_LONG_LAT_PT"(:SDO_POINT_TYPE1,:SDO_POINT_TYPE2),'distance=1 ,
                  unit=MILE')='TRUE')
      10 - filter("DIM"."VEH_NORMAL"='N' AND "PRICE_S"='Y' AND "ODOM_PLUS_0"='Y')
      11 - access("C"."JUNK_DIM_ID"="DIM"."JUNK_DIM_ID")
    Statistics
    ----------------------------------------------------------
           1386  recursive calls
              0  db block gets
          18615  consistent gets
          11969  physical reads
         633564  redo size
           2315  bytes sent via SQL*Net to client
           2584  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
              2  rows processed
    
    
    
  • 12. Re: SDO_DISTANCE performance
    _jum Journeyer
    Currently Being Moderated

    How long does only the spatial query take ?

    SELECT SELECT /*+ INDEX (C A_SPATIAL_SHAPE_IDX_PP)*/ 
          C.ID, 
          C.NAME 
    FROM A_SPATIAL C 
    WHERE SDO_WITHIN_DISTANCE(shape, get_long_lat_pt(:SDO_POINT_TYPE1, :SDO_POINT_TYPE2), 'distance=1 unit=MILE')='TRUE' ;
  • 13. Re: SDO_DISTANCE performance
    Stefan Jager Journeyer
    Currently Being Moderated

    Rinne wrote:

     

    How do you recommend I partition spatially?

     

    Table A_SPATIAL is partitioned by A_FLG (list) and it only has two values.

    Hmm. OK, so assuming you have 20 million rows you have two partitions with records in them, each roughly 10 million. How did you think that was going to help?

     

    You also said your data is centerpoints of zipcodes. I do not know where you are from, but where I come from zipcodes (or postcodes, as they are called) are max 6 positions. first 4 are digits, last two are characters. so the more positions you add, the more detail you get. This you could use to group zipcodes, in my example you could take the first 3 digits for example, create an id out of that and assuming your zipcodes are spatially close together you will end up with a spatially partitioned table, even if the partitioning is not actually done spatially. You could do something like this for municipalities or townships or counties too. But i do not know your data, so I can't give you more than pointers like this.

     

    Next create partitioned spatial index, for that you can follow the documentation.

     

    Lastly: make sure your partitions cover area's that are a little bit larger than the average distance that you will be querying on. That way most of your queries will only need one partition, thus giving you the best performance.

     

    HTH,

    Stefan

  • 14. Re: SDO_DISTANCE performance
    Siva Ravada Expert
    Currently Being Moderated

    You should not use the max_resolution/min_resolution with the distance queries.

    The purpose of those parameters is to eliminate features that are very small or very large from the result set.

    In your case, since they are all points, you should not use these parameters.

     

    Since the table is partitioned, is the spatial index created as partitioned local index ?

     

    siva

Legend

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