OTN Discussion Forums : Case insensitive search and index ...

user613483

Posts: 95
Registered: 01/08/08

Case insensitive search and index
Posted: Jul 2, 2009 9:38 AM

Reply

I have to execute a case insentitive search.
I created this index on a not null field:

create index indx_prova on
table (nlssort(campo, 'NLS_SORT=BINARY_CI'));

The select is:

select * from tabella where campo like 'A storage%'

This select should retrive 5 records:
A storage ring for crystalline beam studies
a storage ring for crystalline beam studies
A Storage Ring for Crystalline Beam Studies
A storage ring for crystalline beam studies
A storage ring for crystalline beam studies

Instead I got only 3 records:

A storage ring for crystalline beam studies
A storage ring for crystalline beam studies
A storage ring for crystalline beam studies

So The query isn't case insensitive.

I can't set nls_sort=BINARY_CI and nls_comp=LINGUISTIC at level session.

Is there a solution.
Am I doing something wrog?

Justin Cave

Posts: 23,999
Registered: 10/11/99

Re: Case insensitive search and index
Posted: Jul 2, 2009 10:23 AM

in response to: user613483

Reply

If you can't change NLS_SORT/ NLS_COMP at the session level, you can't change the semantics of a search.

Creating the index is only useful when you have also changed the NLS_SORT of the session to tell Oracle that you want a case insensitive sort. As it stands, you're asking for a case-sensitive search on a table where you happen to have an index that would support a case insensitive search.

Justin

user613483

Posts: 95
Registered: 01/08/08

Re: Case insensitive search and index
Posted: Jul 2, 2009 10:28 AM

in response to: Justin Cave

Reply

So I have to set nls_sort=BINARY_CI and I also have to create the index on nls_sort function on that column?

Justin Cave

Posts: 23,999
Registered: 10/11/99

Re: Case insensitive search and index
Posted: Jul 2, 2009 10:34 AM

in response to: user613483

Reply

Changing the NLS_SORT at the session level actually changes the semantics of the search. That's what tells Oracle that you want the search to be case insensitive.

You can have case insensitive searches without having a case insensitive index (just as you can have case sensitive searches without a case sensitive index), it just requires a full table scan. Creating a case insensitive index merely causes the case insensitive search to be reasonably efficient.

If you can't change the NLS_SORT at the session level, you could always throw an UPPER on both the column and the string that you are searching, create a function based index on UPPER( <<column name>> ) and let that do case insensitive searches. That was a standard approach before changing the NLS_SORT/ NLS_COMP at the session level came along and eliminated the need to have UPPER's throughout your code.

Justin

user613483

Posts: 95
Registered: 01/08/08

Re: Case insensitive search and index
Posted: Jul 2, 2009 10:05 PM

in response to: Justin Cave

Reply

I set alter session set nls_comp=LINGUISTIC; alter session set nls_sort=BINARY_CI;

I create this index:
create index titolo_indx on
table (nlssort(campo, 'NLS_SORT=BINARY_CI'));

If I execute this query:
select * from ri01_prodotti where titolo like 'A storage ring f%'

Oracle doesn't user the index.
SQL_ID 7yvspnyf96vp8, child number 0

select * from ri01_prodotti where titolo like 'A storage ring%'

Plan hash value: 350479533

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT				2020 (100)
* 1	TABLE ACCESS FULL	TABLE	1	1365	2020 (1)	00:00:25

Predicate Information (identified by operation id):

1 - filter("CAMPO" LIKE 'A storage ring%')

If I execute a query with =, oracle use index.

select * from table where campo ='A storage ring for crystalline beam studies'

SQL_ID 5jzr5nm6b37pq, child number 0

select * from ri01_prodotti where titolo ='A storage ring for crystalline beam
studies'

Plan hash value: 3866031381

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT				151 (100)
1	TABLE ACCESS BY INDEX ROWID	RI01_PRODOTTI	377	502K	151 (0)	00:00:02
* 2	INDEX RANGE SCAN	TITOLO_INDX	151		3 (0)	00:00:01

Predicate Information (identified by operation id):

