Uno de los nuevos paquetes PL/SQL con Oracle 23ai es DBMS_SEARCH. Esto se puede usar para indexar (y buscar) varios objetos de esquema en un solo índice. En esta entrada vamos a exponeros varios ejemplos de uso.
Breve introducción del Índex
Este tipo de índice es un poco diferente al índice tradicional.
Con DBMS_SEARCH podemos crear un índice a través de varios objetos de esquema usando un solo índice. Esto nos proporciona mayores capacidades de indexación para escenarios en los que necesitamos buscar datos en varios objetos. Puede crear un índice de búsqueda ubicua en varias columnas de una tabla o en varias columnas de diferentes tablas de un esquema determinado. Todo hecho usando un índice, en lugar de tener que usar múltiples. Debido a esta capacidad de búsqueda más amplia, verá que este (DBMS_SEARCH) se denomina índice de búsqueda ubicuo. Un índice de búsqueda ubicua es un índice de búsqueda JSON y se puede usar para búsquedas de texto completo y basadas en rangos.
Para crear el índice, primero definirá el nombre del índice y, a continuación, le agregará los diferentes objetos de esquema (tablas, vistas). Los comandos principales para crear el índice son:
DBMS_SEARCH. CREATE_INDEX
DBMS_SEARCH. ADD_SOURCE
Nota: Cada tabla utilizada en el ADD_SOURCE debe tener una clave principal.
Descripción del escenario
A continuación, se muestra un ejemplo del uso de este tipo de índice mediante el esquema/conjunto de datos SCOTT.
En este ejemplo se enumeran los pasos implicados en un escenario de búsqueda de base de datos ubicua simple.
Supongamos que, como SCOTT, desea crear una aplicación de consulta de texto que permita a los usuarios consultar documentos en las tablas PRODUCTS y CUSTOMERS. En lugar de crear varios índices o definir manualmente los procedimientos de USER_DATASTORE y MULTI_COLUMN_DATASTORE junto con las vistas materializadas, puede utilizar el paquete PL/SQL DBMS_SEARCH para sintetizar automáticamente los documentos de ambas tablas en un índice de búsqueda ubicuo.
A continuación, puede realizar una búsqueda de texto completo y basada en rangos en las tablas PRODUCTS y CUSTOMERS utilizando el mismo índice.
Pasos para la elaboración
1. Cree las tablas PRODUCTS y CUSTOMERS:
2. Cargue los datos:
3. Cree un índice de búsqueda ubicuo denominado MYINDEX mediante el procedimiento DBMS_SEARCH. CREATE_INDEX:
4. Agregue las tablas PRODUCTS y CUSTOMERS como orígenes de datos al índice mediante el procedimiento DBMS_SEARCH. ADD_SOURCE:
Este procedimiento combina el contenido de todas las columnas de las tablas PRODUCTS y CUSTOMERS
en una única tabla de índice denominada MYINDEX (que coincide con el nombre del índice).
La columna METADATA de la tabla MYINDEX almacena una representación JSON del siguiente formulario para cada fila indexada:
5. Visualice el documento virtual indexado mediante el procedimiento DBMS_SEARCH. GET_DOCUMENT:
El resultado devuelve un documento JSON con valores de metadatos combinados, tal como se indexan en el índice MYINDEX para cada fila de las tablas PRODUCTS y CUSTOMERS:
6. Ejecute consultas en el índice combinado, como se indica a continuación:
Usando CONTAINS:
Para buscar en todos los campos, en la instrucción SELECT, especifique la consulta en la cláusula WHERE con el operador CONTAINS.
Buscar una fuente específica:
Búsqueda en todas las fuentes:
Utilice el operador SCORE con CONTAINS para obtener una puntuación de relevancia:
Usando JSON_TEXTCONTAINS:
Para buscar datos JSON, en la instrucción SELECT, especifique la consulta en la cláusula WHERE con el operador JSON_TEXTCONTAINS.
Especifique la expresión de ruta JSON para indicar los campos en los que desea buscar el texto especificado.
Buscar una fuente específica:
Utilice la notación de puntos JSON para obtener el valor de la clave principal individual:
Utilice JOIN para obtener varios valores de clave principal:
Esta consulta une filas de la tabla CUSTOMERS de acuerdo con la condición de equijoin:
Uso JSON_EXISTS:
Para buscar un valor determinado dentro de los datos JSON, en la instrucción SELECT, especifique la consulta en la cláusula WHERE con el operador JSON_EXISTS.
Utilice JSON_EXISTS en el tipo de dato número:
Utilice JSON_EXISTS en el tipo de dato string:
Esperamos que esta entrada haya sido de utilidad. Nos vemos en próximas entradas. Puedes ver nuestro contenido de Oracle aquí.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn