| 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)
|
|
|
|
| Re: Create Index for CLOB-datatype [message #655601 is a reply to message #655598] |
Mon, 05 September 2016 08:36  |
John Watson
Messages: 8960
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 |
 |
Barbara Boehmer
Messages: 9100
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
