Oracle Text Search

[luciffer]

I'm trying to decide which index to use content or ctxcat, i'm new to Oracle Text, and from the documentation in the Application Developer's Guide, i'm having trouble deciding which index is the right one.

The concept is that the application this will be used on is to display a manual online. The manual is split into chapters and paragraphs. The chapters table has ID, NAME, CHAPTER_NUMBER columns; the paragraphs table contains an ID, NAME, TEXT, CHAPTER_NUMBER, PARAGRAPH_NUMBER.

The only fields that need to be indexed are the CHAPTER - NAME (VARCHAR2) and the PARAGRAPH - TEXT (CLOB).

Now i don't expect that any paragraph would exceed say 1MB. But the paragraphs could have inserts, updates, deletes fairly frequently.

So the auto synchornizing of the ctxcat looks appealing, but i'd like to see more information or examples on what type of data best suits each index type.

any help is appreciated.

[anandkl]

Hi,
Context index will have maintenance over head since it creates its own data dictionary for data management. You need to regularly rebuild the index. But this will provide lot of other features for you search condition that is not available in CTXCAT index.

CTXCAT index will use more space for index and also the features available right now for this is very limited.

If your application requirement is only search on text and if the text is too big and does not include any normal column in the "WHERE" condition then it would be more appropriate to use CONTEXT index.

And its the reverse in ctxcat index.

You can actually create both indexes on the same column since they are different index type and do your test.

If you feel on of them is giving better performance for your application please go for it.

You can also find valuable documentation in metalink on context and ctxcat index.


We have implemented CONTEXT index for our acution management tool to search for KEYWORDS.

In our application the search is more on the TEXT and there is not major update,delete or insert happening.

Every week we create new table with new indexes.

regards
anandkl

[luciffer]

this will be a keyword or keywords search on the CLOB field. For example a user would search for something like "travel authorization" and i would want to bring back the top 10 paragraphs matched.

I'm going to keep it to a pretty basic search and I'm using oracle 9i2

Further searching reveiled this in the oracle docs...

"The column you specify when you create a CTXCAT index must be of type CHAR or VARCHAR2. No other types are supported for CTXCAT."

Since i want to search a CLOB field i'm assuming i have to use content index.

[anandkl]

Hi,
This would be only KEYWORD search and you can display top 10 or any number for that matter.

This limit is only on index set i.e if your WHERE condition also contains a normal column, like u want to limit the result set by PRICE range.

regards
anandkl