Home » RDBMS Server » Performance Tuning » Compare spatial and attributive query (Oracle Locator 11g)
Compare spatial and attributive query [message #653436] Tue, 05 July 2016 09:37 Go to next message
moehre
Messages: 43
Registered: June 2016
Member
Hi,

I will compare two different kinds of queries regarding the running time.
In my case I have a tile (2*2km) and I will get all the data out of my table which are inside my tile.

1 spatial query:
SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ 
  FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d
  WHERE  SDO_INSIDE (a.CENTROID_GEOM, SDO_GEOMETRY(2003, 31467, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),SDO_ORDINATE_ARRAY(3499367.35, 5393324.36, 3501367.35, 5395324.36))) = 'TRUE'
  AND a.id = b.BUILDING_ID
  AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
  AND c.GEOMETRY IS NOT NULL
  AND b.OBJECTCLASS_ID = d.ID;

For this query the running time is 0,016 seconds

2 attributive query:
SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ 
  FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d
  WHERE  a.grid_id_2000 = 170
  AND a.id = b.BUILDING_ID
  AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
  AND c.GEOMETRY IS NOT NULL
  AND b.OBJECTCLASS_ID = d.ID;

For this the running time is 0,094 seconds

I was really wondering about that because I have learned that attributive queries are always faster instead of spatial queries.

Just an idea does it depend on the Index?
I created a spatial index for a.CENTROID_GEOM and I created a normal index (nonunique) for a.grid_id_2000.
In a.grid_id there are only integer values.

So how is this possible?
Re: Compare spatial and attributive query [message #653449 is a reply to message #653436] Tue, 05 July 2016 16:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
To see what the execution plans are:

SET AUTOTRACE ON EXPLAIN

Then run both queries again and copy and paste the results, including the explained execution plans.

Theoretically, the optimizer is smart enough to figure the best execution plan and a query that is more restrictive and has an index on the filtering column should run faster. However, sometimes the optimizer isn't as smart as it should be and sometimes we haven't given it everything that it needs either. For example:

Are there indexes on the columns that the tables are joined on?

Are your statistics current?

Is the entry in your user_sdo_geom_metadata view that the spatial index uses appropriate for your data in the table?

Have you run both queries at least twice, so that they are both in the SGA before comparing times?

[Updated on: Tue, 05 July 2016 16:08]

Report message to a moderator

Re: Compare spatial and attributive query [message #653454 is a reply to message #653449] Wed, 06 July 2016 03:59 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok I did use Autotrace and here are the results:

After I restart SQLPLUS I get this result:
spatial query: https://drive.google.com/file/d/0B5AUL4-roeC4QUZlTjdfNi1SVkE/view?usp=sharing

spatial_query_result: https://drive.google.com/file/d/0B5AUL4-roeC4WTNnQnlxV2NsZHM/view?usp=sharing

attributive query: https://drive.google.com/file/d/0B5AUL4-roeC4QUZlTjdfNi1SVkE/view?usp=sharing

attributive query_result: https://drive.google.com/file/d/0B5AUL4-roeC4TGJtODJqbkx0aXM/view?usp=sharing

Regarding Indices:
I have created one spatial index for a.CENTROID_GEOM and a normal index for a.grid_id_2000 (only integer values)!

Furthermore I did it more than one times.
Just for me is it general that the first query takes normally more time than the second? Because of the SGA?

What do you think about the runtime? In the second queries my spatial query (SDO_INSIDE (a.CENTROID_GEOM, SDO_GEOMETRY(2003, 31467, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),SDO_ORDINATE_ARRAY(3499367.35, 5393324.36, 3501367.35, 5395324.36))) = 'TRUE') is nearly equal to my attributive query (WHERE a.grid_id_2000 = 170)
Where can I see the execution time? Do I have to summarize the time values in the table attributive query? The trace option isn´t supported in my case...


[mod-edit: links fixed by bb]

[Updated on: Wed, 06 July 2016 14:43] by Moderator

Report message to a moderator

Re: Compare spatial and attributive query [message #653478 is a reply to message #653454] Wed, 06 July 2016 16:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like you posted the explain plan for the "attributive query" twice and did not post the plan for the spatial query. Both are labeled attributive and look the same.

Based on some of your previous posts, I believe that you need to correct the entries in your user_sdo_geom_metadata view. Once you have corrected those, then you need to drop and recreate or alter and rebuild your spatial index on the centroid_geom column of the building table. I have posted code for inserting previously. I have posted code for updating those values below.

As I said before, you should have indexes on the columns that are used for joining.

As I said before, you need to make sure your statistics are current.

You can probably safely remove "and c.geometry is not null" from your quereis.

When you run a query for the first time, it has to be hard parsed, which takes some time. After that, the query should be stored in the SGA and can be re-used without hard parsing again, so it runs quicker after the first run.

To see the time, just SET TIMING ON.

In your explain plan, the only time that you want to see table access full is when it is accessing most of the rows from that table. Otherwise, you want to see usage of indexes. You cut off the headings in your explain plan, but in the operation column you can see what is done and in the name column you can see the names of the tables and indexes. At the bottom, ideally you want to see access, indicating the joins and conditions were met using indexes, not filter.

Please see the demonstration below, noting the comments above each section, and try to reproduce this on your system, without skipping any steps or doing them in a different order. Note that the second query is faster than the first. Since the quantity and variety of data will be different on your system, I cannot exactly reproduce what you have, but it should still be similar.

-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM building
  2  /

        ID
----------
CENTROID_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------------------------------------
GRID_ID_2000
------------
   1179809
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482971.4, 5376958.4, 521.9175), NULL, NULL)
         170

   1176154
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482967.85, 5376958.19, 521.988), NULL, NULL)
         170

   1179924
SDO_GEOMETRY(3001, 31467, SDO_POINT_TYPE(3482974.96, 5376958.6, 521.847), NULL, NULL)
         170


3 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM thematic_surface
  2  /

BUILDING_ID OBJECTCLASS_ID LOD2_MULTI_SURFACE_ID
----------- -------------- ---------------------
    1179809             35                    19
    1179809             35                    20
    1176154             35                    19
    1179924             35                    20

4 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM surface_geometry
  2  /

   ROOT_ID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------------------------------------
        19
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 53
76958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 53
76954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))

        20
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 53
76954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79,
5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 53
76953.32, 521.847, 3482973.73, 5376953.75, 521.847))


2 rows selected.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM objectclass
  2  /

        ID CLASSNAME
---------- --------------------
        35 BuidingGroundSurface

1 row selected.

-- correct your entries in user_sdo_geom_metadata:
SCOTT@orcl_12.1.0.2.0> UPDATE user_sdo_geom_metadata
  2  SET    diminfo =
  3  	    (SELECT MDSYS.SDO_DIM_ARRAY
  4  		      (MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05),
  5  		       MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05),
  6  		       MDSYS.SDO_DIM_ELEMENT('Z', minz, maxz, 0.05))
  7  	     FROM   (SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
  8  			    ROUND( MAX( v.x ) + 1,0) as maxx,
  9  			    TRUNC( MIN( v.y ) - 1,0) as miny,
 10  			    ROUND( MAX( v.y ) + 1,0) as maxy,
 11  			    ROUND( MIN( v.z ) - 1,0) as minz,
 12  			    ROUND( MAX( v.z ) + 1,0) as maxz
 13  		     FROM   (SELECT SDO_AGGR_MBR(a.centroid_geom) as mbr
 14  			     FROM   building a) b,
 15  			     TABLE(mdsys.sdo_util.getvertices(b.mbr)) v)),
 16  	    srid = 31467
 17  WHERE  table_name = 'BUILDING'
 18  AND    column_name = 'CENTROID_GEOM'
 19  /

1 row updated.

