1 2 Previous Next 18 Replies Latest reply: May 29, 2012 9:51 AM by Waldecir P. Junior RSS

    SDO_ANYINTERACT (Performance issue)

    Waldecir P. Junior
      Hi,

      I have a big table with 12 million records and this table contains a spatial column which stores only points.

      I want to obtain the number of points with some relationship with a polygon that has 5 vertices and 150km².

      I'll explain what I did:
      CREATE TABLE PROSPECT
      (
          COD_PROSPECT NUMBER NOT NULL,
          UFMUN_COD    NUMBER NOT NULL,
          GEOM         MDSYS.SDO_GEOMETRY,
          CONSTRAINT PK_PROSPECTT PRIMARY KEY (COD_PROSPECT) ENABLE
      )
          PARTITION BY RANGE(UFMUN_COD)
          (
             PARTITION NORTE         VALUES LESS THAN (211400),
             PARTITION NORDESTE      VALUES LESS THAN (317220),
             PARTITION SUDESTE_MENOR VALUES LESS THAN (355030),
             PARTITION SAO_PAULO     VALUES LESS THAN (355730),
             PARTITION SUDESTE_MAIOR VALUES LESS THAN (412880),
             PARTITION SUL           VALUES LESS THAN (500840),
             PARTITION CENTRO_OESTE  VALUES LESS THAN (522230)
          );
      The table is partitioned by UFMUN_COD field.
      CREATE INDEX IDX_PROSP_UFMUN_COD ON PROSPECT(UFMUN_COD) LOCAL;
      
      ALTER TABLE PROSPECT PARALLEL 10;
      
      CREATE INDEX IDX_PROSPECT_GEOM ON PROSPECT(GEOM) 
      INDEXTYPE IS MDSYS.SPATIAL_INDEX 
      PARAMETERS ('WORK_TABLESPACE=ONMAPS_WORK LAYER_GTYPE=POINT') LOCAL PARALLEL;
      After creating the table I inserted the 12 million records and ran the query:
      SELECT COUNT(1) AS TOTAL
      FROM ALPHABASE.PROSPECT A
      WHERE A.UFMUN_COD = 355030 AND
      SDO_ANYINTERACT(
         A.GEOM, 
         MDSYS.SDO_GEOMETRY(2003, 
                            8307,
                            NULL, 
                            MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), 
                            MDSYS.SDO_ORDINATE_ARRAY(-46.7386735149995, -23.5904745753416, 
                                                     -46.5616191027776, -23.5904745753416, 
                                                     -46.5616191027785, -23.5055971006428, 
                                                     -46.7386735149986, -23.5055971006428, 
                                                     -46.7386735149995, -23.5904745753416))
      ) = 'TRUE'; 
      TOTAL                  
      ------------
      2183656  
      Problem? It takes 480 seconds to complete.
      Am I doing something wrong?
      Is there a way to improve the performance of these kind of query?

      Thanks!
      Waldecir

      Edited by: Waldecir P. Junior on 03/05/2012 05:31

      Edited by: Waldecir P. Junior on 03/05/2012 05:43

      Edited by: Waldecir P. Junior on 03/05/2012 05:48

      Edited by: Waldecir P. Junior on 03/05/2012 05:50
        • 1. Re: SDO_ANYINTERACT (Performance issue)
          Ivan Bush
          Waldecir,

          Can you please post your query and index creation so they are all visible.

          Regards

          Ivan
          • 2. Re: SDO_ANYINTERACT (Performance issue)
            Waldecir P. Junior
            Hi Ivan,

            I didn't understand. I've already posted the query and the index creation.
            I indented the query and index creation.
            Is it ok now?

            Regards,
            Waldecir
            • 3. Re: SDO_ANYINTERACT (Performance issue)
              John O'Toole
              Hi,

              Can you post your Oracle version and the explain plan for the query? Have a read of this post for details of how to generate the explain plan:
              When your query takes too long ...

              When reading the explain plan we'll try to see if the optimizer did partition pruning.
              Can you try running the query without the "A.UFMUN_COD = 355030" predicate? Is it faster or slower without that? If faster, then consider adding a NO_INDEX hint.

              Also, how many rows do you have per partition?
              SELECT UFMUN_COD, COUNT(*)
              FROM PROSPECT
              GROUP BY UFMUN_COD;
              John
              • 4. Re: SDO_ANYINTERACT (Performance issue)
                Waldecir P. Junior
                Hi John,

                My Oracle version is 11.2.0.3.

                Follows the explain plan:
                PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
                ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
                Plan hash value: 2479220014                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                             
                -------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
                | Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                    
                -------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
                |   0 | SELECT STATEMENT                    |                   |     1 |   101 | 14448   (1)| 00:02:54 |       |       |                                                                                                                                                                                    
                |   1 |  SORT AGGREGATE                     |                   |     1 |   101 |            |          |       |       |                                                                                                                                                                                    
                |   2 |   PARTITION RANGE SINGLE            |                   |  5687 |   560K| 14448   (1)| 00:02:54 |     4 |     4 |                                                                                                                                                                                    
                |*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| PROSPECT          |  5687 |   560K| 14448   (1)| 00:02:54 |     4 |     4 |                                                                                                                                                                                    
                |*  4 |     DOMAIN INDEX                    | IDX_PROSPECT_GEOM |       |       |            |          |       |       |                                                                                                                                                                                    
                -------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                             
                Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
                ---------------------------------------------------                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                             
                   3 - filter("A"."UFMUN_COD"=355030)                                                                                                                                                                                                                                                                        
                   4 - access("MDSYS"."SDO_ANYINTERACT"("A"."GEOM","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"MDSYS"."SDO_ELEM_INFO_A                                                                                                                                                                                            
                              RRAY"(1,1003,1),"MDSYS"."SDO_ORDINATE_ARRAY"((-46.7386735149995),(-23.5904745753416),(-46.5616191027776),(-23.590                                                                                                                                                                              
                              4745753416),(-46.5616191027785),(-23.5055971006428),(-46.7386735149986),(-23.5055971006428),(-46.7386735149995),(                                                                                                                                                                              
                              -23.5904745753416))))='TRUE')                                                                                                                                                                                                                                                                  
                
                20 linhas selecionadas
                The distribution of rows in the partitions is:
                SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'PROSPECT';
                TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION     NUM_ROWS               
                ------------------------------ ------------------------------ ---------------------- ---------------------- 
                PROSPECT                       NORTE                          1                      9                      
                PROSPECT                       NORDESTE                       2                      1550                   
                PROSPECT                       SUDESTE_MENOR                  3                      4188035                
                PROSPECT                       SAO_PAULO                      4                      8530603                
                PROSPECT                       SUDESTE_MAIOR                  5                      4                      
                PROSPECT                       SUL                            6                      11                     
                PROSPECT                       CENTRO_OESTE                   7                      21                         
                The query execution without the "A.UFMUN_COD = 355030" predicate took 498 seconds. The time spent is similar to the initial query.

                Thanks!
                Waldecir

                Edited by: Waldecir P. Junior on 03/05/2012 07:28
                • 5. Re: SDO_ANYINTERACT (Performance issue)
                  ToM2
                  Hi,
                  Your local spatial index is not selective, You should use different partitioning key. Look at row distribution - it's normal?
                  You should use for partitioning regular grid(for ex. six rows and six columns.), assign object to grid cell and put cell_id into partitioning column.
                  Row distribution in partitions should be normal - it means in partitions row_count should return similar value.

                  Could You show row from user_sdo_geom_metadata(DIMINFO and SRID) for PROSPECT table?

                  Regards,
                  ToM
                  • 6. Re: SDO_ANYINTERACT (Performance issue)
                    John O'Toole
                    Hi,

                    From the explain plan it looks like the optimizer used partition pruning to eliminate the other partitions, so it just focused on partition 4.
                    However, as ToM pointed out you have over 60% of your data in that partition. If you are partitioning for performance reasons, then it is better to try to distribute the data more evenly between the partitions. If you are partitioning for maintenance reasons, then maybe that distribution is ok for you.
                    Either way, 8m points in a partition isn't big - I've worked on tables with about 20m rows of points per partitions and sdo_anyinteract queries return in a second. However I always work with projected data, whereas your data is geographic, which is far slower in Oracle due to the more complicated math - that's probably the main issue.

                    Also, you have an awful lot of precision there - is that really needed. What is your DIMINFO tolerance in user_sdo_geom_metadata? Try to keep this to a realistic figure.

                    Out of interest, how long does it take for the spatial index to build?

                    I'd suggest having a look at a few articles on Simon Greener's blog:
                    http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/285/effects-of-sdo_geometry-ordinate-precision-on-performance
                    http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/138/spatial-sorting-of-data-via-morton-key
                    Both of these topics should help you. It is always work trying to cluster your data - it can make a massive difference to spatial query performance.

                    John
                    • 7. Re: SDO_ANYINTERACT (Performance issue)
                      Waldecir P. Junior
                      Hi friends,

                      I'm using partitioning for maintenance reasons. Like John, I also think that 8m points is not big.
                      The index creation takes about an hour.

                      My USER_SDO_GEOM_METADATA information is the following:
                      SELECT * FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'PROSPECT';
                      TABLE_NAME   COLUMN_NAME  DIMINFO                                                                                                    SRID                   
                      ------------ ------------ ---------------------------------------------------------------------------------------------------------- -----
                      PROSPECT     GEOM         MDSYS.SDO_DIM_ARRAY('MDSYS.SDO_DIM_ELEMENT('X',-180,180,0.05)','MDSYS.SDO_DIM_ELEMENT('Y',-90,90,0.05)')   8307 
                      Do you think that this tolerance is small?

                      Tanks!
                      Waldecir
                      • 8. Re: SDO_ANYINTERACT (Performance issue)
                        John O'Toole
                        That tolerance seems ok. I had seen the number of decimal places in the original SDO_ANYINTERACT and thought maybe you had a crazy small tolerance.

                        To test how much of your problem is projected vs. geographic, I'd suggest trying the following:
                        1. Drop the spatial index
                        2. Run:
                        update prospect a set a.geom.sdo_srid=null;
                        3. Run:
                        update user_sdo_geom_metadata set srid=null where table_name = 'PROSPECT';
                        4. Rebuild the spatial index
                        5. Run the same query

                        Of course the results won't be accurate as it'll interpret the data as projected now, but it'll give you a benchmark as to how long it takes to run the query with the geographic data issue out of the way.

                        John
                        • 9. Re: SDO_ANYINTERACT (Performance issue)
                          Waldecir P. Junior
                          Hi John,

                          I performed the tests you suggested and the time is similar to the original configuration.

                          Do you have another suggestion?

                          Thanks!
                          Waldecir
                          • 10. Re: SDO_ANYINTERACT (Performance issue)
                            John O'Toole
                            Hi Waldecir,

                            I'm not too sure what's going on for you there. I had thought that the geographic SRID was the bottleneck, but your testing with null SRID suggests it is not.
                            As a test I ran the statements below on my Windows laptop 11.2.0.3 database to create 5 million points in a table. Then I ran an SDO_ANYINTERACT query - it returns in just over a second. Can you try something similar and see how long this takes?

                            John
                            jot_test@JOHNOT> create sequence test_points_seq;
                            
                            Sequence created.
                            
                            jot_test@JOHNOT>
                            jot_test@JOHNOT> drop table test_points purge;
                            
                            Table dropped.
                            
                            jot_test@JOHNOT>
                            jot_test@JOHNOT> create table test_points (
                              2    id                               integer primary key,
                              3    geometry mdsys.sdo_geometry);
                            
                            Table created.
                            
                            jot_test@JOHNOT>
                            jot_test@JOHNOT> begin
                              2     for i in 1..5 loop
                              3     insert into test_points (id, geometry) (
                              4             select
                              5                     test_points_seq.nextval,
                              6                     sdo_geometry(2001, 2157, sdo_point_type(round(dbms_random.value(400000,750000),3),
                             round(dbms_random.value(500000,1000000),3), null), null, null)
                              7             from dual
                              8             connect by level <= 1000000);
                              9     end loop;
                             10  commit;
                             11  end;
                             12  /
                            
                            PL/SQL procedure successfully completed.
                            
                            jot_test@JOHNOT>
                            jot_test@JOHNOT> select count(*) as total from test_points;
                            
                                 TOTAL
                            ----------
                               5000000
                            
                            1 row selected.
                            
                            jot_test@JOHNOT>
                            jot_test@JOHNOT> delete from user_sdo_geom_metadata where table_name = 'TEST_POINTS';
                            
                            1 row deleted.
                            
                            jot_test@JOHNOT> insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values (
                              2  'TEST_POINTS','GEOMETRY', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 400000, 750000, .0005), 
                            SDO_DIM_ELEMENT('Y', 500000, 1000000, .0005)), 2157);
                            
                            1 row created.
                            
                            jot_test@JOHNOT> commit;
                            
                            Commit complete.
                            
                            jot_test@JOHNOT> create index test_points_geometry on test_points(geometry)
                              2  indextype is mdsys.spatial_index parameters('layer_gtype=point');
                            
                            Index created.
                            
                            jot_test@JOHNOT> set timing on
                            jot_test@JOHNOT> select count(*) as sdo_anyinteract_count
                              2  from test_points
                              3  where sdo_anyinteract(
                              4             geometry, mdsys.sdo_geometry(2003, 2157, null,
                              5             mdsys.sdo_elem_info_array(1,1003,3),
                              6      mdsys.sdo_ordinate_array(500000, 600000, 650000, 700000))) = 'TRUE';
                            
                            SDO_ANYINTERACT_COUNT
                            ---------------------
                                           429167
                            
                            1 row selected.
                            
                            Elapsed: 00:00:01.12
                            • 11. Re: SDO_ANYINTERACT (Performance issue)
                              Waldecir P. Junior
                              Hi John,

                              Based on your previous post I have prepared a benchmark and found a strange behavior of SDO_ANYINTERACT with geographic data.

                              I ran your commands that create the table TEST_POINTS and insert geometries with SRID 2157 (projected). I also created a table with SRID 8307 (geographic). See the commands:
                              SQL> create sequence test_points_8307_seq;
                               
                              Sequence created.
                               
                              SQL>
                              SQL> drop table test_points_8307 purge;
                               
                              Table dropped.
                               
                              SQL>
                              SQL> create table test_points_8307 (
                                2    id                               integer primary key,
                                3    geometry mdsys.sdo_geometry);
                               
                              Table created.
                               
                              SQL>
                              SQL> begin
                                2     for i in 1..5 loop
                                3     insert into test_points_8307 (id, geometry) (
                                4             select
                                5                     test_points_8307_seq.nextval,
                                6                     sdo_geometry(2001, 8307, sdo_point_type(ROUND(dbms_random.value(-180,180),5), ROUND(dbms_random.value(-90,90),5), NULL), NULL, NULL)
                                7             from dual
                                8             connect by level <= 1000000);
                                9     end loop;
                               10  commit;
                               11  end;
                               12  / 
                               
                              PL/SQL procedure successfully completed.
                               
                              SQL>
                              SQL> select count(*) as total from test_points_8307;
                               
                                   TOTAL
                              ----------
                                 5000000
                               
                              1 row selected.
                               
                              SQL>
                              SQL> delete from user_sdo_geom_metadata where table_name = 'TEST_POINTS_8307';
                               
                              1 row deleted.
                               
                              SQL> insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values (
                                2  'TEST_POINTS_8307','GEOMETRY', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, .05), 
                              SDO_DIM_ELEMENT('Y', -90, 90, .05)), 8307);
                               
                              1 row created.
                               
                              SQL> commit;
                               
                              Commit complete.
                               
                              SQL> create index test_points_8307_geometry on test_points(geometry)
                                2  indextype is mdsys.spatial_index parameters('layer_gtype=point');
                               
                              Index created.
                              After creating the two tables I ran the following queries and scored each time. Note that I set the window to keep a similar number of points in each test.

                              TEST 1:
                              SQL>set timing on;
                              SQL>
                              SQL>SELECT count(*) as sdo_anyinteract_count
                                2  FROM test_points
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 2157, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(500000, 600000, 650000, 700000))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                             428243
                              
                              Elapsed: 00:00:01.81
                              
                              
                              SQL>SELECT count(*) AS sdo_anyinteract_count
                                2  FROM test_points_8307
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 8307, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(-35, -34, 40, 40))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                             427372
                              
                              Elapsed: 00:00:01.97
                              TEST 2:
                              SQL>SELECT count(*) as sdo_anyinteract_count
                                2  FROM test_points
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 2157, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(400000, 600000, 750000, 700000))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                            1000496
                              
                              Elapsed: 00:00:04.05
                              
                              
                              SQL>SELECT count(*) AS sdo_anyinteract_count
                                2  FROM test_points_8307
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 8307, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(-54, -53.8, 60, 60))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                            1000632
                              
                              Elapsed: 00:00:04.08
                              TEST 3:
                              SQL>SELECT count(*) as sdo_anyinteract_count
                                2  FROM test_points
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 2157, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(400000, 500000, 700000, 750000))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                            2140831
                              
                              Elapsed: 00:00:10.68
                              
                              
                              SQL>SELECT count(*) AS sdo_anyinteract_count
                                2  FROM test_points_8307
                                3  WHERE sdo_anyinteract(
                                4  geometry, mdsys.sdo_geometry(2003, 8307, null,
                                5  mdsys.sdo_elem_info_array(1,1003,3),
                                6  mdsys.sdo_ordinate_array(-150, -45, 151, 47))) = 'TRUE';
                              
                              SDO_ANYINTERACT_COUNT
                              ---------------------
                                            2136991
                              
                              Elapsed: 00:01:59.42
                              On test 1, where approximately 430,000 points were considered in the window, both queries were fast.
                              On test 2, where approximately 1,000,000 points were considered in the window, both queries were fast.
                              However, on test 3, where approximately 2,000,000 points were considered in the window, the query with SRID 2157 was fast and the query with SRID 8307 was very slow.

                              I think this behavior is abnormal. What do you think?
                              This behavior persisted in all queries considering many points (> 2,000,000).

                              Could you reproduce this issue?

                              Thanks,
                              Waldecir
                              • 12. Re: SDO_ANYINTERACT (Performance issue)
                                750496
                                I ran that test on my 11.2.0.3 Windows 7 laptop. I am not getting the same type of large time difference. Note that before running these queries I flushed the shared pool and buffer cache.

                                John
                                jot_test@JOHNOT> SELECT count(*) as sdo_anyinteract_count
                                  2  FROM test_points
                                  3  WHERE sdo_anyinteract(
                                  4  geometry, mdsys.sdo_geometry(2003, 2157, null,
                                  5  mdsys.sdo_elem_info_array(1,1003,3),
                                  6  mdsys.sdo_ordinate_array(400000, 500000, 700000, 750000))) = 'TRUE';
                                
                                SDO_ANYINTERACT_COUNT
                                ---------------------
                                              2145007
                                
                                1 row selected.
                                
                                Elapsed: 00:01:08.52
                                jot_test@JOHNOT>
                                jot_test@JOHNOT> SELECT count(*) AS sdo_anyinteract_count
                                  2  FROM test_points_8307
                                  3  WHERE sdo_anyinteract(
                                  4  geometry, mdsys.sdo_geometry(2003, 8307, null,
                                  5  mdsys.sdo_elem_info_array(1,1003,3),
                                  6  mdsys.sdo_ordinate_array(-150, -45, 151, 47))) = 'TRUE';
                                
                                SDO_ANYINTERACT_COUNT
                                ---------------------
                                              2134905
                                
                                1 row selected.
                                
                                Elapsed: 00:02:02.08
                                • 13. Re: SDO_ANYINTERACT (Performance issue)
                                  dgeringe
                                  When returning this many rows (millions), if possible, consider SDO_FILTER instead of SDO_ANYINTERACT.

                                  If SDO_FILTER doesn't satisfy your requirement (a primary filter is not good enough for your requirement) then you should use SDO_ANYINTERACT.

                                  The issue you have run into is an SDO_ANYINTERACT performance optimization is not kicking in because the query window is a multipolygon.

                                  I know it doesn't look like a multipolygon, but for a "geodetic optimized rectangle", if it covers more than 1/2 the Earth's surface area, internally, we break it up into a multipolygon where each of it's elements are less than 1/2 the Earth's surface area. We also densify at one degree intervals along the latitude lines.

                                  This behavior is ONLY for "geodetic optimized rectangles".

                                  We're looking into a generic fix for the sdo_anyinteract performance you ran into when the geodetic optimize rectangle is larger than half the Earth's surface area.

                                  In the mean time, here is a workaround. Please give it a try, and let me know how it works out.

                                  --
                                  -- This function takes a geodetic optimize rectangle as input, and returns
                                  -- a table of geometries, one for each element implicitly created when
                                  -- the rectangle covers more than half the Earth's surface area.
                                  --
                                  CREATE OR REPLACE FUNCTION get_geodetic_rectangle_windows (rectangle SDO_GEOMETRY)
                                  RETURN sdo_regionset DETERMINISTIC IS
                                  return_geoms SDO_REGIONSET := sdo_regionset();
                                  num_elems NUMBER := sdo_util.getnumelem(rectangle);
                                  BEGIN
                                  return_geoms.extend(num_elems);

                                  FOR r IN 1 .. num_elems LOOP
                                  return_geoms(r) := sdo_region (r, sdo_util.extract (rectangle, r));
                                  END LOOP;

                                  RETURN return_geoms;
                                  END;
                                  /


                                  --
                                  -- This query should run in a few seconds instead of 2 minutes
                                  --
                                  SELECT count(*) AS sdo_anyinteract_count
                                  FROM TABLE (get_geodetic_rectangle_windows (
                                  sdo_geometry(2003, 8307, null,
                                  sdo_elem_info_array(1,1003,3),
                                  sdo_ordinate_array(-150, -45, 151, 47)))) a,
                                  test_points_8307 b
                                  WHERE sdo_anyinteract(b.geometry, a.geometry) = 'TRUE';
                                  • 14. Re: SDO_ANYINTERACT (Performance issue)
                                    John O'Toole
                                    Using this workaround I get the result in 4 seconds on a cold buffer cache - that's certainly a performance uplift.
                                    jot_test@JOHNOT> SELECT count(*) AS sdo_anyinteract_count
                                      2  FROM TABLE (get_geodetic_rectangle_windows (
                                      3  sdo_geometry(2003, 8307, null,
                                      4  sdo_elem_info_array(1,1003,3),
                                      5  sdo_ordinate_array(-150, -45, 151, 47)))) a,
                                      6  test_points_8307 b
                                      7  WHERE sdo_anyinteract(b.geometry, a.geometry) = 'TRUE';
                                    
                                    SDO_ANYINTERACT_COUNT
                                    ---------------------
                                                  2134906
                                    
                                    1 row selected.
                                    
                                    Elapsed: 00:00:04.03
                                    1 2 Previous Next