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

On May 11, 9:23 am, Steve Chien <stevech...@xxxxxxxxxxxxxx> wrote:
On 11 May 2007 00:09:08 -0700, Cristian Cudizio



<cristian.cudi...@xxxxxxxx> wrote:
On May 11, 9:06 am, Steve Chien <stevech...@xxxxxxxxxxxxxx> wrote:
On 11 May 2007 00:00:03 -0700, Cristian Cudizio

<cristian.cudi...@xxxxxxxx> wrote:
On May 11, 8:50 am, Steve Chien <stevech...@xxxxxxxxxxxxxx> 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

.

Relevant Pages