Home » Server Options » Text & interMedia » Create Index for CLOB-datatype (Oracle Locator 11g)
Create Index for CLOB-datatype [message #655598] Mon, 05 September 2016 07:31 Go to next message
Messages: 43
Registered: June 2016

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)
Re: Create Index for CLOB-datatype [message #655601 is a reply to message #655598] Mon, 05 September 2016 08:36 Go to previous messageGo to next message
John Watson
Messages: 7265
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 =.
Re: Create Index for CLOB-datatype [message #655607 is a reply to message #655598] Mon, 05 September 2016 16:05 Go to previous message
Barbara Boehmer
Messages: 8805
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?
Previous Topic: Fulltext search
Next Topic: Search over multiple Columns and Tables possible?
Goto Forum:

Current Time: Sat Feb 24 09:17:39 CST 2018

Total time taken to generate the page: 0.01434 seconds