Oracle 10g2 LIKE operator and case-insensitive issues

From: Steve Chien <stevechien_at_wisagetech.com>
Date: Fri, 11 May 2007 14:50:13 +0800
Message-ID: <vn3843963dp26cd1pqfmvsnvk0g9o8udl6@4ax.com>

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

| 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

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

Received on Fri May 11 2007 - 01:50:13 CDT