viernes, 7 de septiembre de 2007

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

2 comentarios:

Unknown dijo...

Justo lo que buscaba, muchas gracias :)

Anónimo dijo...

Muy bueno, felicidades!

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.