| Oracle text - CONTAINS sql query returning wrong result [message #534027] |
Fri, 02 December 2011 04:33  |
 |
thetechie007
Messages: 4
Registered: December 2011
Location: India
|
Junior Member |
|
|
Hi,
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'
Output
--------
ITEMNUM SCORE DESCRIPTION
---------- -------- ------------
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 DESCRIPTION
===========================================================================
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:
===========================================================================
INDEX OBJECTS
===========================================================================
datastore: DIRECT_DATASTORE
filter: NULL_FILTER
section group: NULL_SECTION_GROUP
lexer: BASIC_LEXER
wordlist: BASIC_WORDLIST
stemmer: ENGLISH
fuzzy_match: GENERIC
stoplist: BASIC_STOPLIST
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
storage: BASIC_STORAGE
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  |
 |
Barbara Boehmer
Messages: 9101
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:
http://dictionary.reference.com/browse/bearing
bear
1 [bair] Show IPA verb, bore or ( Archaic ) bare; borne or born; bear·ing.
verb (used with object)
1.
to hold up; support: to bear the weight of the roof.
2.
to hold or remain firm under (a load): The roof will not bear the strain of his weight.
3.
to bring forth (young); give birth to: to bear a child.
4.
to produce by natural growth: a tree that bears fruit.
5.
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
