> <cristian.cudi@yahoo.it> wrote:
> >On May 11, 10:06 am, Steve Chien <stevech@wisagetech.com> wrote:
> >> On 11 May 2007 00:41:48 -0700, Cristian Cudizio

> >> <cristian.cudi@yahoo.it> wrote:
> >> >On May 11, 9:23 am, Steve Chien <stevech@wisagetech.com> wrote:
> >> >> On 11 May 2007 00:09:08 -0700, Cristian Cudizio

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

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