2 - access("RI01_PRODOTTI"."SYS_NC00078$"=HEXTORAW('612073746F726167652072696E6720
666F72206372797374616C6C696E65206265616D207374756469657300') )

Richard Foote

Posts: 482
Registered: 12/13/99

Re: Case insensitive search and index
Posted: Jul 2, 2009 11:51 PM

in response to: user613483

Reply

Linguistic indexes don't support the LIKE operator until 11g and so will ignore any available linguistic indexes.

Cheers

Richard Foote
http://richardfoote.wordpress.com/

user613483

Posts: 95
Registered: 01/08/08

Re: Case insensitive search and index
Posted: Jul 3, 2009 12:29 AM

in response to: Richard Foote

Reply

I create an index on UPPER function: create index campo_indx ontable UPPER(campo)

Now the select is:
select * from ri01_prodotti where UPPER(titolo) like UPPER('A storage ring%') ;

Oracle doesn't still user index.

SQL_ID 84kczx4shsv2b, child number 0

select * from ri01_prodotti where UPPER(titolo) like UPPER('A storage
ring%')

Plan hash value: 350479533

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT				2021 (100)
* 1	TABLE ACCESS FULL	RI01_PRODOTTI	1884	2371K	2021 (1)	00:00:25

Predicate Information (identified by operation id):

1 - filter(UPPER("TITOLO") LIKE 'A STORAGE RING%')

Does anyone know why?

Richard Foote

Posts: 482
Registered: 12/13/99

Re: Case insensitive search and index
Posted: Jul 3, 2009 12:38 AM

in response to: user613483

Reply

Yes, you created the index on the wrong column: UPPER(campo) instead of UPPER(titolo) ...

Cheers

Richard Foote
http://richardfoote.wordpress.com/

user613483

Posts: 95
Registered: 01/08/08

Re: Case insensitive search and index
Posted: Jul 3, 2009 12:41 AM

in response to: Richard Foote

Reply

create index titolo_indx on table UPPER(titolo);

SQL_ID 46g4vp9027nrr, child number 0

select * from table where UPPER(titolo) =UPPER('A storage ring for
crystalline beam studies')

Plan hash value: 350479533

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT				2020 (100)
* 1	TABLE ACCESS FULL	RI01_PRODOTTI	377	474K	2020 (1)	00:00:25

Predicate Information (identified by operation id):

1 - filter(UPPER("TITOLO")='A STORAGE RING FOR CRYSTALLINE BEAM
STUDIES')

Oralce doesn't use index.

user613483

Posts: 95
Registered: 01/08/08

Re: Case insensitive search and index
Posted: Jul 3, 2009 2:52 AM

in response to: user613483

Reply

I would like to trye with oracle text index.
So I create this index:

CREATE index TITOLO_INDX ON table ( TITOLO ) INDEXTYPE IS CTXSYS.CONTEXT ;

Now I would like to retrieve all record start with "A storag".

This select fails:
SELECT * FROM RI01_PRODOTTI WHERE contains(titolo,'A stor') >0;

it returns not records.

If I execute this query:
SELECT * FROM RI01_PRODOTTI WHERE contains(titolo,'A stor%') >0;

Oracle retrive a lot of records and not only those one who starts with "A stor".

Richard Foote

Posts: 482
Registered: 12/13/99

Re: Case insensitive search and index
Posted: Jul 3, 2009 5:42 AM

in response to: user613483

Reply

If you create a function based index, you need to make sure you also collect the statistics on the associated virtual column, else all bets are off regarding the CBO doing the right thing.

See my post on http://richardfoote.wordpress.com/2008/12/04/function-based-indexes-and-missing-statistics-no-surprises/ for more details.

Cheers

Richard Foote
http://richardfoote.wordpress.com/

Richard Foote

Posts: 482
Registered: 12/13/99

Re: Case insensitive search and index
Posted: Jul 3, 2009 6:21 AM

in response to: user613483

Reply

user613483 wrote:
I would like to trye with oracle text index.
So I create this index:

CREATE index TITOLO_INDX ON table ( TITOLO ) INDEXTYPE IS CTXSYS.CONTEXT ;

Now I would like to retrieve all record start with "A storag".

This select fails:
SELECT * FROM RI01_PRODOTTI WHERE contains(titolo,'A stor') >0;

Yes, the context index will only return those records that has a specific token (word) = 'stor' (in any case).

it returns not records.

If I execute this query:
SELECT * FROM RI01_PRODOTTI WHERE contains(titolo,'A stor%') >0;

Oracle retrive a lot of records and not only those one who starts with "A stor".

This should return those records in which a specific word starts with 'stor' (assuming that 'A' is treated as a stopword and is ignored) so it should include all records that has the word 'storage' somewhere in the index edcolumn.

Cheers

Richard Foote
http://richardfoote.wordpress.com/