SCOTT@orcl_12.1.0.2.0> SELECT * FROM user_sdo_geom_metadata WHERE table_name = 'BUILDING' AND column_name = 'CENTROID_GEOM'
  2  /

TABLE_NAME
--------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
----------------------------------------------------------------------------------------------------------------------------------
      SRID
----------
BUILDING
CENTROID_GEOM
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 3482966, 3482976, .05), SDO_DIM_ELEMENT('Y', 5376957, 5376960, .05), SDO_DIM_ELEMENT('Z', 521,
523, .05))
     31467


1 row selected.

-- drop and recreate or alter and rebuild your spatial index after changing the values in the sdo_geom_metadata view:
SCOTT@orcl_12.1.0.2.0> DROP INDEX centroid_geom_spx
  2  /

Index dropped.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX centroid_geom_spx ON building (centroid_geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
  2  /

Index created.

-- index you already have:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX grid_id_2000_idx ON building (grid_id_2000)
  2  /

Index created.

-- create indexes on columns that are used for joining:
SCOTT@orcl_12.1.0.2.0> CREATE INDEX thematic_surface_idx ON thematic_surface (building_id, lod2_multi_surface_id, objectclass_id)
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX root_id_idx ON surface_geometry (root_id)
  2  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX id_idx ON objectclass (id)
  2  /

Index created.

-- gather statistics:
SCOTT@orcl_12.1.0.2.0> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'BUILDING')

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'THEMATIC_SURFACE')

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'SURFACE_GEOMETRY')

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'OBJECTCLASS')

PL/SQL procedure successfully completed.

-- test your queries again (run twice and use second results) (I skipped posting the first results unnecessarily):

-- spatial query:
SCOTT@orcl_12.1.0.2.0> SET TIMING ON
SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_12.1.0.2.0> SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ
  2  FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d
  3  WHERE  SDO_INSIDE
  4  	      (a.CENTROID_GEOM,
  5  	       SDO_GEOMETRY
  6  		 (2003, 31467, NULL, SDO_ELEM_INFO_ARRAY (1, 1003, 3),
  7  		  SDO_ORDINATE_ARRAY (3476109.091, 5372296.238, 3528350.521, 5419788.555))) = 'TRUE'
  8  AND    a.id = b.BUILDING_ID
  9  AND    b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
 10  AND    b.OBJECTCLASS_ID = d.ID
 11  /

BUILDING_NR
-----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------------------------------------
POLYGON_TYP
--------------------
    1176154
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 53
76958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 53
76954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))
BuidingGroundSurface

    1179809
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 53
76958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 53
76954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))
BuidingGroundSurface

    1179809
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 53
76954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79,
5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 53
76953.32, 521.847, 3482973.73, 5376953.75, 521.847))
BuidingGroundSurface

    1179924
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 53
76954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79,
5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 53
76953.32, 521.847, 3482973.73, 5376953.75, 521.847))
BuidingGroundSurface


