OraFAQ Forum: Text & interMedia » Create Index for CLOB-datatype

Home » Server Options » Text & interMedia » Create Index for CLOB-datatype (Oracle Locator 11g)

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

Create Index for CLOB-datatype [message #655598]

Mon, 05 September 2016 07:31

moehre
Messages: 43
Registered: June 2016

Member

Hi,

I construct a json (geometry data) as a CLOB type inside my database.
Now I will create an index for this column.

I found this in the internet:

CREATE INDEX myclob_idx
ON surface_geometry (json)
INDEXTYPE IS ctxsys.context;

is this the right way?
I will reduce the time of accessing the CLOB data.

The access is realized through a Webserver (CLOB.read() method)

Report message to a moderator

Re: Create Index for CLOB-datatype [message #655601 is a reply to message #655598]

Mon, 05 September 2016 08:36

John Watson
Messages: 8978
Registered: January 2010
Location: Global Village

Senior Member

It isn't that simple. Context indexes need maintenance, you must research doing this with the CTX_DDL package. Also you need to adjust your queries to use appropriate predicates, such as the CONTAINS operator rather than LIKE or =.

Report message to a moderator

Re: Create Index for CLOB-datatype [message #655607 is a reply to message #655598]

Mon, 05 September 2016 16:05

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You may see a lot of things on the internet that are not necessarily the best way to do things.

If you could upgrade to Oracle 12c, then there are JSON methods that can be used to access the data.

On Oracle 11g, you are better off storing your data in a column of type mdsys.sdo_geometry and querying the
data using Oracle spatial methods.

The Oracle Text ctxsys.context index is intended for searches of unstructured text, such as documents full of
pages of paragraphs of writing.

I gather from your other posts, that you have started with Oracle Spatial data, then gone through a lot of
roundabout things to convert that to json and now are trying to create a text index on it.

As I have stated previously, you should store your spatial data in a column of type mdsys.sdo_geometry,
create appropriate entries in the user_sdo_geom_metadata view, create a spatial index on it, then use
Oracle spatial features to query it.

You keep trying to do things the hard way, mixing features that were not intended to work together.

To answer your question directly, you could create such a context index, but you would need to periodically synchronize, optimize, and perhaps rebuild or drop and recreate it. All it would allow you to do would be to search for things like whether or not the word "polygon" is contained anywhere in the data, using the Oracle Text CONTAINS query operator. What purpose would it serve?

Report message to a moderator

Previous Topic:	Fulltext search
Next Topic:	Search over multiple Columns and Tables possible?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 22 13:26:16 CDT 2025