Home » Server Options » Text & interMedia » CTXCAT / CONTEXT index
CTXCAT / CONTEXT index [message #292053] Mon, 07 January 2008 10:33 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Which index is better to search on text field
1) CTXCAT index (or)
2) CONTEXT index

I have a table of
NUM_ROWS = 3224629
BLOCKS = 140712
SAMPLE_SIZE = 3224629
with column NAME_VALUE as VARCHAR2 (1117 Byte).

Which index would be worth creating on this column NAME_VALUE.

BANNER
---------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE	9.2.0.8.0	Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production


Regards,
Naveen
Re: CTXCAT / CONTEXT index [message #292055 is a reply to message #292053] Mon, 07 January 2008 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which index performed bettered based upon YOUR benchmark tests?
Re: CTXCAT / CONTEXT index [message #292056 is a reply to message #292053] Mon, 07 January 2008 10:40 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

I have tested with CONTEXT index and search with “contains (NAME_VALUE,'AXA')” takes almost 12 seconds.

But business needs the search to be less than 5 seconds. I am a developer and don’t have access to create CTXCAT index for my testing.

Thought, before approaching DBA to test for CTXCAT, I would post the question here.

Regards,
Naveen
Re: CTXCAT / CONTEXT index [message #292078 is a reply to message #292053] Mon, 07 January 2008 13:29 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hi,
Referring to the following from the Oracle Text manual:
----------------------------------------------------------

Quote:


Oracle Text Application Developer's Guide
Release 9.0.1

Creating a CTXCAT Index
The CTXCAT indextype is well-suited for indexing small text fragments and related information. If created correctly,
this type of index can give better structured query performance over a CONTEXT index.


What "created correctly" imply ?

Regards,
Naveen

[Updated on: Mon, 07 January 2008 14:03] by Moderator

Report message to a moderator

Re: CTXCAT / CONTEXT index [message #292081 is a reply to message #292053] Mon, 07 January 2008 13:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Let us step back for a moment. Consider this simple observation: there are two different types of indexes CTXCAT and CONTEXT. If we are thinkers, then first thing we might ask is "?why are there two different kinds of text searching indexes?". Being around the block a few times as we all are, it is not surprising to find out that each has advantages and dis-advantages from the other. In the end we have to deal with this reality:

which one we use depends upon what we want to do and how much work we are willing to live with.

More specifically, some of the notable differences between the two index types are:

1) CONTEXT index is not transaction oriented but CTXCAT index is. Thus many changes to data indexed by a CONTEXT index will not be seen until the index is manually synched, but changes to data under a CTXCAT index are immediatly seen by the CTXCAT index (as one would expect).

2) CONTEXT index is good for document crawling if you will, eg. indexing of WORD, EXCEL, CLOB, XML, HTML documents etc.. CTXCAT index is for simple text strings, most notably varchar2(4000).

3) CONTEXT index only supports one column (the document column), CTXCAT index allows extra scalar columns to be added to the text index. Thus CTXCAT index can take advantage of other data not in the text item being indexed whereas CONTEXT index cannot.

4) although similar, there are differences to the query capabilities between CONTEXT indexes and CTXCAT indexes. In general CTXCAT is not as powerful. In practice this makes little difference as most people don't use the real advanced features of CONTEXT searching.

So... which index to use should be driven not only by your performance requirements, but also by:

1) your tolerance for stale retrieval
2) the kind of data you are indexing
3) your need to search on more than just the text item itself
4) the complexity of your query needs

Unless it is a document of some kind, my money is on CTXCAT.

But maybe someone with more experience than I with Oracle Text Option has a different opinion?

Kevin
Re: CTXCAT / CONTEXT index [message #292094 is a reply to message #292053] Mon, 07 January 2008 15:49 Go to previous message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Kevin ,
Thanks a ton...

Regards,
Naveen
Previous Topic: How to search the word in pdf/word document
Next Topic: [CONTAINS] No result whereas there are
Goto Forum:
  


Current Time: Thu Mar 28 07:52:05 CDT 2024