4 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 4268045907

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                      |     1 |    90 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                      |     1 |    90 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                      |     1 |    78 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                       |                      |     1 |    54 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID       | BUILDING             |     1 |    42 |     1   (0)| 00:00:01 |
|*  5 |      DOMAIN INDEX (SEL: 0.100000 %)   | CENTROID_GEOM_SPX    |       |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | THEMATIC_SURFACE_IDX |     1 |    12 |     1   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| OBJECTCLASS          |     1 |    24 |     1   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                  | ID_IDX               |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID BATCHED | SURFACE_GEOMETRY     |     1 |    12 |     1   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN                   | ROOT_ID_IDX          |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("MDSYS"."SDO_INSIDE"("A"."CENTROID_GEOM","MDSYS"."SDO_GEOMETRY"(2003,31467,NULL,"SDO_ELE
              M_INFO_ARRAY"(1,1003,3),"SDO_ORDINATE_ARRAY"(3476109.091,5372296.238,3528350.521,5419788.555)))='TRUE'
              )
   6 - access("A"."ID"="B"."BUILDING_ID")
   8 - access("B"."OBJECTCLASS_ID"="D"."ID")
  10 - access("B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID")

-- "attributive query":
SCOTT@orcl_12.1.0.2.0> SELECT a.id AS building_nr, c.Geometry AS geometry, d.Classname AS polygon_typ
  2  FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d
  3  WHERE  a.grid_id_2000 = 170
  4  AND    a.id = b.BUILDING_ID
  5  AND    b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID
  6  AND    b.OBJECTCLASS_ID = d.ID
  7  /

BUILDING_NR
-----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------------------------------------
POLYGON_TYP
--------------------
    1179809
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 53
76958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 53
76954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))
BuidingGroundSurface

    1176154
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482967.35, 5376954.8, 521.988, 3482965.61, 53
76958.37, 521.988, 3482966.11, 5376958.61, 521.988, 3482963.9, 5376963.24, 521.988, 3482968.12, 5376965.28, 521.988, 3482973.5, 53
76954.25, 521.988, 3482970.35, 5376952.78, 521.988, 3482967.35, 5376954.8, 521.988))
BuidingGroundSurface

    1179809
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 53
76954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79,
5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 53
76953.32, 521.847, 3482973.73, 5376953.75, 521.847))
BuidingGroundSurface

    1179924
SDO_GEOMETRY(3003, 31467, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(3482973.73, 5376953.75, 521.847, 3482973.5, 53
76954.25, 521.847, 3482968.12, 5376965.28, 521.847, 3482973.01, 5376967.63, 521.847, 3482975.48, 5376962.49, 521.847, 3482976.79,
5376963.12, 521.847, 3482979.9, 5376956.67, 521.847, 3482977.21, 5376955.37, 521.847, 3482976.84, 5376954.15, 521.847, 3482975, 53
76953.32, 521.847, 3482973.73, 5376953.75, 521.847))
BuidingGroundSurface


4 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1087991563

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |     4 |   232 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                          |                      |     4 |   232 |     8   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN                 |                      |     6 |   276 |     8   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN                |                      |     2 |    72 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                  | OBJECTCLASS          |     1 |    24 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT                        |                      |     2 |    24 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                 | SURFACE_GEOMETRY     |     2 |    24 |     3   (0)| 00:00:01 |
|   7 |    BUFFER SORT                         |                      |     3 |    30 |     5   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| BUILDING             |     3 |    30 |     1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                  | GRID_ID_2000_IDX     |     3 |       |     0   (0)| 00:00:01 |
|* 10 |   INDEX RANGE SCAN                     | THEMATIC_SURFACE_IDX |     1 |    12 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("A"."GRID_ID_2000"=170)
  10 - access("A"."ID"="B"."BUILDING_ID" AND "B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID" AND
              "B"."OBJECTCLASS_ID"="D"."ID")

SCOTT@orcl_12.1.0.2.0> SET AUTOTRACE OFF
SCOTT@orcl_12.1.0.2.0> SET TIMING OFF

[Updated on: Wed, 06 July 2016 16:55]

Report message to a moderator

