Home » Server Options » Spatial » Syntax for Creating Local spatial index on Partioned table. (Oracle 11g)
Syntax for Creating Local spatial index on Partioned table. [message #577222] Wed, 13 February 2013 09:26 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Iam using the following syntax to create LOCAL spatial index on partitioned table.

CREATE INDEX EPLC.RESERV_P_GEOX ON EPLC.RESERVOIR
(GEOM)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
[PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=EPLC_IDX')]LOCAL
[(PARTITION P_NORTH
PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=EPLC_IDX')
, PARTITION P_SOUTH
PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=EPLC_IDX')]
)]

It gives following error:
sql error: ora-02158 invalid create index option
*cause: An option other than COMPRESS, NOCOMPRESS,PCTFREE,INITRANS,
MAXTRANS,STORAGE,TABLESPACE,PARALLEL, NOPARALLEL,RECOVERABLE,UNRECOVERABLE,
LOGGING,NOLOGGING,LOCAL OR GLOBAL was specified.

Action: Choose one of the valid create index option

Looks like it did not like LAYER_GTYPE=POINT.

Any help to fix it is greatly appreciated.

Thanks
Re: Syntax for Creating Local spatial index on Partioned table. [message #577228 is a reply to message #577222] Wed, 13 February 2013 10:28 Go to previous messageGo to next message
_jum
Messages: 488
Registered: February 2008
Senior Member
I'd build this LOCAL spatial INDEX:
CREATE INDEX EPLC.RESERV_P_GEOX ON EPLC.RESERVOIR (GEOM)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX
 PARAMETERS ('LAYER_GTYPE=POINT,TABLESPACE=EPLC_IDX')
 LOCAL;

The PARTITION RANGE/HASH has to be defined in the TABLE not in the LOCAL INDEX.
Re: Syntax for Creating Local spatial index on Partioned table. [message #577235 is a reply to message #577228] Wed, 13 February 2013 11:18 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
It still gives me invalid option with your syntax
Re: Syntax for Creating Local spatial index on Partioned table. [message #577248 is a reply to message #577235] Wed, 13 February 2013 13:15 Go to previous message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
The left bracket "[" and right bracket "]" are not part of the syntax and need to be removed. They are used in the documentation to indicate optional syntax. Please see the example below that uses a different schema and different tablespace and also assumes that you have a partitioned table and an appropriate entry in user_sdo_geom_metadata.

SCOTT@orcl_11gR2> CREATE INDEX scott.RESERV_P_GEOX ON scott.RESERVOIR
  2  (GEOM)
  3  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  4  PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=users') LOCAL
  5  (PARTITION P_NORTH
  6  PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=users')
  7  , PARTITION P_SOUTH
  8  PARAMETERS('LAYER_GTYPE=POINT,TABLESPACE=users')
  9  )
 10  /

Index created.

Previous Topic: Oracle Spatial Query help
Next Topic: How to compute Min Max lat and lon values from polygon
Goto Forum:
  


Current Time: Wed Jul 30 12:39:55 CDT 2014

Total time taken to generate the page: 0.14031 seconds