Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle text: contains operator with index ignoring diacritical marks

640834Dec 2 2008 — edited Dec 3 2008
Hi,

I want to create a domain index ignoring diacritical marks like (á,é,ì,ü,...), but it doesn't work. Here's my script:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> drop table test;

Tabla borrada.

SQL> begin
  2    ctx_ddl.drop_preference('my_lexer');
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> create table test (x varchar(30));

Tabla creada.

SQL> insert into test values ('rápido');

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL>
SQL>
SQL> begin
  2    ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  3    ctx_ddl.set_attribute('my_lexer', 'base_letter', 'yes');
  4  end;
  5  /

Procedimiento PL/SQL terminado correctamente.

SQL> commit;

Confirmaci¾n terminada.

SQL>
SQL>
SQL> CREATE INDEX test_idx
  2      ON test(x)
  3      INDEXTYPE IS CTXSYS.CONTEXT
  4  parameters ('lexer my_lexer Sync (on commit)');

-ndice creado.

SQL>
SQL> select x, dump(x) from test;
X                              DUMP(X)
------------------------------ -------------------------------------
rápido                         Typ=1 Len=6: 114,160,112,105,100,111

1 fila seleccionada.

SQL>
SQL> select * from test where  contains(x,'rapido',1)>0;

ninguna fila seleccionada

SQL>
SQL> select token_text from dr$test_idx$i;
TOKEN_TEXT
----------------------------------------------------------------
PIDO
R

2 filas seleccionadas.
Why Oracle is indexing "rápido" in two pieces, "R" and "PIDO"? I guess that is the reason why "contains(x,'rapido',1)>0" does not find any row.
Am I doing anything wrong related to the baisc_lexer?

Thanks!

Joaquin Gonzalez

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 31 2008
Added on Dec 2 2008
#general-database-discussions, #index, #text
1 comment
666 views