Re: Oracle Text index question, somewhat urgent

From: Bill Pass <wbpass_at_yahoo.com>
Date: Thu, 13 Jun 2002 02:28:29 -0800
Message-ID: <F001.0047CCE6.20020613022829@fatcity.com>

This did work for us and is documented in the following document:

Oracle Text Application Developers Guide

Case-Sensitive Indexing and Querying
By default, all text tokens are converted to uppercase and then indexed. This results
in case-insensitive queries. For example, separate queries on each of the three words
cat, CAT, and Cat all return the same documents. You can change the default and have the index record tokens as they appear in the
text. When you create a case-sensitive index, you must specify your queries with
exact case to match documents. For example, if a document contains Cat, you must
specify your query as Cat to match this document. Specifying cat or CAT does not
return the document.
To enable or disable case-sensitive indexing, use the mixed_case attribute of the
BASIC_LEXER preference.
See Also: Oracle Text Reference to learn more about the BASIC_LEXER.

One other note on performance of text indexes. Keep the table that you use as the basis for your indexes as small as possible for best query time performance (stay away from inline storage of data).

For faster index creation, the exact opposite is true. Inline storage of data is faster when updating your index, but comes at the cost of slower queries.

The reason is that for each term in the CONTAINS clause oracle retrieves all rows in the table corresponding to the matched term and then does a concatenation of the retrieved rows. With really wide tables containing chained rows, this retrieval can consume the majority of your time. We ended up moving the text into other tables and using a
DETAIL_DATASTORE to get the best overall performance for both.

Bill

• Jonathan Gennick <listmail_at_gennick.com> wrote:
  > I need to ask a question of someone who understands
  > Oracle Text
  > indexes. So if you know Oracle Text, please read on.
  >
  > I was told that if I had a query such as the
  > following:
  >
  > SELECT falls_name
  > FROM waterfalls
  > WHERE INSTR(UPPER(falls_directions),
  > 'MACKINAC BRIDGE') <> 0;
  >
  > where falls_directions is a CLOB, that I can define
  > a case-insensitive
  > index using Oracle Text and then use CONTAINS to
  > execute the query
  > more efficiently:
  >
  > SELECT falls_name
  > FROM waterfalls
  > WHERE
  > CONTAINS(falls_directions,'mackinac bridge') > 0;
  >
  > Is this correct? Is there such a thing as a
  > case-insensitive index in
  > Oracle Text? I did take a quick look at the Oracle
  > Text manual, but
  > this capability didn't leap out at me. Is
  > "case-insensitive" the term
  > Oracle Text uses? In any case, I need to verify
  > whether what I've been
  > told is correct.
  >
  > Jonathan Gennick --- Brighten the corner where you
  > are
  > mailto:jonathan_at_gennick.com * 906.387.1698
  > http://Gennick.com * http://MichiganWaterfalls.com *
  > http://ValleySpur.com
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Jonathan Gennick
  > INET: listmail_at_gennick.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  

  ---

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Pass
  INET: wbpass_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Jun 13 2002 - 05:28:29 CDT