Forum Stats

  • 3,741,292 Users
  • 2,248,405 Discussions
  • 7,861,728 Comments

Discussions

Linguistic index with LIKE + LOWER

Paulo
Paulo Member Posts: 80 Blue Ribbon
edited Dec 18, 2013 4:38AM in SQL & PL/SQL

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

Tagged:

Answers

  • davidp 2
    davidp 2 Member Posts: 400

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

    davidp 2
  • chris227
    chris227 Member Posts: 3,513 Bronze Crown
    edited Dec 18, 2013 4:40AM
    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.

This discussion has been closed.