Re: Oracle Spatial Question

From: Don Spencer <spencer_at_caris.com>
Date: Thu, 30 Mar 2000 16:02:27 GMT
Message-ID: <38E37D92.458A7E28@caris.com>

Patrick,

Here's an update on the problem you were having with your Oracle 8i Spatial query. The information
below applies to 8.1.6. The stuff in my last message applies to 8.1.5 only.

1. Oracle is now recommending fixed indexes (not hybrid) in almost all cases.

         i.e either set SDO_NUMTILES = 0 or don't specify the parameter.

         The cases where they are not recommending fixed indexes are very rare.

   2) In 8.1.6, you will NEVER run into the 30000 tile error.

         It is recommended to index your layers to a level where your
        typical query will be covered by around 200 tiles.

        This means you can look at your dense areas and tile to a very small tile
level
        and get great performance on those small areas, as well as very large

windows.

        The small tile resolution is not a problem in 8.1.6.

      3) It is VERY important to analyze the spatial index tables ie. the INDEX_NAME_F?$
         tables created by the spatial index.

         Here is some SQL you can run to create SQL to analyze the spatial index tables.

         You must run the SQL generated by the following statement.

            select 'ANALYZE table ' || sdo_index_table || ' compute statistics; '
            from user_sdo_index_metadata;

Don Spencer
www.spatialcomponents.com

Patrick Jones wrote:

> Howdy,
>
> I'm having trouble with my 1st attempt at Oracle Spatial. Below is the
> script I run. The select statement gives me the following error:
>
> select s.NAME from SMALL s
> *
> ERROR at line 1:
> ORA-29902: error in executing ODCIIndexStart() routine
> ORA-13211: failed to tessellate the window object
> ORA-13208: internal error while evaluating
> [TOO_MANY_TILES_FOR_WINDOW_OBJECT] operator
> ORA-13213: failed to generate spatial index for window object
> ORA-13213: failed to generate spatial index for window object
> ORA-06512: at "MDSYS.SDO_INDEX_METHOD", line 73
> ORA-06512: at line 1
>
> What am I doing wrong?
>
> Thanx,
>
> Pat Jones
> plj_at_mitre.org
>
> --------------------------
>
> drop table SMALL;
>
> create table SMALL (
> NAME VARCHAR2(255),
> PLACE MDSYS.SDO_GEOMETRY
> );
>
> insert into SMALL VALUES(
> 'TheName',
> MDSYS.SDO_GEOMETRY(1,NULL,MDSYS.SDO_POINT_TYPE(
> -86.54991944444444,30.46041111111111,NULL), NULL, NULL)
> );
>
> DELETE from sdo_geom_metadata where TABLE_NAME = 'SMALL';
>
> INSERT INTO sdo_geom_metadata VALUES
> ('SMALL',
> 'PLACE',
> MDSYS.SDO_DIM_ARRAY(
> MDSYS.SDO_DIM_ELEMENT('Long', -180, 180, .005),
> MDSYS.SDO_DIM_ELEMENT('Lat', -90, 90, .005)
> ));
>
> drop index PLACEIND FORCE;
> create index PLACEIND on SMALL(PLACE) indextype is MDSYS.SPATIAL_INDEX
> PARAMETERS(
> 'SDO_LEVEL = 12, SDO_NUMTILES = 1');
>
> select s.NAME from SMALL s
> where mdsys.sdo_filter(
> s.PLACE,
> mdsys.sdo_geometry(3,null,null,
> mdsys.sdo_elem_info_array(1,3,3),
> mdsys.sdo_ordinate_array(-120,25,-70,50)),
> 'querytype=window layer_gtype=POINT') = 'TRUE';
>
> commit;
Received on Thu Mar 30 2000 - 10:02:27 CST