Linguistic index with LIKE + LOWER

Paulo

Hi,

It seems that Oracle 11.2.0.1.0 is not able to use linguistic index when using the LIKE operator + LOWER function...

ALTER SESSION SET NLS_COMP='LINGUISTIC';

ALTER SESSION SET NLS_SORT='FRENCH_AI';

CREATE INDEX test_fai_idx

ON mytable(NLSSORT(LOWER(somefield), 'NLS_SORT=FRENCH_AI'));

SELECT *

FROM mytable

WHERE LOWER(somefield) LIKE 'gue%';

-- Table access FULL

DROP INDEX test_fai_idx;

ALTER SESSION SET NLS_COMP='BINARY';

CREATE INDEX test_bin_idx

ON mytable(LOWER(somefield));

SELECT *

FROM mytable

WHERE LOWER(somefield) LIKE 'gue%';

-- Index Range Scan

Questions:

-Will an upgrade to 11.2.0.2, 11.2.0.3 or 11.2.0.4 could solve that problem? (if anyone of you who have access to such a db and could make the test and report the result here, that will be much appreciated)

-Other ideas to help me?

I understand that using LOWER + AI seem strange since AI means accent-insensitive AND case-insensitive but, the software which run on the db use LOWER in all his SQL...

Thanks.

Paulo

davidp 2

I get the same behaviour on 11.2.0.3 - it uses the index for LOWER(somefield) = but not for LOWER(somefield) like

chris227

create table test (nid number, name varchar2(30))

insert into test values(1, 'hügo')

ALTER SESSION SET NLS_COMP='LINGUISTIC';
ALTER SESSION SET NLS_SORT='FRENCH_AI';

select
*
from test
where
lower(name) like 'hug%'

no data found, full scan

create index idx_t on test(lower(name))

select
*
from test
where
lower(name) like 'hug%'

no data found, index range scan

drop index idx_t
create index idx_t on test(nlssort(lower(name), 'NLS_SORT=FRENCH_AI'))

select
*
from test
where
lower(name) like 'hug%'

no data found, full scan

Why should the index be used? You are looking for lower(name) but nlssort(lower(name), 'NLS_SORT=FRENCH_AI') indeed is something totally different:

select
nlssort(lower(name), 'NLS_SORT=FRENCH_AI') s
from test
S
3773325A000202020200

since

select
nlssort('hug', 'NLS_SORT=FRENCH_AI') s
from test

is

S
3773320002020200

you might recongize that it is not possible to do a like with nlssort.