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

On May 11, 11:02 am, Steve Chien <stevech..._at_wisagetech.com> wrote:
> On 11 May 2007 01:09:51 -0700, Cristian Cudizio
>
> <cristian.cudi..._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
>
> ----------------------------------------------------------------------------
> | 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

i don't understand precisely, i've strange interaction between autotrace and NSL settings

Bye
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Fri May 11 2007 - 04:14:26 CDT