Re: Indexing a CLOB

From: <>
Date: Thu, 12 Feb 2009 13:42:38 -0600
Message-ID: <>

Actually Context / interMedia / Oracle Text (the name changes over releases) is perfect for indexing CLOBs - very fast. I used it with a past company on Oracle8i. The only problem then was that domain indexes couldn't be partitioned. With 9i and 10g I believe that restriction was removed, which makes Oracle Text indexes and the tables they're on partitionable. The search tools are rich and performance is great. I highly recommend using out-of-line CLOBs - you have more options for placing the CLOB segments and keeping your base table lean and fast wrt, say, full table scans.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9715 (wk) / 512.935.5929 (pager)

z b <>  

Sent by: 02/12/2009 11:46 AM
Please respond to

Re: Indexing a CLOB

What exactly is slow?

Context is used to search within lobs - probably not relevant here.

Try caching the lob.

alter table <table> modify lob (lob_name) (cache);

On Thu, Feb 12, 2009 at 11:40 AM, Ben Wittmeier <> wrote:
> We need to increase the size of a data field beyond its varchar2(4000)
> definition because 4000 characters isn't large enough. The field is
> currently not indexed. The developer has done some initial testing with
> CLOB's but found the performance with the CLOB seemed slow so he's
> wondering about an index on the field. Though he also said the slow
> performance may be a result of the way he's doing things with the CLOB
> (as this is new to him). Are there any options available to index the
> CLOB field to improve performance? Oracle mentions using a CONTEXT
> index....
> Does anyone have any thoughts on indexing CLOB fields?
> This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
> --


Received on Thu Feb 12 2009 - 13:42:38 CST

Original text of this message