Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte I.

(Continua en parte II).

En muchas ocasiones he podido ver como el uso de las funcionalidades de gestión documental de Oracle era un misterio para los directores de proyecto, que solían confiar para estas tareas en otras herramientas documentales, analistas y programadores.

Éstos últimos, tenían serios problemas en realizar búsquedas por contenido sobre campos almacenados como CLOB, ya que en la mayoría de casos utilizaban la cláusula LIKE con comodines a ambos lados, lo cual impedía el uso por parte del servidor de cualquier índice asociado a esa columna. Otras veces la solución implementada era aun peor (lowers, uppers, substr, instr... ¡sobre un campo que puede llegar a tener hasta 4Gb de capacidad!). Terrible.

Oracle dispone de una potencia de gestión documental principalmente basada en el uso de índices domain. Se trata de índices por palabras (también conocidos como índices CONTEXT) que permiten a Oracle, en una columna de CLOBs o BFILEs, tener una relación del contenido de estos objetos.

El siguiente ejemplo es un pequeño ejercicio basado en el uso de las funcionalidades documentales del servidor Oracle9i.

Pasos:

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.




SQL> -- Inicialmente creo una tabla DOCUMENTOS donde vincularé los documentos en formato word
SQL> -- para su posterior indexación

SQL> create table documentos(
  2        id number constraint pk_documentos primary key,
  3        tipo char(4),
  4        titulo varchar2(60) not null,
  5        documento bfile);

Tabla creada.

SQL> -- Es preciso crear un objeto DIRECTORY, vinculado al directorio de sistema operativo
SQL> -- donde se encuentran los documentos word.

SQL> create or replace directory documentos_word as 'c:\documentos';

Directorio creado.

SQL> grant read on directory documentos_word to CTXSYS;

Concesión terminada correctamente.

SQL> host dir c:\documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:\documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
               4 archivos        935.424 bytes
               2 dirs   4.859.199.488 bytes libres


SQL> -- Inserción de los cuatro documentos sobre la tabla
SQL> -- y creación del índice CONTEXT (la cláusula es INDEXTYPE IS ctxsys.context)
SQL> -----------------------------------------------------------------------------------
SQL> insert into documentos
  2  values (1,'WORD','Entornos no producción. Permisos extendidos.',
  3          BFILENAME('DOCUMENTOS_WORD','EntornosNoProd_Permisos extendidos.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (2,'WORD','Normativa de DBA.',
  3          BFILENAME('DOCUMENTOS_WORD','normas_dba.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (3,'WORD','Creación de un entorno de BBDD.',
  3          BFILENAME('DOCUMENTOS_WORD','creacion_entorno_bbdd.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (4,'WORD','Creación de instancia Oracle en UNIX.',
  3          BFILENAME('DOCUMENTOS_WORD','CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc'));

1 fila creada.

SQL> CREATE INDEX idx_documentos_doc ON documentos(DOCUMENTO) INDEXTYPE IS ctxsys.context;

Índice creado.

-- Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
-- más documentación al respecto en   http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
------------------------------------------------------------

-- documentos con la palabra 'tablespace' cerca de 'storage'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'tablespace near storage') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- documentos con una palabra parecida a 'locally' en un 70%
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 70, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de un entorno de BBDD.

-- documentos con una palabra parecida a 'locally' en un 60%
-- nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 60, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Creación de un entorno de BBDD.
Entornos no producción. Permisos extendidos.

-- documentos que hablen sobre 'listener'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- relevancia de documentos que hablan de 'listener'
-- nota: en 'Normativa de DBA', efectivamente, se habla bastante menos sobre el listener.
SQL> select CONTAINS(documento, 'about(listener)')||'%' relevancia, titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

RELEVANCIA TITULO
---------- ------------------------------------------------------------
78%        Creación de instancia Oracle en UNIX.
20%        Normativa de DBA.


Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732