RE: Oracle Context index returning inconsistent results

From: Robert Buda <rjbuda_at_budaconsulting.com>
Date: Mon, 26 Jan 2009 12:05:54 -0500
Message-ID: <004701c97fd8$5b2fc2d0$1500a8c0_at_BudaConsulting.local>

Thanks Rich but these are the actual values so any issue with concatenation of Numbers and Letters should impact both rows.

Any other ideas?

Bob

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Monday, January 26, 2009 11:40 AM
To: oracle-l_at_freelists.org
Subject: Re: Oracle Context index returning inconsistent results

> Now when I use the contains clause to get the data, the results differ:
> When I use the first value above, no rows are returned
>
> SQL> select *
> 2 from inventory
> 3 where contains (title,'05:1254C') > 0
>
>
> no rows selected
>
>
> When I use the second value a row is returned properly
>
> SQL> select inventorynumber
> 2 from inventory
> 3 where contains (title,'97:0273C') > 0 ;
>
>
> 97:0273C

Are those the actual values you're using? Pay attention to the lexer and stoplists. IIRC, when I used a Context index in 10g (can't remember R1 or R2), the defaults segregated concatenated alpha and digits (e.g. "ABC123" was indexed as "ABC" and "123"), which gave me odd results.

This doesn't explain the disparity between the two similarly-formatted examples above, but may prove to be a valid workaround. BE SURE TO TEST your changes!

HTH! GL! Rich

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 26 2009 - 11:05:54 CST