Re: Oracle 10g2 LIKE operator and case-insensitive issues

On 11 May 2007 00:00:03 -0700, Cristian Cudizio <cristian.cudizio_at_yahoo.it> wrote:

>On May 11, 8:50 am, Steve Chien <stevech..._at_wisagetech.com> wrote:
>> Hi,
>>
>> We're curerrently facing some performance issues related to the
>> "LIKE" operator in Oracle 10g2. Here is what we encountered.
>>
>> We have a Oracle 10g2 database which has the following
>> characteristics.
>>
>> NLS_CHARACTERSET => AL32UTF8
>> NLS_NCHAR_CHARACTERSET => AL16UTF16
>> NLS_RDBMS_VERSION => 10.2.0.1.0
>>
>> We created a table like below.
>> -- creates test table
>> CREATE TABLE MYTEST
>> (id NUMBER(10, 0) NOT NULL,
>> str1 VARCHAR2(128) NOT NULL,
>> str2 NVARCHAR2(128) NOT NULL);
>>
>> Then, we populated with some random data.
>> -- PL/SQL for creating random data
>> BEGIN
>> DBMS_RANDOM.SEED('thisisjustatest');
>> FOR i IN 1 .. 100000 LOOP
>> INSERT INTO MYTEST VALUES(i, DBMS_RANDOM.STRING('P', 64),
>> DBMS_RANDOM.STRING('P', 64));
>> END LOOP;
>> INSERT INTO MYTEST VALUES(100001, 'steve', 'chien');
>> INSERT INTO MYTEST VALUES(100002, 'STEVE', 'CHIEN');
>> END;
>>
>> Afterwards, we created the indexes.
>> -- creates indexes
>> CREATE INDEX AK1_ID_MYTEST ON MYTEST(ID);
>> CREATE INDEX AK2_STR1_MYTEST ON MYTEST(STR1);
>> CREATE INDEX AK3_STR2_MYTEST ON MYTEST(STR2);
>>
>> With the "autotrace" turned on , NLS_COMP set to BINARY, and
>> NLS_SORT set to BINARY in SQLPlus, we did two experiments.
>>
>> CASE I.
>> select * from mytest where str1 = 'steve'
>>
>> Plan hash value: 587925449
>>
>> -----------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>> -----------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 1 | 209 | 5 (0)| 00:00:01 |
>> | 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 209 | 5 (0)| 00:00:01 |
>> |* 2 | INDEX RANGE SCAN | AK2_STR1_MYTEST | 1 | | 3 (0)| 00:00:01 |
>> -----------------------------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 2 - access("STR1"='steve')
>>
>> CASE II.
>> select * from mytest where str1 like 'steve%';
>>
>> Plan hash value: 587925449
>>
>> -----------------------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>> -----------------------------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 1 | 209 | 5 (0)| 00:00:01 |
>> | 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 209 | 5 (0)| 00:00:01 |
>> |* 2 | INDEX RANGE SCAN | AK2_STR1_MYTEST | 1 | | 3 (0)| 00:00:01 |
>> -----------------------------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 2 - access("STR1" LIKE 'steve')
>>
>> Here is what bothered us more... We actually wanted to do
>> case-insensitive searches & sorts on columnes str1 & str2. We dropped
>> the indexes and re-created them as blows.
>>
>> - drop & re-create indexes
>> DROP INDEX AK1_ID_MYTEST;
>> DROP INDEX AK2_STR1_MYTEST;
>> DROP INDEX AK3_STR2_MYTEST;
>>
>> - creates indexes
>> CREATE INDEX AK1_ID_MYTEST ON MYTEST(ID)
>> CREATE INDEX AK2_STR1_MYTEST ON MYTEST(NLSSORT(STR1,
>> 'NLS_SORT=GENERIC_M_CI'));
>> CREATE INDEX AK3_STR2_MYTEST ON MYTEST(NLSSORT(STR2,
>> 'NLS_SORT=GENERIC_M_CI'));
>>
>> With "autotrace" turned on, NLS_COMP set to LINGUISTIC, and NLS_SORT
>> set to GENERIC_M_CI in SQLPlus, we dir the following two test cases.
>>
>> CASE I.
>> select * from mytest where str1 = 'steve'
>>
>> Plan hash value: 3883648009
>>
>> ------ -
>> -----------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>> ------ -
>> -----------------------------------------
>> | 0 | SELECT STATEMENT | | 851 | 173K| 404 (1)| 00:00:05 |
>> | 1 | TABLE ACCESS BY INDEX ROWID| MYTEST | 851 | 173K| 404 (1)| 00:00:05 |
>> |* 2 | INDEX RANGE SCAN | AK2_STR1_MYTEST | 340 | | 3 (0)| 00:00:01 |
>> ------ -
>> -----------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 2 - access(NLSSORT("STR1",'nls_sort=''GENERIC_M_CI''')=HEXTORAW('
>> 024F025501FE026101FE00000202020202') )
>>
>> CASE II.
>> select * from mytest where str1 like 'steve%';
>>
>> Plan hash value: 1692938441
>>
>> ------ - -----------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>> ------ - -----------------------------------------
>> | 0 | SELECT STATEMENT | | 32 | 6688 | 791 (1)| 00:00:10 |
>> |* 1 | TABLE ACCESS FULL| MYTEST | 32 | 6688 | 791 (1)| 00:00:10 |
>> ------ - -----------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 1 - filter("STR1" LIKE 'steve%')
>>
>> Oracle was using the "TABLE ACCESS FULL" to handle the "LIKE"
>> operator. It's extremely slow and we wondered why it couldn't use the
>> "INDEX RANGE SCAN" anymore.
>>
>> Thanks for any suggestion!
>>
>> - Steve
>
>On asktom.oracle.com you can find useful information about Oracle db
>case sensitive. However
>from 10gR2 you can use
>NLS_COMP = LINGUISTIC
>NLS_SORT = BINARY_CI
>
>it make searches case insensitive
>
>Bye
> Cristian Cudizio
>
>http://oracledb.wordpress.com
>http://cristiancudizio.wordpress.com

Hi,

  We did try the BINARY_CI too. However, the outstanding question is that the Oracle was not using the index with the "LIKE" operator.

  Any suggestion?

  Thanks!

• Steve