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

From: Steve Chien <stevechien_at_wisagetech.com>
Date: Fri, 11 May 2007 17:02:07 +0800
Message-ID: <nub843t0nju0ef9qmoddug04a06i9s7a93@4ax.com>

On 11 May 2007 01:09:51 -0700, Cristian Cudizio <cristian.cudizio_at_yahoo.it> wrote:

>On May 11, 10:06 am, Steve Chien <stevech..._at_wisagetech.com> wrote:
>> On 11 May 2007 00:41:48 -0700, Cristian Cudizio
>>
>> <cristian.cudi..._at_yahoo.it> wrote:
>> >On May 11, 9:23 am, Steve Chien <stevech..._at_wisagetech.com> wrote:
>> >> On 11 May 2007 00:09:08 -0700, Cristian Cudizio
>>
>> >> <cristian.cudi..._at_yahoo.it> wrote:
>> >> >On May 11, 9:06 am, Steve Chien <stevech..._at_wisagetech.com> wrote:
>> >> >> On 11 May 2007 00:00:03 -0700, Cristian Cudizio
>>
>> >> >> <cristian.cudi..._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
>>
>> >> >Yes, hear me, use
>> >> >NLS_COMP = LINGUISTIC
>> >> >NLS_SORT = BINARY_CI
>>
>> >> >it works on 10gR2
>>
>> >> >Bye
>> >> > Cristian Cudizio
>>
>> >> >http://oracledb.wordpress.com
>> >> >http://cristiancudizio.wordpress.com
>>
>> >> Hi,
>>
>> >> I dropped the orignal index and did,
>>
>> >> c
>>
>> >> With "autotrace" turned on, NLS_COMP set to LINGUISTIC, and NLS_SORT
>> >> set to BINARY_CI in SQLPlus, we ran the following test again.
>>
>> >> select * from mytest where str1 like 'steve%';
>>
>> >> We got the following plan,
>>
>> >> 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%')
>>
>> >> Note
>> >> -----
>> >> - dynamic sampling used for this statement
>>
>> >> Still "TABLE ACCESS FULL SCAN"... Did I miss anything?
>>
>> >> Thanks!
>>
>> >> - Steve
>>
>> >Yes,
>> >create index normally,
>> >create index ak2_str1_mytest on mytest(str1)) ;
>>
>> >Bye
>> >Cristian Cudizio
>>
>> >http://oracledb.wordpress.com
>> >http://cristiancudizio.wordpress.com
>>
>> Hi,
>>
>> We dropped the index and recreated as below.
>>
>> create index ak2_str1_mytest on mytest(str1) COMPUTE STATISTICS;
>>
>> With "autotrace" turned on, NLS_COMP set to LINGUISTIC, and NLS_SORT
>> set to BINARY_CI, we still got the following plan.
>>
>> select * from mytest where str1 like 'steve%';
>>
>> Plan hash value: 1692938441
>>
>> ----------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
>> ----------------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 2 | 418 | 791 (1)| 00:00:10 |
>> |* 1 | TABLE ACCESS FULL| MYTEST | 2 | 418 | 791 (1)| 00:00:10 |
>> ----------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 1 - filter("STR1" LIKE 'steve%')
>>
>> Note
>> -----
>> - dynamic sampling used for this statement
>>
>> Still "TABLE ACCESS FULL SCAN"? Any thought?
>>
>> - Steve
>
>i don't user "compute statistics" on index creation,
>the note
> Note
>> -----
>> - dynamic sampling used for this statement
>says that statistc are not present, use FIRST RULE OF CBO: compute
>statistisc
>use
>exec
>dbms_stats.gather_table_stats(OWNNAME=>'OWNER',tabname=>'TABLENAME',CASCADE=>TRUE);
>
>Bye
>Cristian Cudizio
>
>http://oracledb.wordpress.com
>http://cristiancudizio.wordpress.com
>

Hi,

Sorry for bothering again...

We dropped & re-created the index like below.

drop index ak2_str1_mytest;
create index ak2_str1_mytest on mytest(str1);

Also, we gathered the stats by running,

exec DBMS_STATS.gather_table_stats('STEVECHIEN', 'MYTEST', cascade => true);

With "autotrace" turned on, NLS_COMP set to LINGUISTIC, and NLS_SORT set to BINARY_CI, we still got similar results,

select * from mytest where str1 like 'steve%';

Plan hash value: 1692938441

|   0 | SELECT STATEMENT  |        |     1 |   198 |   792   (2)| 00:00:10 |
|*  1 |  TABLE ACCESS FULL| MYTEST |     1 |   198 |   792   (2)| 00:00:10 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - filter("STR1" LIKE 'steve')

          1  recursive calls
          0  db block gets
       2897  consistent gets
          0  physical reads
          0  redo size
        592  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Any thought?

Steve

Received on Fri May 11 2007 - 04:02:07 CDT