Re: Compare spatial and attributive query [message #653503 is a reply to message #653478] Fri, 08 July 2016 05:29 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
1 OK first I did correct my entries in user_sdo_geom_metadata as you explained!

2 Then I droped and recreated my spatial index after changing the values in the sdo_geom_metadata view.

3 I did create indexes on columns that are used for joining.

4. Last I did gather statistics.

Now I did run the query again with "SET TIMING ON" and "SET AUTOTRACE ON EXPLAIN".
The weird thing is my elapsed time is null???
But this cannot be.

My result:
spatial_query: https://drive.google.com/file/d/0B5AUL4-roeC4SzcxSzFMQ3FpMnc/view?usp=sharing

attributive_query: https://drive.google.com/file/d/0B5AUL4-roeC4ZExRbk5hVHN5QmM/view?usp=sharing

My final goal is to compare both queries (spatial and attributive) in time execution. Which time would you take for a comparison?


[mod-edit: links fixed by bb]

[Updated on: Fri, 08 July 2016 13:56] by Moderator

Report message to a moderator

Re: Compare spatial and attributive query [message #653507 is a reply to message #653503] Fri, 08 July 2016 14:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I ran what I ran from SQL*Plus. It looks like you ran it from some tool, which adds an extra layer of potential problems and obscures some things. It may be showing the time that it took to display the plan, instead of the time that the query that the plan was for took.

I can see from the filter conditions on both that you did not remove the line "and c.geometry is not null" from the query, which could slow it down significantly.

If all you want to do is compare times, then from SQL*Plus, not some tool, SET TIMING ON, run both queries twice and compare the elapsed time for the second runs. It helps if you can do this when there is not a lot of other stuff running that might affect the time or such things are at least consistent. If you get varrying results, then you may need to do multiple runs and average them. If you really want a detailed comparison, then you can use Tom Kyte's runstats: http://betteratoracle.com/posts/12-runstats utility. I can't seem to find the original on his site, but there are copies and variations all over the internet if you search.

It would also be a lot easier for everybody to read if you would just copy and paste your results into the post, as I have done, instead of providing url's to images that I have to fix to make them clickable links.


Re: Compare spatial and attributive query [message #653560 is a reply to message #653507] Mon, 11 July 2016 07:04 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
So for better understanding "SET TIMING ON" is the real time which he needs to do the query and to show the result. Am I right?

Ok I did run both queries on sql plus. Before that I used sql developer.
I get same results (time) for both queries. I thougt it would take more time with spatial query.
But for me in this case there is no difference between spatial and attributive query.

Another thing is if I remove the filter AND c.GEOMETRY IS NOT NULL
it takes more time!!!
Re: Compare spatial and attributive query [message #653568 is a reply to message #653560] Mon, 11 July 2016 12:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

So for better understanding "SET TIMING ON" is the real time which he needs to do the query and to show the result. Am I right?

Yes, that is right.

Quote:

Ok I did run both queries on sql plus. Before that I used sql developer.
I get same results (time) for both queries. I thougt it would take more time with spatial query.
But for me in this case there is no difference between spatial and attributive query.


Which is faster can depend on your data. If both criteria narrow it down to the same rows, the process should be similar using either index.

Quote:

Another thing is if I remove the filter AND c.GEOMETRY IS NOT NULL
it takes more time!!!


That is surprising. After removing that filter, did you run the new query twice and use the timing results from the second query? Remember, that when you change the query, it has to be hard parsed again. Do you get any different results with or without that filter? Are there any null values for c.geometry?
Re: Compare spatial and attributive query [message #653606 is a reply to message #653568] Tue, 12 July 2016 04:10 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Ok so here you can see my results:

1. attributive query (WHERE a.grid_id_2000 = 170):

Abgelaufen: 00:00:53.24

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1074465723

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

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

| Id  | Operation                      | Name             | Rows  | Bytes |TempS

pc| Cost (%CPU)| Time     |

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

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

|   0 | SELECT STATEMENT               |                  |  5170 |   646K|
  | 67588   (1)| 00:13:32 |

|*  1 |  HASH JOIN                     |                  |  5170 |   646K|
  | 67588   (1)| 00:13:32 |

|   2 |   TABLE ACCESS FULL            | OBJECTCLASS      |   106 |  2226 |
  |     3   (0)| 00:00:01 |

|*  3 |   HASH JOIN                    |                  |  5170 |   540K|  112

0K| 67585   (1)| 00:13:32 |

|*  4 |    HASH JOIN                   |                  | 30934 |   755K|
  |  1926   (2)| 00:00:24 |

|   5 |     TABLE ACCESS BY INDEX ROWID| BUILDING         |  3015 | 30150 |
  |   154   (0)| 00:00:02 |

|*  6 |      INDEX RANGE SCAN          | INDEX2           |  3015 |       |
  |     7   (0)| 00:00:01 |

|   7 |     TABLE ACCESS FULL          | THEMATIC_SURFACE |  1712K|    24M|
  |  1763   (2)| 00:00:22 |

|*  8 |    TABLE ACCESS FULL           | SURFACE_GEOMETRY |   284K|    22M|
  | 64337   (1)| 00:12:53 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."OBJECTCLASS_ID"="D"."ID")
   3 - access("B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID")
   4 - access("A"."ID"="B"."BUILDING_ID")
   6 - access("A"."GRID_ID_2000"=170)
   8 - filter(SYS_OP_NOEXPAND("C"."GEOMETRY") IS NOT NULL)

So I need around 53 seconds to execute the query and get the result!
If I take out the filter geometry is not null it takes around 1.19 min. I run the queries more than one times. Yes there are some null values in c.surface_geometry

2 The next query is the spatial query (WHERE SDO_INSIDE (a.CENTROID_GEOM, SDO_GEOMETRY(2003, 31467, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),SDO_ORDINATE_ARRAY(3499367.35, 5393324.36, 3501367.35, 5395324.36))) = 'TRUE'):
Abgelaufen: 00:00:49.48

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 2826424408

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

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

| Id  | Operation                          | Name              | Rows  | Bytes |

TempSpc| Cost (%CPU)| Time     |

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

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

|   0 | SELECT STATEMENT                   |                   |  3039 |   646K|

       | 67493   (1)| 00:13:30 |

|*  1 |  HASH JOIN                         |                   |  3039 |   646K|

       | 67493   (1)| 00:13:30 |

|   2 |   TABLE ACCESS FULL                | OBJECTCLASS       |   106 |  2226 |

       |     3   (0)| 00:00:01 |

|*  3 |   HASH JOIN                        |                   |  3039 |   584K|

  2256K| 67490   (1)| 00:13:30 |

|*  4 |    HASH JOIN                       |                   | 18182 |  2041K|

       |  1776   (2)| 00:00:22 |

|   5 |     TABLE ACCESS BY INDEX ROWID    | BUILDING          |  1772 |   173K|

       |     4   (0)| 00:00:01 |

|*  6 |      DOMAIN INDEX (SEL: 0.100000 %)| CENTROID_GEOM_SPX |       |       |

       |     4   (0)| 00:00:01 |

|   7 |     TABLE ACCESS FULL              | THEMATIC_SURFACE  |  1712K|    24M|

       |  1763   (2)| 00:00:22 |

|*  8 |    TABLE ACCESS FULL               | SURFACE_GEOMETRY  |   284K|    22M|

       | 64337   (1)| 00:12:53 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."OBJECTCLASS_ID"="D"."ID")
   3 - access("B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID")
   4 - access("A"."ID"="B"."BUILDING_ID")
   6 - access("MDSYS"."SDO_INSIDE"("A"."CENTROID_GEOM","MDSYS"."SDO_GEOMETRY"(20

03,31467,NULL,"SDO_ELEM_

              INFO_ARRAY"(1,1003,3),"SDO_ORDINATE_ARRAY"(3499367.35,5393324.36,3

501367.35,5395324.36)))='TRUE')

   8 - filter(SYS_OP_NOEXPAND("C"."GEOMETRY") IS NOT NULL)

For this query I need about 49 seconds.

The results for both queries are the same. I want to create a webservice and I need the fastest way! So do I have to use the spatial query?
I thought within the spatial query oracle has to execute a spatial function for each polygon. He has to compare each coordinate of the polygon instead on one value (attributive query). Is it more efficient to use a spatial query with good index instead of attributive with normal index on large datasets???

Furthermore I did test queries on a small amount of data. Instead a 2km tile I used a 500m tile. And there the executing time is nearly the same when using attributive query instead of spatial query. So does it depends on the amount of data. That if I have a huge amount the spatial index is faster than the normal index?

What is with the sum of the time in the explain plan? for the 500m tile and 2km tile the time (sum) of the explain plan is faster for the attributive query than for the spatial query

Hope you can help me to do the right decision!

[Updated on: Tue, 12 July 2016 05:27]

Report message to a moderator

Re: Compare spatial and attributive query [message #653628 is a reply to message #653606] Tue, 12 July 2016 14:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It is difficult to read the autotrace results when the lines are wrapped around. It helps if you set your linesize to 130 or however big it needs to be to prevent wrapping.

The two queries that you are comparing are very different. Usually, what you use depends on what data you want. I don't know if for every query, you can choose between selecting by grid_id_2000 or the spatial query to get the same results.

There are also other things besides data and indexes that can affect speed. How many rows does such a query usually return? Are you then paging through these results? Is it more important to return the first page faster than it is to return the entire result set quickly? If so, then you may want to use the first_rows hint. For example, you could begin your query with the following to tell the optimizer to adjust the plan to give priority to returning the first 20 rows quickly:

SELECT /*+ FIRST_ROWS(10) */

If I were you or a user, in general, I would not be happy with a query that takes almost a minute to return results, but if I could read the first page quickly, that would be sufficient. There is kind of a general industry standard of 3 seconds. I like to have things under 1 second. However, there are things that are complex enough that we have to wait. Anything that you can use to make the result set smaller will generally make things faster. It might help to have an understanding of what information your users would be trying to obtain for what purpose. Sometimes, differences in design or rewriting queries are more beneficial than just trying to make an existing query faster.

It would also help to know if this is static data or if there are frequent changes. If it is static, then a data warehouse environment can be more efficient.

The times in the plan output are not as accurate as what you get from using SET TIMING ON. The benefit to the times in the plan output are that you can see where the most time is spent. For example, the almost 13 seconds for each query on the full table scan of surface_geometry, is presumably due to using IS NOT NULL.

Some things scale well and some don't and not everything can be predicted exactly. Tuning is ongoing.

There are also other factors that affect speed, including hardware, network traffic, memory, processing speed, and so forth and there are some settings that affect how some things are used. This sub-forum is primarily for spatial issues. We have a separate sub-forum for performance tuning. You may wish to post there with a link to this thread for reference.


Re: Compare spatial and attributive query [message #653648 is a reply to message #653628] Wed, 13 July 2016 03:45 Go to previous messageGo to next message
moehre
Messages: 43
Registered: June 2016
Member
Just to know some background infromations:
I have a database with buildings which are scale down to seperate polygons. Next I calculated a centroid point for each building. Now I will create a webserver and I need a sql-statement to execute the query. This query need to be very fast.
I am doing this in the examples below for a 2km tile. At the end I will take something around 500m tile so that the results are not so large and because of that the execution time is much faster.
My idea at the beginning was to do a select statement by defining a bounding box and execute a spatial query to find the centroids of the buildings inside this bounding box. In comparison to that another idea was to add a Grid (2km), assign each building a integer value with the regarding grid id. So that I can execute a query to get all buildings from the specified grid_id(in my example grid_id == BBox). So and my opinion was that the second query will be much faster because I have no geometrc function inside!!!

Quote:
It is difficult to read the autotrace results when the lines are wrapped around. It helps if you set your linesize to 130 or however big it needs to be to prevent wrapping

Ok I did it!

1 attributive query:
31134 Zeilen ausgewõhlt.

Abgelaufen: 00:00:57.21

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 2727586714

--------------------------------------------------------------------------------
---------------------------
| Id | Operation | Name | Rows | Bytes |TempS
pc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
---------------------------
| 0 | SELECT STATEMENT | | 5170 | 646K|
| 67588 (1)| 00:13:32 |
|* 1 | HASH JOIN | | 5170 | 646K|
| 67588 (1)| 00:13:32 |
| 2 | TABLE ACCESS FULL | OBJECTCLASS | 106 | 2226 |
| 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 5170 | 540K| 112
0K| 67585 (1)| 00:13:32 |
|* 4 | HASH JOIN | | 30934 | 755K|
| 1926 (2)| 00:00:24 |
| 5 | TABLE ACCESS BY INDEX ROWID| BUILDING | 3015 | 30150 |
| 154 (0)| 00:00:02 |
|* 6 | INDEX RANGE SCAN | GRID_ID_2000_IDX | 3015 | |
| 7 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | THEMATIC_SURFACE | 1712K| 24M|
| 1763 (2)| 00:00:22 |
|* 8 | TABLE ACCESS FULL | SURFACE_GEOMETRY | 284K| 22M|
| 64337 (1)| 00:12:53 |
--------------------------------------------------------------------------------
---------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("B"."OBJECTCLASS_ID"="D"."ID")
3 - access("B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID")
4 - access("A"."ID"="B"."BUILDING_ID")
6 - access("A"."GRID_ID_2000"=170)
8 - filter(SYS_OP_NOEXPAND("C"."GEOMETRY") IS NOT NULL)


2. spatial query:
31124 Zeilen ausgewõhlt.

Abgelaufen: 00:00:56.02

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 2414190016

--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Bytes |
TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT | | 5567 | 1185K|
| 67617 (1)| 00:13:32 |
|* 1 | HASH JOIN | | 5567 | 1185K|
| 67617 (1)| 00:13:32 |
| 2 | TABLE ACCESS FULL | OBJECTCLASS | 106 | 2226 |
| 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 5567 | 1070K|
4136K| 67614 (1)| 00:13:32 |
|* 4 | HASH JOIN | | 33309 | 3740K|
| 1809 (2)| 00:00:22 |
| 5 | TABLE ACCESS BY INDEX ROWID | BUILDING | 3247 | 317K|
| 37 (0)| 00:00:01 |
|* 6 | DOMAIN INDEX (SEL: 1.831968 %)| CENTROID_GEOM_SPX | | |
| 37 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | THEMATIC_SURFACE | 1712K| 24M|
| 1763 (2)| 00:00:22 |
|* 8 | TABLE ACCESS FULL | SURFACE_GEOMETRY | 284K| 22M|
| 64337 (1)| 00:12:53 |
--------------------------------------------------------------------------------
--------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("B"."OBJECTCLASS_ID"="D"."ID")
3 - access("B"."LOD2_MULTI_SURFACE_ID"="C"."ROOT_ID")
4 - access("A"."ID"="B"."BUILDING_ID")
6 - access("MDSYS"."SDO_INSIDE"("A"."CENTROID_GEOM","MDSYS"."SDO_GEOMETRY"(20
03,31467,NULL,"SDO_ELEM_
INFO_ARRAY"(1,1003,3),"SDO_ORDINATE_ARRAY"(3499367.35,5393324.36,3
501367.35,5395324.36)))='TRUE')
8 - filter(SYS_OP_NOEXPAND("C"."GEOMETRY") IS NOT NULL)


Time: spatial query: 56:02s | attributive query: 57:21s

Quote:
The two queries that you are comparing are very different. Usually, what you use depends on what data you want. I don't know if for every query, you can choose between selecting by grid_id_2000 or the spatial query to get the same results.

The results in both queries are the same. I only want to have the method with the quickest response! So my criteria is the time!!! At the end I have to choose one for my webserver to get the polygons out ofthe database.


Quote:
It would also help to know if this is static data or if there are frequent changes. If it is static, then a data warehouse environment can be more efficient.

So for my project the data is static. It can be later on in the future that some buildings will be added to the database. But for me it is not relevant

Re: Compare spatial and attributive query [message #653649 is a reply to message #653648] Wed, 13 July 2016 03:55 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since this seems to have become more about tuning than spatial, I will move this thread to the tuning forum.
Previous Topic: Whast is connect$_by$_pump$ in explain plan
Next Topic: consecutive executions
Goto Forum:
  


Current Time: Thu Mar 28 17:28:23 CDT 2024