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

On 11 May 2007 01:09:51 -0700, Cristian Cudizio
<cristian.cudizio@xxxxxxxx> wrote:

On May 11, 10:06 am, Steve Chien <stevech...@xxxxxxxxxxxxxx> wrote:
On 11 May 2007 00:41:48 -0700, Cristian Cudizio

<cristian.cudi...@xxxxxxxx> wrote:
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

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

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 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
.

Relevant Pages