RE: Oracle Context index returning inconsistent results

From: Clarke, Andrew <andrew.clarke_at_logica.com>
Date: Tue, 27 Jan 2009 12:19:37 +0000
Message-ID: <C06698D943003C4097B7544C28E32F1F04E01713_at_uk-ex002.groupinfra.com>

>  Does not work:   


>	where contains (title,)

>	where contains (title,)


Text indexes whole words.  So we would not expect '5:1254C' to match

'05:1254C'. However, it seems reasonable to expect '%05:1254C' to match string, and in my 11g instance it does (score label 20)....

SQL> select title

  2          , score(10) as sc_10
  3          , score(20) as sc_20
  4          , score(30) as sc_30
  5          , score(40) as sc_40
  6          , score(50) as sc_50
  7          , score(60) as sc_60
  8          , score(70) as sc_70

  9 from a
 10 where contains (title,'5:1254C', 10) >= 0

 11  or contains (title,'%05:1254C', 20) >= 0
 12  or contains (title,'%5:1254C',30) >= 0
 13  or contains (title,'05:1254C%',40) >= 0
 14  or contains (title,'%05:1254C%',50) >= 0
 15  or contains (title,'%5:1254C%',60) >= 0
 16  or contains (title,'05:1254C', 70) >= 0

 17 order by id
 18 /

TITLE SC_10 SC_20 SC_30 SC_40 SC_50 SC_60 SC_70 ------------ ------ ------ ------ ------ ------ ------ ------

5:1254C           5      0      4      0      0      4      0
A 5:1254C H       5      0      4      0      0      4      0
A 05:1254C H      0      4      4      5      4      4      5
A 45:1254C H      0      0      4      0      0      4      0
05:1254C          0      4      4      5      4      4      5
15:1254C          0      0      4      0      0      4      0
45:1254C          0      0      4      0      0      4      0
05:1254CF         0      0      0      5      4      4      0
5:1254CF          0      0      0      0      0      4      0
45:1254CF         0      0      0      0      0      4      0
105:1254C         0      4      4      0      4      4      0
105:1254CF        0      0      0      0      4      4      0

12 rows selected.

SQL> Cheers, APC

Please help Logica to respect the environment by not printing this email / Merci d'aider Logica à préserver l'environnement en évitant d'imprimer ce mail / Bitte drucken Sie diese Nachricht nicht aus und helfen Sie so Logica dabei die Umwelt zu schuetzen / Por favor ajude a Logica a respeitar o ambiente não imprimindo este correio electrónico.

This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you.

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

Received on Tue Jan 27 2009 - 06:19:37 CST