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