OraFAQ Forum: Spatial » Syntax for Creating Local spatial index on Partioned table.

Home » Server Options » Spatial » Syntax for Creating Local spatial index on Partioned table. (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Syntax for Creating Local spatial index on Partioned table. [message #577222]

Wed, 13 February 2013 09:26

gentleman777us
Messages: 122
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

Report message to a moderator

Re: Syntax for Creating Local spatial index on Partioned table. [message #577228 is a reply to message #577222]

Wed, 13 February 2013 10:28

_jum
Messages: 577
Registered: February 2008

Senior Member

I'd build this LOCAL spatial INDEX:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: Syntax for Creating Local spatial index on Partioned table. [message #577235 is a reply to message #577228]

Wed, 13 February 2013 11:18

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

It still gives me invalid option with your syntax

Report message to a moderator

Re: Syntax for Creating Local spatial index on Partioned table. [message #577248 is a reply to message #577235]

Wed, 13 February 2013 13:15

Barbara Boehmer
Messages: 9105
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.

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 01:55:11 CDT 2025