Mejora de desempeño con Oracle Text

Existen muchos casos de sentencias SQL que lo que intentan a partir de varios campos, saber qué registros cumplen con una palabra "clave". Imaginemos lo siguiente, llega una persona a generar una factura en un grupo que tiene 1 millón de clientes, y el dato que podemos dar para buscar el cliente a nombre de quién se factura es el nombre "Hugo", pero la definición de la tabla tiene nombre, segundo_nombre, primer_apellido, segundo_apellido y razon_social.

Imaginemos también que no se tiene ningún tipo de constraint para evitar el uso indistinto de Mayúsculas/Minúsculas.

Así que aquí tenemos un ejemplo:

SQL> create table catalogo
  2  (nombre varchar2(30),
  3   segundo_nombre varchar2(30),
  4   primer_apellido varchar2(30),
  5   segundo_apellido varchar2(30),
  6   razon_social varchar2(100));

Tabla creada.
 
SQL> insert into CATALOGO
  2  with registros as (
  3     select  OWNER, OBJECT_NAME, OBJECT_TYPE
  4     from    dba_objects
  5     )
  6  select
  7     t2.owner,
  8     substr(t2.OBJECT_NAME,mod(rownum,10),5),
  9     substr(t2.OBJECT_TYPE,1,mod(rownum,10)),
 10     substr(t2.OBJECT_NAME,1,mod(rownum,10)),
 11     t2.owner||' '||t2.object_type||' '||t2.object_name
 12  from
 13     registros       t1,
 14     registros   t2
 15  where rownum < 1000000;
 
999999 filas creadas.

 

SQL> insert into catalogo values('Hugo','Enrique','Contreras','Gamiño',null);

1 fila creada.

SQL> commit;

Ahora ya tenemos 1 millón de registros, y podemos hacer uso de nuestra palabra clave "ENRIQUE" que sabemos de antemano que sólo nos regresará un registro.

SQL> set timing on
SQL> set autot traceonly stat
SQL> select * from catalogo 
  2  where upper(nombre||segundo_nombre||primer_apellido||segundo_apellido||razon_social)
  3  like '%ENRIQUE%';

Transcurrido: 00:00:14.67

EstadÝsticas
----------------------------------------------------------
        285  recursive calls
          0  db block gets
      10087  consistent gets
      10031  physical reads
        116  redo size
        712  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

>

Cómo se puede ver, hay varias situaciones aquí, la primera es que el desarrollador tuvo que concatenar todos los campos descriptivos y además se aplicó la función "UPPER". La segunda es que no importa qué índice de tipo B-TREE se cree, se terminará haciendo un Full Table Scan o Full Index Scan.

La solución qu eparece ser más óptima es con el uso de Oracle Text.

Se eligió para este ejemplo un índice de tipo "Context". Como primer paso se debe de crear un datastore de multicolumna que englobe los campos descriptivos de la tabla que queremos indexar.

SQL> begin
  2  ctx_ddl.create_preference('mi_datastore', 'multi_column_datastore');
  3  ctx_ddl.set_attribute('mi_datastore', 'columns',
  4  'nombre,segundo_nombre,primer_apellido,segundo_apellido,razon_social');
  5  end;
  6  /

Procedimiento PL/SQL terminado correctamente.

Una vez creado el datastore, se puede crear el índice.

SQL> create index cat_texto_idx on catalogo(nombre)
  2  indextype is ctxsys.context
  3  parameters('datastore mi_datastore');
  
Indice creado.

El índice es creado sobre la columns "nombre" y cada registro es tomado encuenta como un documento, por lo cual, oracle text nos permite buscar por el campo nombre y hacer referencia a el datastore múltiple.

SQL> select * from catalogo
  2  where contains(nombre,'enrique',1)>0;

Transcurrido: 00:00:00.02

EstadÝsticas
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        712  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

En este ejemplo observamos que se usa la función "Contains" de oracle text para buscar sobre un campo indexado (que en nuestro ejemplo es nombre). Se puede observar también que para este ejemplo, es indistinto el uso de mayúsculas o minúsculas. y que en lugar de leer más de 10,000 bloques de datos, sólo se leen 21 bloques. en lugar de tardar más de 10 segundos, sólo se tardó el query 0.020 segundos.

Hay algo que se debe de tomar en cuenta, un índice de contexto no es transaccional por default, es decir, a medida que los datos se van modificando (Cualquier DML) el índice queda fuera de sincronía.

SQL> insert into catalogo values('Diego','Armando','Maradona',null,null);

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL> select * from catalogo
  2  where contains(nombre,'maradona',1)>0;
  
ninguna fila seleccionada.

Para mantener la sincronía, existe el siguiente comando

SQL> begin
  2  CTX_DDL.SYNC_INDEX('CAT_TEXTO_IDX','50K');
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL> select * from catalogo
  2  where contains(nombre,'maradona',1)>0;
  
NOMBRE     SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Diego      Armando    Maradona

Ya que esto puede ser inaceptable para muchos modelos de sistemas, oracle tiene una propiedad (que no es default) para poder sincronizar los índices.

SQL> drop index cat_texto_idx;

Indice borrado.

SQL> create index cat_texto_idx on catalogo(nombre)
  2     indextype is ctxsys.context
  3     parameters('datastore mi_datastore sync (on commit)');

Indice creado.

SQL> insert into catalogo values('Eric','Daniel','Cantona',null,null);

1 fila creada.

SQL> select * from catalogo where contains(nombre,'cantona',1)>0;

ninguna fila seleccionada

SQL> commit;

Confirmaci¾n terminada.

SQL> select * from catalogo where contains(nombre,'cantona',1)>0;

NOMBRE     SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Eric       Daniel     Cantona

El índice quedará sincronizado cada que nosotros hagamos commit;

Todo esto es sólo un ejemplo práctico que me ayudó a resolver un problema de desempeño en un sistema, sin embargo es muy limitado en cuanto al uso de Oracle text, por eso recomiendo leer el Manual de referencia de oracle text.