Home » Server Options » Text & interMedia » Oracle text - CONTAINS sql query returning wrong result (Oracle 10g)
Oracle text - CONTAINS sql query returning wrong result [message #534027] Fri, 02 December 2011 04:33 Go to next message
Messages: 4
Registered: December 2011
Location: India
Junior Member

I am using CONTAINS with STEM($) operator in it, in SQL query. Query output is not as expected. Need your help to find the root cause of the issue.

SQL Query

select itemnum, score(1), description from maximo.item
where (( contains(description,' $bearing ', 1) > 0 )) and itemnum = '1357851011'


---------- -------- ------------
1357851011 7 LINER, 7-3/4" ROUGH BORE,TYPE 3-HHE-2-1 MFR #1H-33510-P1,INGERSOLL RAND RECIP.COMPRESSOR SER #XHH-1430/31,SIZE 7-3/4 & 5-1/2 & 9 X 12"

Quey returns the record where word "BEARING" is not at all present in the description in the database.

Query is generated by an ERP which has multilingual support, however for the present insatnce we are using only ENGLISH. SQL query can't be change to exclude STEM($).

I tried to check the ORACLE TEXT preferences set for the index and for the following information. Hope this would help you conclude.

SQL Query
index name: "MAXIMO"."ITEM_NDX2"
index id: 4069
index type: context

base table: "MAXIMO"."ITEM"
primary key column:
text column: DESCRIPTION
text column type: VARCHAR2(254)
language column:
format column:
charset column:



section group: NULL_SECTION_GROUP


stemmer: ENGLISH
fuzzy_match: GENERIC

stop_word: Mr
stop_word: Mrs
stop_word: Ms
stop_word: a
stop_word: all
stop_word: almost
stop_word: also
stop_word: although
stop_word: an
stop_word: and
stop_word: any
stop_word: are
stop_word: as
stop_word: at
stop_word: be
stop_word: because
stop_word: been
stop_word: both
stop_word: but
stop_word: by
stop_word: can
stop_word: could
stop_word: d
stop_word: did
stop_word: do
stop_word: does
stop_word: either
stop_word: for
stop_word: from
stop_word: had
stop_word: has
stop_word: have
stop_word: having
stop_word: he
stop_word: her
stop_word: here
stop_word: hers
stop_word: him
stop_word: his
stop_word: how
stop_word: however
stop_word: i
stop_word: if
stop_word: in
stop_word: into
stop_word: is
stop_word: it
stop_word: its
stop_word: just
stop_word: ll
stop_word: me
stop_word: might
stop_word: my
stop_word: no
stop_word: non
stop_word: nor
stop_word: not
stop_word: of
stop_word: on
stop_word: one
stop_word: only
stop_word: onto
stop_word: or
stop_word: our
stop_word: ours
stop_word: s
stop_word: shall
stop_word: she
stop_word: should
stop_word: since
stop_word: so
stop_word: some
stop_word: still
stop_word: such
stop_word: t
stop_word: than
stop_word: that
stop_word: the
stop_word: their
stop_word: them
stop_word: then
stop_word: there
stop_word: therefore
stop_word: these
stop_word: they
stop_word: this
stop_word: those
stop_word: though
stop_word: through
stop_word: thus
stop_word: to
stop_word: too
stop_word: until
stop_word: ve
stop_word: very
stop_word: was
stop_word: we
stop_word: were
stop_word: what
stop_word: when
stop_word: where
stop_word: whether
stop_word: which
stop_word: while
stop_word: who
stop_word: whose
stop_word: why
stop_word: will
stop_word: with
stop_word: would
stop_word: yet
stop_word: you
stop_word: your
stop_word: yours

r_table_clause: lob (data) store as (cache)
i_index_clause: compress 2
Re: Oracle text - CONTAINS sql query returning wrong result [message #534064 is a reply to message #534027] Fri, 02 December 2011 08:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
It is producing the correct results. The stem operator returns rows containing any words that have the same stem or root as the word in the query. The word bearing has various definitions and different conjugations for the different definitions. In one case, the stem or root word of bearing is bear and the past tense of bear is bore. So, bearing and bore share the same stem or root word bear. That is why it finds a row containing bore when searching for $bearing.

The following is an excerpt (with bold added) from:


1    [bair] Show IPA verb, bore or ( Archaic ) bare; borne or born; bear·ing.
verb (used with object)
to hold up; support: to bear the weight of the roof.
to hold or remain firm under (a load): The roof will not bear the strain of his weight.
to bring forth (young); give birth to: to bear a child.
to produce by natural growth: a tree that bears fruit.
to hold up under; be capable of: His claim doesn't bear close examination.

[Updated on: Fri, 02 December 2011 08:24]

Report message to a moderator

Re: Oracle text - CONTAINS sql query returning wrong result [message #536460 is a reply to message #534064] Wed, 21 December 2011 01:17 Go to previous message
Messages: 4
Registered: December 2011
Location: India
Junior Member
Thanks Barbara for your justification !! Smile
Previous Topic: SYS Context index is not working for new records
Next Topic: getting error ora-29855 AND Error while creating Index on clob data (2 threads merged by bb)
Goto Forum:

Current Time: Tue Aug 09 00:24:11 CDT 2022