A text index creates several objects - token tables, separate indexes, etc.,
that are used to support the domain index. For a table this large, you will
need to manipulate the storage options for a text index to ensure you are
not choking performance on particular table space.
If you are indexing plain text in a size limited field, this is the ideal
case with respect to performance. The internal filter in Oracle is slow and
fat, which is why I usually write my own filter in C when I need to process
documents like HTML or even PDFs. However, for plain text, there really is
no filtering, which is why it gives the best performance. If you need to
accept special tokens or text combinations that are normally filtered, you
will need to manipulate lexer options as well. It is very important that
you read the documentation carefully.
The text engine is feature rich and reasonably robust, and it allows you to
do things you cannot do with a like query. For example, it is always case
insensitive, it can search on stubs, etc. *HOWEVER* if the number of
keywords accepted by a user for a search is small (3 or 4 at most), you
should time how long a dynamic LIKE query takes. If the LIKE query returns
quickly enough, I wouldn't bother with a context/text index.
One of the drawbacks of domain indexes is performance. I have had to use
hints and other tricks much more with domain indexes - spatial and text,
than I have had to with other types of queries. Domain indexes are not
common, and the optimizer sometimes gets confused. This why I would
recommend testing LIKE queries first.
If the text is mixed case, you can use a function based index to ignore case
(8i or above), or you can use the case insensitive character set for sorting
and comparison (10g only). I would *not* use a text index just to get case
insensitivity.
Good Luck!
Chris
----------------------------------------------------
Chris Weiss, Blueoak Database Engineering, LLC
http://www.blueoakdb.com
PH: 517-381-9305
CELL: 517-974-5634
eFAX: 801-340-0740
-----Original Message-----
From: Sparacello
[mailto:oracledba-ezmlmshield-x58672217.[Email address protected]
Sent: Friday, November 12, 2004 4:27 AM
To: LazyDBA Discussion
Subject: CONTEXT
Hi Gurus... my boss asked to me to evaluate implementation of context..
there is a way.. or some rules to evaluate this without real test?
I work in a bank and I can't simply "test".. I have to move lots of people
to enable context so.. I need to know BEFORE if it can be helpful..
(tables with 3.000.000 records field to be indexed is a varcha2(2000) and
people via web have to serch keyword inside this field)
Gianluca
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Web Marketing: impara tutti i segreti del Posizionamento sui Motori di
Ricerca. Partecipa al Workshop Nazionale SEO Italia 2004 - clicca qui
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2800&d=20041112
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page