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

On Fri, 11 May 2007 14:50:13 +0800, Steve Chien <stevechien_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
>
>

  BTW, I looked at the article in asktom. It looked like there there is no easy way for "LIKE" to work with functional-based index (like nlssort).

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16370675423662

  Any insight? Thanks in advance!

• Steve