Búsquedas Case-Insensitive

Los tipos de búsquedas Case-Insensitive (nueva funcionalidad en Oracle 10gR2) nos permiten tratar por igual las letras mayúsculas y las letras minúsculas cuando realizamos una búsqueda.

Veamos...

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Fijense que cuando busco las palabra 'oracle' en minúsculas, gracias a ésta nueva funcionalidad, la consulta me devuelve todas las palabras 'oracle' que encuentra.
Pero cómo logramos realizar ésto? Necesitamos setear algún parámetro?

Realicemos el ejemplo completo:

SQL_10gR2> CREATE TABLE test ( texto  VARCHAR2(10) ) ;

Table created.

SQL_10gR2> INSERT INTO test VALUES ( 'Oracle' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'ORACLE' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'oraCLE' ) ;

1 row created.

SQL_10gR2> INSERT INTO test VALUES ( 'oracle' ) ;

1 row created.

Ya tenemos nuestra tabla creada con sus respectivos datos cargados.
Ahora vamos a prestar atención a 2 parámetros: NLS_COMP y NLS_SORT.

En versiones anteriores a la 10gR2, si teníamos la tabla TEST (la tabla de nuestro ejemplo) con estos mismos datos cargados y buscabamos la palabra 'oracle' (con el parámetro NLS_SORT = BINARY que es el valor por default), veríamos lo siguiente:

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
oracle

1 row selected.

Qué sucede si nuestra aplicación necesita realizar búsquedas que devuelvan tanto los valores que se encontraron en mayúsculas como los valores encontrados en minúsculas?

SQL_10gR2> ALTER SESSION SET NLS_COMP = ANSI ;

Session altered.

SQL_10gR2> ALTER SESSION SET NLS_SORT = BINARY_CI ;

Session altered.

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Al setear el parámetro NLS_SORT = BINARY_CI, Oracle convierte nuestras búsquedas en case-insensitive.

Pero qué sucede si nuestra aplicación necesita hacer búsquedas del tipo LIKE '%ora%' porque no conoce la palabra completa que quiere buscar?

SQL_10gR2> SELECT * FROM test WHERE texto LIKE '%ora%' ;

TEXTO
----------
oraCLE
oracle

2 rows selected.

Al parecer con los parámetros que seteamos anteriormente no fue suficiente para satisfacer el resultado de ésta consulta.
Bien, es hora de setear correctamente el parámetro NLS_COMP. Si lo seteamos con el valor LINGUISTIC, le dice a Oracle que cada sort y comparación, use el orden lingüístico del parámetro NLS_SORT. Esto quiere decir, que si seteamos NLS_SORT = BINARY_CI, todos los sorts y comparaciones van a ser case-insensitive en esa sesión.

SQL_10gR2> ALTER SESSION SET NLS_COMP = LINGUISTIC ;

Session altered.

SQL_10gR2> ALTER SESSION SET NLS_SORT = BINARY_CI ;

Session altered.

SQL_10gR2> SELECT * FROM test WHERE texto LIKE '%ora%' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

SQL_10gR2> SELECT * FROM test WHERE texto = 'oracle' ;

TEXTO
----------
Oracle
ORACLE
oraCLE
oracle

4 rows selected.

Fijense, que éste último seteo de parámetros me sirvió para satisfacer las 2 consultas que estuvimos realizando!

Veamos el explain plan de ésta última consulta:

SQL_10gR2> explain plan for
  2  SELECT * FROM test WHERE texto = 'oracle' ;

Explained.

SQL_10gR2> @explains
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("TEXTO",'nls_sort=''BINARY_CI''')=HEXTORAW('6F7261636C6500') )

Note
-----
   - dynamic sampling used for this statement
   - star transformation used for this statement

Vemos que el filter del explain plan nos muestra que se está utilizando case-insensitive.
Bien, ahora vamos a crear un índice con el contenido del filter que acabamos de ver:

SQL_10gR2> CREATE INDEX test_texto_idx ON test (NLSSORT(texto,'NLS_SORT=BINARY_CI')) ;

Index created.

Ejecutamos nuevamente la consulta:

SQL_10gR2> explain plan for
  2  SELECT * FROM test WHERE texto = 'oracle' ;

Explained.

SQL_10gR2> @explains
Plan hash value: 2134909805

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     4 |    28 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     4 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_TEXTO_IDX |     4 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("TEXTO",'nls_sort=''BINARY_CI''')=HEXTORAW('6F7261636C6500') )

Note
-----
   - dynamic sampling used for this statement
   - star transformation used for this statement