Skip navigation
1 2 Previous Next

PL/SQL Argentina

21 posts

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar las mejoras introducidas a la función JSON_OBJECT.

 

En los siguientes ejemplos, vamos a utilizar una tabla llamada Producto que podemos crear con el script incluido a continuación:

 

CREATE TABLE Producto (

      ID NUMBER(10) NOT NULL,

      Marca VARCHAR(100) NOT NULL,

      Nombre VARCHAR(100) NOT NULL,

      Precio NUMBER(10,4) NOT NULL,

      CONSTRAINT PK_Producto PRIMARY KEY (ID)

);

INSERT INTO Producto VALUES (1, 'Sony', 'TV LED 40"', 20000);

INSERT INTO Producto VALUES (2, 'Philips', 'TV LED 32"', 15000 );

INSERT INTO Producto VALUES (3, 'Motorola', 'Moto Z4', 25000);

--

COMMIT;

 

 

Uso de Comodín

Es posible utilizar el comodín “*” para referenciar todas las columnas en un solo paso. Se puede usar el mismo para toda la consulta o para una tabla en particular usando un alias:

 

SELECT JSON_OBJECT(t.*) AS json_data FROM <Tabla> t;

 

Ejemplo

SELECT JSON_OBJECT(p.*) AS json_data FROM Producto p;

 

 

Lista de Columnas

Es posible utilizar una lista de columnas separadas por coma. Los nombres de los elementos mantienen las mayúsculas / minúsculas definidas en la lista:

 

SELECT JSON_OBJECT(columnaA, ColumnaB) AS json_data

FROM <Tabla> t;

 

 

Ejemplo

 

SELECT JSON_OBJECT(Nombre, Precio) AS json_data

FROM Producto p;

 

 

 

Sintaxis KEY ... VALUE simplificada

No es necesario utilizar la sintaxis ‘KEY … VALUE’, pudiéndose usar directamente “:” para definir los pares de datos:

 

SELECT JSON_OBJECT('ID' : id,

                   'Nombre' : name) AS json_data

FROM persons;

 

Ejemplo

 

SELECT JSON_OBJECT('ID':id,

                   'Producto':nombre) AS json_data_new

FROM Producto p;

 

 

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_SERIALIZE.

 

La misma nos permite convertir un documento JSON, cualquiera sea su formato (BLOB, RAW, CLOB) en texto

 

La sintaxis de la función es la siguiente:

 

JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]

                 [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])

 

  • La cláusula "RETURNING" así como las opciones “PRETTY”, “ASCII” y “TRUNCATE” se comportan igual a lo visto en la función JSON_MERGEPATCH.
  • La función JSON_SERIALIZE permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.
  • Al igual que la función “TREAT(… AS JSON)” y "JSON_ MERGEPATCH", es una función SQL que no puede emplearse en forma directa en PL/SQL.

 

Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos en una columna BLOB:

CREATE TABLE BLOBJSON (

       ID NUMBER(10) NOT NULL,

       Datos BLOB,

       CONSTRAINT PK_BLOBJSON PRIMARY KEY (ID),

       CONSTRAINT CK_BLOBJSON_DatosJSON CHECK(Datos IS JSON)

);

INSERT INTO BLOBJSON VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');

INSERT INTO BLOBJSON VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');

INSERT INTO BLOBJSON VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');

--

COMMIT;

Si queremos consultar los datos, no podemos hacerlo ya que se encuentran en formato binario:

 

 

Si los datos no son muy grandes, se puede usar la función "UTL_RAW.cast_to_varchar2"para realizar la conversion:

 

SELECT UTL_RAW.cast_to_varchar2(Datos) AS data2 FROM BLOBJSON;

 

 

Y el resultado que obtenemos es el siguiente:

 

 

Otra opción es utilizar la función JSON_QUERY para ver el contenido:

 

SELECT JSON_QUERY(Datos, '$') AS data FROM BLOBJSON;

 

 

Y el resultado que obtenemos es el siguiente:

 

O directamente utilizar la nueva función (en este caso, adicionalmente estamos filtrando la consulta para ver solo un registro):

 

SELECT JSON_SERIALIZE(d.Datos PRETTY) AS data

FROM   BLOBJSON d

WHERE  d.datos.marca = 'Dodge';

 

 

Y el resultado que obtenemos es el siguiente:

 

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_MERGEPATCH.

 

En Oracle 12c y 18c para modificar un documento JSON era necesario obtenerlo, procesar y aplicar los cambios y luego reemplazar el documento original con la versión modificada. Con “JSON_MERGEPATCH” es posible simplificar el proceso notablemente en algunos casos, ya que esta función permite modificar el contenido de un objeto JSON, ya sea en una sentencia SELECT como en un UPDATE.

 

La sintaxis de la función es la siguiente:

 

JSON_MERGEPATCH

   ( target_expr, patch_expr[returning_clause] [PRETTY] [ASCII]

     [TRUNCATE] [on_error_clause] )

 

Si “patch_expr” se refiere a un elemento existente (o a un grupo de elementos), el o los mismos son actualizados con el valor provisto en la expresión, al cual puede ser por ejemplo el valor en blanco.

  • Si “patch_expr” no se refiere a un elemento existente, el mismo es agregado a cada documento.
  • Si “patch_expr” le asigna el valor nulo a un elemento, el mismo es removido de los documentos.
  • La clausula "RETURNING" funciona como en todas las otras funciones de manejo de JSON, y permite definir un tipo de dato.
  • La opción "PRETTY" muestra el resultado con un formato mas legible, aunque mas extenso.
  • La opción "TRUNCATE" indica que el resultado debe ser truncado si es mayor que el tipo de datos definido.
  • La opción "ASCII" convierte cualquier carácter no-ASCII a una secuencia de escape JSON.
  • La función JSON_MERGEPATCH permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.

 

Al igual que la función “TREAT(… AS JSON)”, es una función SQL que no puede emplearse en forma directa en PL/SQL. Para hacerlo es necesario embeber la misma en una llamada a SQL, por ejemplo "SELECT JSON_MERGEPATCH(...) INTO xxx FROM DUAL".

 

La función JSON_MERGEPATCH se puede utilizar tanto en un SELECT como en sentencias UPDATE para actualizar los datos contenidos en un documento JSON.

 

Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos:

 

CREATE TABLE DocumentoJSON (

      ID_DocumentoJSON NUMBER(10) NOT NULL,

      Datos CLOB,

      CONSTRAINT PK_DocumentoJSON PRIMARY KEY (ID_DocumentoJSON),

      CONSTRAINT CK_DocumentoJSON_DatosJSON CHECK(Datos IS JSON)

);

INSERT INTO DocumentoJSON

    VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');

INSERT INTO DocumentoJSON

    VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');

INSERT INTO DocumentoJSON

    VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');

--

COMMIT;

 

A continuación vamos a consultar los datos, pero reemplazando el elemento "cantidad" con el valor 500:

 

SELECT JSON_MERGEPATCH(Datos, '{"cantidad":500}') AS DatosModificados

FROM   DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente:

 

 

A continuación vamos a consultar nuevamente los datos, pero agregando el elemento "Marca" (recuerden que "marca" ya existe, pero  JSON es case sensitive en cuanto a la definición de los nombres de elementos por lo que lo considera un nuevo elemento):

 

SELECT JSON_MERGEPATCH(Datos, '{"Marca":"Skoda"}') AS DatosModificados
FROM   DocumentoJSON;

Y el resultado que obtenemos es el siguiente (noten que la cantidad se mantiene tal cual la insertamos originalmente, ya que la función aplicada en el ejemplo anterior fue en una sentencia SELECT y por lo tanto no modificó los datos):

 

 

 

En este ejemplo, vamos a remover el elemento "marca", asignándole el valor NULL:

 

SELECT JSON_MERGEPATCH(Datos, '{"marca":NULL}' PRETTY) AS DatosModificados

FROM   DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente:

 

 

Como ultimo ejemplo, vamos a actualizar los datos en la tabla, agregando el elemento "importado", y luego consultamos los datos tal cual están guardados en la base de datos:

 

UPDATE DocumentoJSON dj

SET Datos = JSON_MERGEPATCH(Datos, '{"importado":"USA"}') ;

--

SELECT * FROM DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente:

 

El miércoles pasado, Oracle anuncio a través de un Press Release una asociación que permite una mejor interoperabilidad entre su nube "Oracle Cloud Infrastructure (OCI)" y la nube de Microsoft, Azure.

 

Considerando que mas del 80% de las empresas y organizaciones utilizan productos de Oracle y Microsoft, resulta normal que las mismas deseen mezclar y combinar los servicios de Oracle Cloud y Microsoft Azure para obtener el mejor rendimiento para un determinado proyecto o funcionalidad, sin tener que aprender nuevas habilidades, reescribir aplicaciones o modificar la arquitectura de sus sistemas

 

"Los clientes han estado invirtiendo en productos de Oracle y Microsoft por décadas", mencionó Don Johnson, vicepresidente ejecutivo de Oracle Cloud. "Ahora que hemos conectado nuestras nubes, estamos permitiendo a los clientes aprovechar toda su inversión pre-existente (tanto en conocimiento como en licencias) para cumplir con sus objetivos de migración a la nube y modernización de negocios".

 

Este acuerdo de interoperabilidad le permite a una empresa aprovechar lo mejor de la nube de cada compañía, incluido el completo ecosistema de Azure y los servicios de bases de datos autónomas y de Exadata de Oracle.

 

 

El acuerdo

La base del acuerdo es la inversión realizada por ambas empresas para conectar ambas plataformas mediante redes privadas dedicadas. ExpressRoute en Azure y FastConnect en OCI dependen de un conjunto de socios que proporcionan conectividad y conmutación de red. Este conjunto de proveedores de telecomunicaciones, redes y co-ubicación entregan conectividad de alta velocidad entre la nube y los centros de datos de ambas compañías.

 

De esta forma, los clientes pueden conectar perfectamente Azure y OCI para mezclar y combinar las cargas de trabajo en los entornos de nube. Por ejemplo, una aplicación web implementada en Azure puede comunicarse con una base de datos Oracle que se ejecuta en OCI casi sin cambios en el código.

 

Este acuerdo se implemento inicialmente en el datacenter de Ashburn, en la costa este de los EE. UU., pero se ira ampliando a los otros datacenter de ambas compañías.

 

 

Algunas características adicionales permite mejorar la integración entre ambas plataformas

 

Identidad unificada y gestión de acceso.

Microsoft ofrece el poder de Active Directory (AD) D a la nube permitiendo el inicio de sesión único para aplicaciones de línea de negocios y aplicaciones web implementadas en la nube. Como parte del acuerdo, los clientes de Oracle pueden integrar la administración de acceso basada en el AD de Azure a través de un modelo de identidad federado, que ofrece un mecanismo unificado para la autenticación y autorización de usuarios y aplicaciones.

 

Modelo de soporte integrado y colaborativo.

Los clientes que ejecutan cargas de trabajo en el entorno de múltiples nubes no necesitan lidiar con dos suscripciones de soporte. Pueden llamar a Oracle o Microsoft para soporte. Microsoft y Oracle han creado conjuntamente un equipo de soporte que está capacitado en los servicios ofrecidos por ambos proveedores de la nube. Con esto, los clientes pueden aprovechar y ampliar sus relaciones y procesos existentes de soporte al cliente de Microsoft Azure y OCI.

 

Fácil migración a la nube.

Ambas empresas simplificaron su modelo de licencias, resultando mas sencillo implementar aplicaciones empresariales de Oracle en Azure con la misma asignación de procesadores que sus implementaciones locales existentes. Oracle E-Business Suite, JD Edwards, Peoplesoft Enterprise, Hyperion y Oracle Retail Applications se pueden migrar a Azure a través de esquemas de licencias simplificados.

 

El mismo modelo se aplica a las productos de Microsoft, incluida la opción de ejecutar el servidor de bases de datos SQL Server en OCI.

 

Elegir modelos de despliegue mixto

Con esta asociación, los clientes pueden ejecutar aplicaciones de en Azure mientras utilizan instancias de base de datos de Oracle que se ejecutan en OCI. Pueden integrar a la perfección aplicaciones a través de transmisión por secuencias, eventos y disparos disponibles exclusivamente en Azure. Los clientes de OCI también pueden aprovechar el poder de los servicios AI, IoT, Blockchain y Edge provistos por Azure. Los clientes de Microsoft pueden aprovechar Oracle RAC, Exadata y la base de datos autónoma implementada en OCI.

 

 

Que se puede esperar en el futuro

Aparte de la conectividad básica y la interoperabilidad, Microsoft y Oracle están buscando ampliar la integración de Azure y OCI, y algunas posibilidades son:

 

  • Integración de Oracle Analytics Cloud y Azure Data Services
  • Integración de Power BI con aplicaciones y bases de datos Oracle
  • Monitoreo integrado entre Azure AppInsights y servicio de monitoreo OCI
  • Gestión de claves integrada entre Azure KMS y OCI KMS
  • Integración de equipos de Microsoft con aplicaciones Oracle.

Oracle Database 18c.png

Oracle 18cXE.jpg

 

A continuación voy a describir los pasos completos para instalar Oracle 18c XE en un equipo con Windows, junto con algunas recomendaciones para simplificar la instalación, evitar errores y poder utilizar las bases de datos facilmente.

 

Validar Requerimientos

La primer tarea antes de iniciar la instalación es validar que el equipo donde deseamos instalar Oracle XE cumpla con los requerimientos mínimos de hardware y software

 

Las plataformas soportadas son:

 

  • Windows 7 x64 - Ediciones Professional, Enterprise y Ultimate
  • Windows 8.1 x64 - Ediciones Pro y Enterprise
  • Windows 10 x64 - Ediciones Pro, Enterprise y Education
  • Windows Server 2012 x64 - Ediciones Standard, Datacenter, Essentials y Foundation
  • Windows Server 2012 R2 x64 - Ediciones Standard, Datacenter, Essentials y Foundation
  • Windows Server 2016 x64 - Ediciones Standard, Datacenter y Essentials

 

En el caso de Windows Server, el mismo no puede ser Domain Controller (esto no está aclarado en la documentación pero si fue reportado en los foros y confirmado por Oracle).Los requerimientos de hardware son:

  • 8,5 Gb de espacio en disco, mas 2 Gb temporales durante la instalación.
  • 2 Gb de RAM.

A nivel permisos, el usuario que ejecuta la instalación debe ser miembro del grupo Administradores. En caso de realizar la instalación con un usuario de Dominio, asegurarse que el equipo este conectado a la red antes de comenzar la instalación.

 

Descargar Oracle 18c XE para Windows

Descargar el software desde la página de descargas de Oracle XE18c.Una vez descargado el archivo Zip, descomprimir el mismo en una carpeta temporal desde donde se realizará la instalación, la cual puede ser eliminada una vez finalizado el proceso de instalación.

 

Instalar Oracle 18c XE

  • Asegurarse que la variable de  entorno "ORACLE_HOME" no esté definida en el equipo.
  • Ubicarse en la carpeta donde se descomprinió el archivo Zip, hacer click derecho sobre el archivo "Setup.exe" y ejecutar como administrador.

 

01 - Ejecutar como Administrador.jpg

 

  • Esperar a la instalación del paquete de pre-requisitos. Una vez finalizado este paso, el instalador presentará la pantalla de bienvenida, en la que se debe presionar "Siguiente":

 

02 - Bienvenida.jpg

 

  • A continuación se debe aceptar el Acuerdo de Licencia y presionar "Siguiente":

 

03 - Licencia.jpg

 

  • El paso siguiente requiere seleccionar una carpeta de instalación. Dentro de la misma se generan las siguientes carpetas:
    • Admin (Archivos de dump, pfile y Oracle Wallet)
    • Audit (Logs de Auditoria)
    • cfgtoollogs (Logs de herramientas de configuración como DBCA o NetCA)
    • checkpoints
    • dbhomeXE (Oracle Home de la version  XE)
    • diag (logs y archivos de diagnostico)
    • oradata (archivos de datos de la CDB y las PDB)

 

04 - Destino.jpg

 

  • A continuación se debe ingresar la contraseña para los usuarios SYS, SYSTEM y PDBADMIN y presionar "Siguiente":

 

05 - Password.jpg

 

  • La siguiente pantalla muestra un resumen de donde se va a instalar Oracle 18c XE. Al presionar "Instalar" se da comienzo a la instalación:

 

06 - Confirmacion.jpg

 

  • El proceso de instalación, creación de la base de datos CDB y de una base de datos PDB demanda unos 20 minutos:

 

07 - Instalación.jpg

 

  • Una vez finalizado el mismo, la pantalla final nos muestra información sobre la forma de conectarse tanto a la CDB como a la PDB y de la dirección web donde se encuentra en ejecución el Enterprise Manager Express de la instancia:

 

08 - Resumen.jpg

 

Servicios

Una vez instalada Oracle 18c XE, los siguientes servicios deberían estar disponibles:

 

09 - Servicios.jpg

 

Inicio automático de bases PDB

Al finalizar la instalación, tanto la base de datos CDB como la PDB se encuentran abiertas y disponibles para ser utilizadas. Pero al reiniciar el servidor y volver a iniciarse el servicio, solo la base de datos contenedor (CDB) se iniciará en forma automática.

 

Para configurar las bases de datos PDB (como ser la base XEPDB1 creada durante la instalación) para que inicien de forma automática se debe realizar lo siguiente:

 

  1. Conectarse a la base de datos contenedor (CDB).
  2. Ejecutar las siguientes sentencias

 

SQL> ALTER PLUGGABLE DATABASE ALL OPEN; SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

 

Nota: Si se crea alguna base de datos PDB con posterioridad, se debe repetir estos pasos.

 

 

Conexión desde SQL Developer

 

Para establecer una conexión a la base de datos XE recientemente instalada, deberemos utilizar los datos informados en el ultimo paso del proceso de instalación:

 

  • Nombre de Conexión: Usar un nombre que nos permita identificar a que base de datos y con que usuario nos estamos conectando
  • Usuario: Utilizar inicialmente el usuario "System"
  • Contraseña: Utilizar la contraseña provista en el punto 4 del proceso de instalación.
  • Nombre del Host: Si la conexión la estamos haciendo desde el miso equipo donde realizamos la instalación, alcanza con utilizar "localhost". De estar conectándonos desde otro equipo utilizar en nombre o dirección IP apropiada
  • Puerto: utilizar el puerto informado en el ultimo paso del proceso de instalación (se lo identifica por el número en la cadena de conexión, como ser "localhost:1521"). El puerto donde escucha el Listener por defecto es el 1521, pero podría variar si hay mas de un Listener ya instalado.
  • Seleccionar "Nombre del Servicio" e ingresar "XE" para la base de datos contenedor (CDB) o "XEPDB1" para la PDB.

 

A continuación, al presionar "Probar" debe aparecer un mensaje "Estado: Correcto" en la sección inferior izquierda. Presionar luego "Guardar" para guardar la conexión.

 

10 - SQL Developer.jpg

 

Como vimos en el post anterior, Auto Indexing es un sistema experto que emula el trabajo que un especialista en performance realiza normalmente en una base de datos, supervisando la carga de trabajo en la base de datos y determinando si hay algún índice que pueda ayudar a mejorarla. Esta nueva característica es una evolución de los Advisors disponibles desde Oracl 10g, y tal como ocurría con los mismos, hay diversas vistas del diccionario de datos que permiten conocer como está configurada esta característica y que índicas han sido creados por la misma.

 

Vistas de Configuración

Una vez habilitada la opción, podemos ver los detalles de configuracion de la misma consultando la vista "DBA_AUTO_INDEX_CONFIG"

 


SELECT parameter_name, parameter_value FROM dba_auto_index_config;

PARAMETER_NAME                    PARAMETER_VALUE

AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                   IMPLEMENT
AUTO_INDEX_REPORT_RETENTION       31
AUTO_INDEX_RETENTION_FOR_AUTO     373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET           50

 

Los diferentes parámetros tienen el siguiente uso

 

  • AUTO_INDEX_MODE: Indica si la funcionalidad de encuentra deshabilitada , habilitada en forma completa (IMPLEMENT) o sólo en forma informativa (REPORT ONLY).
  • AUTO_INDEX_SCHEMA: Indica el o los esquemas para los cuales se habilita o deshabilita la funcionalidad.
  • AUTO_INDEX_REPORT_RETENTION: Cantidad de días para los cuales los logs de actividad de Auto Indexado se mantienen en la base de datos. El valor por defecto es 31 días.
  • AUTO_INDEX_RETENTION_FOR_AUTO: Cantidad de días que los indices creados en forma automática y que no son usados son mantenidos en la base de datos, antes de ser eliminados. El valor por defecto es 373 días.
  • AUTO_INDEX_RETENTION_FOR_MANUAL: Cantidad de días que los indices creados en forma manual y que no son usados son mantenidos en la base de datos, antes de ser eliminados. El valor por defecto es NULL, lo que significa que los indices creados manualmente no son eliminados aunque no sean utilizados.
  • AUTO_INDEX_DEFAULT_TABLESPACE: Tablespace donde se crearán los indices creados en forma automática. Cuando el valor es NULL (opción por defecto) se utiliza el tablespace por defecto.
  • AUTO_INDEX_SPACE_BUDGET: Cuando se usa el tablespace por defecto, porcentaje máximo del mismo que puede ser utilizado por indices creados en forma automática.

 

Vistas de Log de Actividad

DBA_AUTO_INDEX_EXECUTIONS

La vista "DBA_AUTO_INDEX_EXECUTIONS" posee información sobre todas las ejecuciones del proceso de indexado automático, incluyendo fecha de inicio/fin, status y si se encontró algún error.

SELECT * FROM dba_auto_index_executions ORDER BY execution_start;

DBA_AUTO_INDEX_STATISTICS

Esta vista posee un resumen de la actividad realizada por los procesos de Indexado Automático (espacio utilizado, indices evaluados, indices creados, porcentaje de mejora, etc.).

SELECT * FROM dba_auto_index_statistics WHERE value > 0;

DBA_AUTO_INDEX_IND_ACTIONS

Esta vista muestra los comandos ejecutados para crear índices, teniendo en cuenta los distintos pasos del proceso

  • Creación del indice en estado UNUSABLE para evaluar el plan de ejecución.
  • Reconstrucción del indice (online pero invisible) para evaluar el impacto en las sentencias deseadas.
  • Convertir el índice a VISIBLE para que sea utilizado, si el mismo es aceptado por el proceso.
SELECT * FROM dba_auto_index_ind_actions ORDER BY table_name, action_id;

DBA_AUTO_INDEX_SQL_ACTIONS

Esta vista posee información sobre las sentencias SQL ejecutadas para validad y verificar los indices creados en forma automática
SELECT * FROM dba_auto_index_sql_actions;

Procedimiento "REPORT_ACTIVITY"

Resulta mas sencillo consultar un reporte completo mediante el procedimiento "REPORT_ACTIVITY" del paquete "DBMS_AUTO_INDEX" que consultar cada una de las vistas en forma individual

 

-------------------------------------------------------------------------------- 
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 29-AUG-2018 12.20.40
Activity end : 30-AUG-2018 12.20.40
Executions completed : 13
Executions interrupted : 3
Executions with fatal error : 1
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 53
Indexes created (visible / invisible) : 12 (12 / 0)
Space used (visible / invisible) : 3.48 MB (3.48 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 16
SQL statements improved (improvement factor) : 16 (3x)
SQL statements disallowed from auto indexes : 0
Overall improvement factor : 3x
-------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
Unused indexes (visible / invisible) : 10 (8 / 2)
Space used (visible / invisible) : 100 MB (76 MB / 24 MB)
Unusable indexes : 0
-------------------------------------------------------------------------------
18 Sample Report
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. |

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:*: invisible
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
--------------------------------------------------------------------------------
| OPT | T_10K_CP1 | SYS_AI_3cpm0ahgt469g | ROWID_UNIQUE        | B-TREE | NONE |
| OPT | T_10K_CP1 | SYS_AI_3rk4h2m9d49b5 | CHAR_UNIQUE         | B-TREE | NONE |
| OPT | T_10K_CP1 | SYS_AI_5cq2h6jhmznc9 | DATE_UNIQUE         | B-TREE | NONE |
| OPT | T_10K_CP1 | SYS_AI_6vg5wr5nwcqxs | THOUSAND            | B-TREE | NONE |
| OPT | T_10K_CP1 | SYS_AI_agnvzczmz4z0a | TEN,UNIQUE1,UNIQUE2 | B-TREE | NONE |
| OPT | T_10K_CP1 | SYS_AI_bcms9qy98nq1c | VCHAR_UNIQUE        | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_0urcv8chmxu20 | VCHAR_UNIQUE        | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_2pvk34mqdh7pa | TEN,UNIQUE1,UNIQUE2 | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_428hqd6qu531y | THOUSAND            | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_5d2cukrm2gju2 | DATE_UNIQUE         | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_97zrtmcmn5tz6 | CHAR_UNIQUE         | B-TREE | NONE |
| OPT | T_5K_CP   | SYS_AI_cn9fsv12paxcb | ROWID_UNIQUE        | B-TREE | NONE |
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Schema Name : OPT
SQL ID : 2vy3tr5kyg88z
SQL Text : select count(*) from t_5k_cp where vchar_unique ='MAN'
Improvement Factor : 2x
PLANS SECTION
-------------------------------------------------------------------------------
Original -----------------------------
Plan Hash Value : 3944640934
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | | | | |
| 2 | TABLE ACCESS FULL | T_5K_CP | | | | |
---------------------------------------------------------------------
With Auto Indexes
-----------------------------
Plan Hash Value : 2541075899
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT AGGREGATE | | | | | |
| * 2 | INDEX RANGE SCAN | SYS_AI_0urcv8chmxu20 | | | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("VCHAR_UNIQUE"='MAN')

 

 

 

Algunas consideraciones adicionales

 

  • La funcionalidad de Automatic Indexing consume CPU, memoria y almacenamiento, por lo que:
    • La tarea esta limitada a sólo una CPU por el Resource Manager Plan
    • Se puede elegir el tablespace donde se almacenan los indices creados en forma automática.
    • Se puede elegir el tablespace temporal donde se construyen los indices creados en forma automática.
  • La funcionalidad de Automatic Indexing  soporta
    • Índices sencillos o concatenados (mas de una columna).
    • Índices basados en funciones.
    • Compresión avanzada en nivel LOW.



Links Utiles

 

Oracle 19c Database Logo.png

Auto Indexing es un sistema experto que emula el trabajo que un especialista en performance realiza normalmente en una base de datos. El mismo supervisa la carga de trabajo en la base de datos y determina si hay algún índice que pueda ayudar a mejorarla.

 

A diferencia de los asesores de índices ya existentes en versiones anteriores de Oracle, el indexado automática no se limita a recomendaciones sino que valida las mismas (construye los índices y prueba las sentencias que se verían afectadas), e identifica sentencias que pueden ser perjudicadas por la existencia del nuevo índice, generando un SQL Plan Baseline para evitar que el nuevo índice afecte a la sentencia.

 

Automatic Indexing.png

 

Este es un proceso iterativo, el cual se ejecuta de manera periódica y posee mecanismos de aprendizaje que mejoran el proceso de recomendación de índices. Asimismo, para evitar mantener indices innecesarios, aquellos índices generados por en indexado automático que ya no son útiles son eliminados por el mismo proceso, reduciendo la sobrecarga generada por indices innecesario al ejecutar sentencias de DML sobre las tablas afectadas.

 

La configuración y administración de esta funcionalidad se hace mediante el nuevo paquete DBMS_AUTO_INDEX.

 

Activando o Desactivando Autimatic Indexing

Es posible activar esta nueva funcionalidad de dos formas, ya sea en modo Implement (que genera los indices y los deja visibles) o en modo Reports (donde los índices quedan en modo invisible y no son utilizados por las sentencias):

 

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

 

Para des-habilitar la función, también se usa el paquete DBMS_AUTO_INDEX:

 

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

 

Para ver mas detalles sobre la configuración y mas opciones de esta nueva característica, se puede consultar la documentación oficial.

Oracle 19c Database Logo.png

La versión 19c de la base de datos Oracle incluye una interesante mejora a la función LISTAGG, largamente solicitada y esperado por los desarrolladores.

 

La versión 12.2 ya introdujo algunas mejoras realmente interesantes, las cuales ya compartí en este articulo Oracle 12c R2 - LISTAGG y OVERFLOW.

 

Ahora es posible eliminar los valores repetidos utilizando la opción DISTINCT. Veamos el siguiente ejemplo (tomado del blog de Connor McDonald):

 

SELECT deptno,
       listagg(job,',') WITHIN GROUP ( ORDER BY job) AS jobs
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

DEPTNO    JOBS
--------- --------------------------------------------------
10        CLERK,MANAGER,PRESIDENT
20        ANALYST,ANALYST,CLERK,CLERK,MANAGER
30        CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN

 

Ahora es muy sencillo eliminar los valores duplicados simplemente utilizando la opción DISTINCT:

 

SELECT deptno,
       listagg(distinct job,',') WITHIN GROUP ( ORDER BY job) AS jobs
FROM scott.emp
GROUP BY deptno
ORDER BY 1;

DEPTNO    JOBS
--------- --------------------------------------------------
10        CLERK,MANAGER,PRESIDENT
20        ANALYST,CLERK,MANAGER
30        CLERK,MANAGER,SALESMAN

 

Si quieren probarlo ustedes mismo, nada mejor que los ejemplos disponibles en Oracle Live SQL (entorno de base de datos gratuito en la nube provisto por Oracle).

 

También es útil revisar la sintaxis completa de la función LISTAGG en la documentación de Oracle 19c.

Oracle Database 18c.png

Desde Oracle 8i es posible crear Tablas temporales en Oracle, aunque la definición de Temporal en esos casos es relativa. Si bien el "contenido" (los datos) de dichas tablas es temporal, su definición no lo es.

 

Las tablas temporales tal cual las conocemos, llamadas Global Temporary Table, son objectos de diccionario de datos, por lo que su definición es almacenada en el mismo y persiste, no así con el contenido de las mismas.

 

¿Que son las tablas temporales privadas?

A diferencia de las tablas temporales globales, las tablas temporales privadas no se almacenan en el diccionario de datos, su definición reside en memoria en la PGA (Process Global Area, el area de memoria destinada a mantener valores de cada sesión en Oracle) por lo que son eliminadas en forma automática ya sea al hacer commit (opción por defecto) o bien al finalizar la sesión que la crea.

 

Al ser objectos que existen sólo en memoria y no se encuentran almacenados en el Diccionario de Datos, no pueden ser utilizados en forma directa en objectos PL/SQL (procedimientos, funciones) ni en vistas, ya que su definición es "volátil". Si necesitamos utilizar una tabla temporal privada dentro de un procedimiento o función, deberemos crear la misma y utilizarla mediante código dinámico.

 

¿Como se crea una tabla temporal privada?

La sintaxis para crear una tabla temporal privada es la siguiente:

 

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction 
(time_id      DATE,
amount_sold  NUMBER(10,2))
[ON COMMIT [DELETE | PRESERVE] ROWS]
ON COMMIT [DROP|PRESERVE] DEFINITION;

 

La primer cosa a considerar es el nombre de las tablas temporales privadas. Las mismas deben comenzar con un prefijo específico, el cual se define en el parámetro "PRIVATE_TEMP_TABLE_PREFIX", y cuyo valor por defecto es "ORA$PTT_".

 

Por otro lado, al declarar una tabla temporal privada deberemos especificar la "duración" de la misma, con la clausula "ON COMMIT [DROP|PRESERVE] DEFINITION". Por defecto, la tabla es eliminada (tanto su definición como su contenido) cuando se ejecuta un COMMIT en la sesión que la creó. Pero se puede preservar la existencia de la misma incluyendo la clausula "ON COMMIT PRESERVE DEFINITION".

 

La opción "ON COMMIT [DELETE|PRESERVE] ROWS" se mantiene igual que en la sintaxis de las tablas temporales globales.

 

Restricciones de las Tablas Temporales Privadas

De por si, las tablas temporales poseen un conjunto de restricciones que detallamos a continuación.

 

  • Las tablas temporales no puede ser particionadas, ni de tipo Cluster o Index Organized.
  • No se pueden definir claves foráneas sobre las tablas temporales.
  • Las tablas temporales no pueden contener columnas de tipo tabla anidada.
  • No se puede realizar UPDATE, DELETE ni MERGE en paralelo en las tablas temporales.
  • La única parte de la clausula de atributos de segmento que se puede especificar para las tablas temporales es la option TABLESPACE, la cual debe referenciar a un único tablespace de tipo temporal.
  • Las tablas temporales no pueden ser utilizadas en transacciones distribuidas.
  • Las tablas temporales no pueden contener columnas de tipo INVISIBLE.

 

 

Cuando trabajamos con tablas temporales privadas debemos tener en cuenta las siguientes restricciones adicionales:

 

  • No se puede crear índices, vistas materializadas con tablas temporales privadas.
  • No se puede asignar un valor por defecto a las columnas de una tabla temporal privada.
  • No se puede hacer referencia a tablas temporales privadas en objetos permanentes, como ser vistas o triggers.
  • Las tablas temporales privadas no son visibles a trabes de database links.
  • Al no almacenarse su definición en el diccionario de datos, no es factible ver las mismas en las vistas usuales del diccionario de datos, como ser "USER_TABLES". Para poder conocer las tablas temporales privadas existentes en un momento dado se pueden consultar estas dos nuevas vistas:

 

¿Cuando usar tablas temporales privadas?

Un ejemplo de cuando utilizar una tabla temporal privada es en una base de datos en modo READ ONLY, ya que la misma no es almacenada en el diccionario de datos.

Oracle Database 18c.png

Tablas Externas "Inline"

¿Que son las tablas externas?

Las tablas externas existen en Oracle desde la versión 9i, y permiten acceder a información contenida en archivos de texto como si fueran tablas almacenadas dentro de la base de datos.

 

Las mismas fueron adquiriendo nuevas características con el paso del tiempo. Inicialmente, eran de solo lectura, pero desde Oracle 10g se puede realizar modificaciones a las mismas.

 

En Oracle 12.2 es posible “particionar” el contenido de las tablas externas, con lo cual podemos ver varios archivos como si fueran una única tabla, y también es posible modificar algunos parámetros (como ser el directorio por defecto o los nombres de archivo) directamente al momento de escribir una consulta, sin necesidad de modificar la definición de la tabla en forma permanente.

 

¿Qué es lo nuevo en Tablas Externas en Oracle 18c?

Oracle 18c introduce la opción de Tablas Externas "INLINE", lo que traducido a conceptos básicos significa que la definición de la tabla externa se puede hacer directamente en la sentencia que la va a utilizar, sin necesidad de que la misma exista previamente.

 

SELECT Pais, Provincia, Sucursal, MontoMensual 
FROM   EXTERNAL (
                    ( Pais    VARCHAR2(30),
                      Provincia VARCHAR2(30),
                      Sucursal  VARCHAR2(30),
                      MontoMensual NUMBER(18,4) )
                    TYPE oracle_loader
                    DEFAULT DIRECTORY datos_externos
                    ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
                                        BADFILE datos_externos
                                        LOGFILE datos_externos:'inline_ext_tab_%a_%p.log'
                                        DISCARDFILE datos_externos
                                        FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                                        MISSING FIELD VALUES ARE NULL ( Pais, Provincia, Sucursal, MontoMensual )
               ) LOCATION ('ventas_argentina.txt', 'ventas_chile.txt', 'ventas_uruguay.txt')
               REJECT LIMIT UNLIMITED ) inline_ext_tab
ORDER BY Pais, Provincia, Sucursal;

 

Si bien el código no queda muy elegante, permite acceder a datos externos (que sean accesibles para la base de datos) sin necesidad de crear un objeto de tipo tabla, lo cual puede ser muy útil en bases de datos o esquemas de solo lectura.

 

La sintraxis completa puede ser consultada aqui y aqui.

Oracle Database 18c.png

ALTER SYSTEM CANCEL

A partir de la version 18c, es posible cancelar una sentencia ejecutándose en la base de datos sin necesidad de terminar (matar) a la sesión que la está ejecutando.

 

Para ello, Oracle 18c introduce una nueva opción "CANCEL" en la sentencia "ALTER SYSTEM", la cual tiene la siguiente sintaxis:

 

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL, [, @INST_ID] [, SQL_ID]’;

 

Los valores de "SID" y "SERIAL" son idénticos a los que se utilizan históricamente para terminar una sesión utilizando "ALTER SYSTEM KILL". La opción "CANCEL" nos permite indicar dos parámetros adicionales:

 

  • @INST_ID: Indica la instancia donde la sesión se está ejecutando, y va siempre precedida del símbolo @. Si no se especifica un valor, Oracle asume que la sentencia a cancelar se está ejecutando en la misma instancia donde se ejecuta la sentencia "ALTER SYSTEM CANCEL".
  • SQL_ID: Indica el identificador de la sentencia SQL que deseamos cancelar. Si no se especifica un valor, la sentencia que se esté ejecutando al momento de hacer el "ALTER SYSTEM CANCEL" será cancelada.

 

Para obtener los datos de las sesiones que están activas, se puede ejecutar la siguiente sentencia:

 

SELECT s.sid,
       s.serial#,
       s.sql_id,
       s.username,
       s.program,
       s.status
FROM v$session s
WHERE s.status = 'ACTIVE';

 

 

 

En entornos RAC, se debe usar la vista global "gv$session" e incluir la columna "inst_id" en los resultados:
SELECT  s.sid,
        s.serial#,
        s.inst_id,
        s.sql_id,
        s.username,
        s.program
FROM   gv$session s
WHERE s.status = 'ACTIVE';

Se puede consultar la documentación oficial aquí.

Oracle Database 18c.png

 

Instalar en un ORACLE_HOME de sólo lectura

La versión 18c de Oracle permite realizar la instalación del motor de bases de datos en un ORACLE_HOME de sólo lectura, mientras que los archivos de configuración y logs residen en un directorio independiente fuera del mismo. Esta nueva característica permite utilizar a un ORACLE_HOME instalado en modo sólo lectura como una imagen para ser distribuida entre múltiples servidores, agilizando el proceso de actualización (patches & upgrades) y simplificando la provisión de entornos, al separar la instalación de la configuración.

 

¿Qué es ORACLE_BASE_HOME?

Es la ubicación donde se encuentran los archivos de logs y de instancia de un "ORACLE_HOME". En una instalación normal, la misma coincide con el "ORACLE_HOME", pero en una instalación de modo sólo lectura esta ubicación se encuentra ahora en "ORACLE_BASE/homes/HOME_NAME".


Por ejemplo, los directoriosn "network/admin", "network/trace", y "network/log" se encuentran dentro del directorio "ORACLE_BASE_HOME".

 

Para saber donde se encuentra ubicado "ORACLE_BASE_HOME", se debe realizar lo siguiente:

 

$ setenv ORACLE_HOME /u01/app/oracle/product/18.0.0/dbhome_1 
$ cd $ORACLE_HOME/bin
$ ./orabasehome
$ u01/app/oracle/homes/OraDB18Home1

 

¿Qué es ORACLE_BASE_CONFIG?

Es la ubicación donde se encuentran los archivos de configuraciónde un "ORACLE_HOME". En una instalación normal, la misma coincide con el "ORACLE_HOME", pero en una instalación de modo sólo lectura esta ubicación se encuentra ahora en "ORACLE_BASE".

 


Por ejemplo, "ORACLE_BASE_CONFIG/dbs" contiene los archivos de configuracion para el "ORACLE_HOME". Cada archivo en el directorio "dbs" directory contiene "$ORACLE_SID" como parte del nombre de forma tal que pueda ser compartido por distintos "ORACLE_SIDs".

 

 

Para saber donde se encuentra ubicado "ORACLE_BASE_CONFIG", se debe realizar lo siguiente:

$ setenv ORACLE_HOME /u01/app/oracle/product/18.0.0/dbhome_1 
$ cd $ORACLE_HOME/bin
$ ./
orabaseconfig
$ u01/app/oracle/

 

¿Qué es orabasetab?

Es un archivo utilizado para definir donde se ubican los directorios principales de cada "$ORACLE_HOME": "ORACLE_BASE", "ORACLE_BASE_HOME" y "ORACLE_BASE_CONFIG".

 

El archivo se encuentra en "ORACLE_HOME/install/orabasetab" y puede ser utilizado para determinar si el "ORACLE_HOME" es de sólo lectura. También define los valores de "ORACLE_BASE" y "HOME_NAME" (nombre interno del "ORACLE_HOME")

 

Para consultar su contenido, se puede realizar:

 

$ cd /u01/app/oracle/product/18.0.0/dbhome_1/install 
$ cat orabasetab
$ u01/app/oracle/product/18.0.0/dbhome_1:/u01/app/oracle:OraDB18Home1:Y:

La última "Y" indica que el Oracle Home es de sólo lectura.

Oracle Database 18c.png

 

Instalar en Linux Utilizando RPM

La instalación de base de datos basada en RPM (RDI) permite, en entornos Linux, instalar el software de base de datos Oracle utilizando RPM.

 

Con el comando rpm-ivh, una instalación de base de datos basada en RPM realiza las validaciones de pre-instalación, extrae el software empaquetado, reasigna el propietario del software extraído al usuario y grupos pre-configurados, actualiza el inventario de Oracle y ejecuta todas las operaciones de "root" requeridas para completar la instalación del software Oracle.

 

 

La documentación oficial esta disponible:

Oracle 18c - Nuevas Características.

Oracle 18c - Guía de Instalación para Linux

 

 

Como Descargar Oracle 18c

Para descargar Oracle 18c y probarla en Linux, es posible utilizar los binarios de Exadata, los cuales se encuentran disponibles en el sitio de eDelivery de Oracle

 

Primero se debe seleccionar "Oracle Database (Exadata Only)" en la búsqueda de productos:

 

2018-03-07 eDelivery1.png

 

Luego, seleccionar la plataforma (recordando que Exadata es Linux):

 

2018-03-07 eDelivery2.png

 

Luego de aceptar la licencia, se puede descargar el archivo en forma directa o utilizando un gestor de descargas:

 

2018-03-07 eDelivery3.png

Para quienes estamos acostumbrados a trabajar con Oracle, nos resulta normal que la base de datos considere a los caracteres "a", "á", "A" y "Á" como distintos, ya que es conocido que Oracle por defecto es una base de datos donde los valores de tipo carácter son almacenados, comparados y ordenados en forma binaria, teniendo en cuenta el valor ASCII de cada uno de ellos.

 

Pero este comportamiento por defecto puede resultar molesto en situaciones donde debemos realizar búsquedas sobre información ingresada en forma manual, donde la misma puede tener diferencias en Mayúsculas/Minúsculas o en la acentuación. Por ejemplo, si buscamos clientes utilizando esta sintaxis

 

WHERE Apellido LIKE '%García%'

 

la sentencia no devolverá aquellos clientes que fueron ingresados en nuestra base de datos como "GARCIA", "Garcia" o "garcía". Este comportamiento puede observarse en operaciones lógicas (ya sea por igualdad, rango o similitud), en agregaciones (GROUP BY) y en el ordenamiento de los datos devueltos por la consulta (ORDER BY).

 

Es por ello que en muchos sistemas se optaba por alguno de estos dos enfoques al momento de manejar este tipo de información:

 

1) Se "normalizaba" la información ingresada por los usuarios, convirtiendo todo a mayúsculas o minúsculas, y reemplazando los acentos. De esta forma, los siguientes datos se almacenaban así:

 

Garcia --> GARCIA

García --> GARCIA

garcia --> GARCIA

2) Se mantenía la información en la base de datos como la ingresaba el usuario, pero se creaba un indice basado en funciones que indexaba por los valores convertidos a mayúsculas/minúsculas y sin acentos.

 

En ambos casos, al momento de realizar una búsqueda se requiere que el texto a buscar se encuentre en el mismo "formato" (mayúsculas o minúsculas, acentos eliminados) que como se ha almacenado en la tabla o en el índice basado en funciones.

 

¿Qué es "COLLATION" y para que sirve?

 

2017-11-27 Collation.jpg

Collation es un nuevo atributo de las columnas de tipo caracter, el cual define como se interpretan las distintas variaciones (por mayúscula/minúscula, acentuación) de las letras del abecedario.

 

Existen dos tipos básicos de COLLATION:

 

Binario: Las comparaciones y el ordenamiento de los datos se basan en el valor numérico de cada caracter de una cadena.

Lingüístico: El ordenamiento y las comparaciones se basan en secuencias alfabéticas de los caracteres, independiente del valor numérico de los mismos.

Los distintos tipos de COLLATION linguisticos pueden consultarse en el sitio de Oracle. Las distintas COLLATION poseen tres sufijos que definen el comportamiento de las mismas al momento de realizar comparaciones y ordenamiento:

 

  • "_CS": Sensible a acentos y mayúscula/minúscula. Es la opcion por defecto, y si no se especifica el sufijo se asume el mismo.
  • "_CI": Case Insensitive, significa que no se tiene en cuenta diferencias entre mayúsculas y minúsculas, pero si a los acentos.
  • "_AI": No tiene en cuenta mayúsculas y minúsculas ni acentos.

 

Si no se especifica una COLLATION en particular, Oracle utiliza un pseudo-valor "USING_NLS_COMP", el cual significa que se tienen en cuenta los valores de los parámetros "NLS_SORT" y "NLS_COMP" para determinar la COLLATION a utilizar.

 

¿Como se define la COLLATION de una columna?

Al momento de crear una tabla, cada columna puede incluir la definición del COLLATION que se va a utilizar. Asimismo, se puede definir una COLLATION por defecto a nivel de tabla, esquema, sesión y base de datos. A continuación veremos algunos ejemplos:

 

A nivel Columna

 

CREATE TABLE t_Tabla1 (

  ID_Tabla1          NUMBER,

  Texto              VARCHAR2(20 CHAR),

  TextoBinarioCI     VARCHAR2(20 CHAR) COLLATE BINARY_CI,

  CONSTRAINT pk_Tabla1 PRIMARY KEY (ID_Tabla1)

);

 

Para probar como se comporta la columna "TextoBinarioCI", vamos a insertar algunos datos y ejecutar algunas consultas:

 

INSERT INTO t_Tabla1

    SELECT 1, 'Opción', 'Opción' FROM DUAL UNION ALL

    SELECT 2, 'Opcion', 'Opcion' FROM DUAL UNION ALL

    SELECT 3, 'opción', 'opción' FROM DUAL UNION ALL

    SELECT 4, 'opcion', 'opcion' FROM DUAL;

 

4 rows inserted.

 

SELECT * FROM t_Tabla1 t

WHERE t.Texto = 'opcion';

 

ID_TABLA1 TEXTO                TEXTOBINARIOCI

---------- -------------------- --------------------

         4 opcion               opcion

SELECT * FROM t_Tabla1 t

WHERE t.TextoBinarioCI = 'opcion';

 

ID_TABLA1 TEXTO                TEXTOBINARIOCI

---------- -------------------- --------------------

         2 Opcion               Opcion

         4 opcion               opcion

 

A nivel Tabla

Podemos crear una tabla con una COLLATION por defecto, o modificar una tabla existente. Hay que tener en cuenta que modificar la COLLATION por defecto de una tabla existente, no modifica las columnas existentes, sino que el nuevo valor se aplica a las columnas que se creen en la misma posteriormente.

 

ALTER TABLE t_Tabla1 DEFAULT COLLATION BINARY_AI;
Table T_TABLA1 altered.

ALTER TABLE t_Tabla1 ADD (

  TextoBinarioAI  VARCHAR2(20 CHAR) );

 

Table T_TABLA1 altered.

 

A continuación, actualizamos los datos existente en la tabla, repitiendo los valores de las dos columnas ya existentes en la nueva columna. Y luego realizamos una búsqueda similar a la anterior, pero sobre la nueva columna.

 

UPDATE t_Tabla1

SET TextoBinarioAI = CASE ID_Tabla1 WHEN 1 THEN 'Opción'

                                    WHEN 2 THEN 'Opcion'

                                    WHEN 3 THEN 'opción'

                                    WHEN 4 THEN 'opcion'

                     END;

 

4 rows updated.

 

SELECT * FROM t_Tabla1 t

WHERE t.TextoBinarioAI = 'opcion';

 

ID_TABLA1 TEXTO                TEXTOBINARIO         TEXTOBINARIOAI

---------- -------------------- -------------------- --------------------

         1 Opción               Opción               Opción

         2 Opcion               Opcion               Opcion

         3 opción               opción               opción

         4 opcion               opcion               opcion

 

Podemos ver que al crear la nueva columna, como la tabla había sido configurada con la opción "DEFAULT COLLATION BINARY_AI", la misma no distingue entre mayúsculas y minúsculas ni acentos al momento de realizar comparaciones.

 

A nivel Esquema

Para definir una COLLATION por defecto a nivel de esquema, se utiliza la clausula "DEFAULT COLLATION {COLLATION}" al momento de crear un usuario, o podemos modificarlo posteriormente:

 

CREATE USER usr1 IDENTIFIED BY pwd1

  DEFAULT TABLESPACE users

  DEFAULT COLLATION BINARY_CI;

 

ALTER USER usr2 DEFAULT COLLATION BINARY_AI;

 

Es importante recordar que estos cambios afectan a las columnas creadas con posterioridad, no modificando las columnas ya existentes.

 

A nivel Sesión

La forma mas sencilla es utilizando una sentencia "ALTER SESSION SET DEFAULT_COLLATION", como se muestra a continuación:

 

ALTER SESSION SET DEFAULT_COLLATION=SPANISH_CI;

 

También es posible modificar la COLLATION con la que se crearan las nuevas columnas en forma implícita, al configurar los valores de "NLS_SORT" y "NLS_COMP" y luego crear columnas sin explicitar ninguna COLLATION (ni haber configurado una COLLATION por defecto para la tabla, esquema o sesión).

 

ALTER SESSION SET NLS_SORT=SPANISH_CI;

ALTER SESSION SET NLS_COMP=LINGUISTIC;

 

CREATE TABLE t_Tabla2 (

  Texto     VARCHAR2(10)

);

 

Para ver la COLLATION con la que fue creada una columna, podemos utilizar la siguiente consulta:

 

SELECT column_id, column_name, collation

FROM   user_tab_columns

WHERE  table_name = 'T_TABLA2'

 

COLUMN_ID COLUMN_NAME                 COLLATION

---------- --------------------------- --------------------------------

         1 TEXTO                       USING_NLS_COMP

 

A nivel Base de Datos

Para cambiar la COLLATION por defecto de toda una base de datos, se puede hacer el cambio a los parámetros "NLS_SORT" y "NLS_COMP" a nivel de la CDB, (y el mismo impacta esta base de datos y a todas las PDB conectadas a la misma) o en cada PDB.

 

ALTER SYSTEM SET NLS_SORT=BINARY_CI SCOPE=SPFILE;

ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

 

A nivel de Sentencia

Podemos utilizar la clausula "COLLATE {COLLATION}" al momento de hacer agrupaciones, ordenamientos o comparaciones, como podemos ver en los siguientes ejemplos:

 

SELECT ID_Tabla1, Texto

FROM t_Tabla1

WHERE Texto COLLATE SPANISH_CI LIKE 'O%o%';

 

ID_TABLA1 TEXTO                                          

---------- --------------------------------------------------

         2 Opcion                                         

         4 opcion                                         

 

SELECT ID_Tabla1, Texto

FROM t_Tabla1

WHERE Texto COLLATE SPANISH_AI LIKE 'O%o%';

 

ID_TABLA1 TEXTO                                          

---------- --------------------------------------------------

         1 Opción                                         

         2 Opcion                                         

         3 opción                                         

         4 opcion    

 

SELECT TextoBinarioAI COLLATE SPANISH_CI, COUNT(*)

FROM t_Tabla1

GROUP BY TextoBinarioAI COLLATE SPANISH_CI;

 

TEXTOBINARIOAICOLLAT   COUNT(*)

-------------------- ----------

Opcion                        2

Opción                        2

 

SELECT TextoBinarioAI COLLATE SPANISH_AI, COUNT(*)

FROM t_Tabla1

GROUP BY TextoBinarioAI COLLATE SPANISH_AI;

 

TEXTOBINARIOAICOLLAT   COUNT(*)

-------------------- ----------

Opción                        4

 

SELECT ID_Tabla1, TextoBinarioAI

FROM t_Tabla1

ORDER BY TextoBinarioAI;

 

ID_TABLA1 TEXTOBINARIOAI   

---------- --------------------

         1 Opción           

         4 opcion           

         3 opción           

         2 Opcion           

 

SELECT ID_Tabla1, TextoBinarioAI

FROM t_Tabla1

ORDER BY TextoBinarioAI COLLATE BINARY;

 

ID_TABLA1 TEXTOBINARIOAI   

---------- --------------------

         2 Opcion           

         1 Opción           

         4 opcion           

         3 opción         

¿Como validar si un dato puede ser convertido?

Cuando se deben realizar conversiones de datos, es frecuente encontrar valores que no pueden ser convertidos al tipo de datos deseado.

 

Tomemos el siguiente ejemplo (el texto en azul lo utilizaremos en todos los ejemplos del presente artículo):

 

WITH vDatos (ID, Fecha) AS

( SELECT 1, '20170101' FROM DUAL

  UNION ALL

  SELECT 2, '20171501' FROM DUAL

  UNION ALL

  SELECT 3, '20170115' FROM DUAL

  UNION ALL

  SELECT 4, '01-apr-2017' FROM DUAL

  UNION ALL

  SELECT 5, '01-abr-2017' FROM DUAL

  UNION ALL

  SELECT 6, '01/08/17' FROM DUAL

  UNION ALL

  SELECT 7, '30-Feb-17' FROM DUAL

)

SELECT ID, Fecha, TO_DATE(Fecha)

FROM vDatos v;

 

Según cómo tengamos configurados los parámetros NLS_DATE_FORMAT y NLS_DATE_LANGUAGE, al ejecutar la consulta anterior nos encontraremos con alguno de estos mensajes de error, los cuales impiden la ejecución de la consulta:

 

ORA-01861: literal does not match format string

ORA-01843: not a valid month

ORA-01858: a non-numeric character was found where a numeric was expected

ORA-01839: date not valid for month specified

 

Es por ello que es normal que quienes se han encontrado repetidamente con este problema, hayan creado funciones del estilo "es_una_fecha()" o "es_un_numero()" para poder identificar los datos que pueden ser convertidos, evitando los errores que cancelan la ejecución de la consulta.

 

En Oracle 12c Release 2, se introduce una nueva función "VALIDATE_CONVERSION()" que recibe como entrada un dato y un tipo de datos al que deseamos convertir el dato original, y la función nos devuelve 1 cuando es posible realizar la conversión y 0 cuando no es posible.

 

Ejemplo (utilizando la clausula WITH del ejemplo anterior)

 

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha)

FROM vDatos v

WHERE VALIDATE_CONVERSION(v.Fecha AS DATE) = 1;

 

2017-10-18 Converiones 01.png

 

También es posible (y recomendado) utilizar un formato específico para probar si el dato puede ser convertido, como vemos en este ejemplo (utilizando la clausula WITH del ejemplo anterior)

 

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha,'YYYYMMDD')

FROM vDatos v

WHERE VALIDATE_CONVERSION(v.Fecha AS DATE, 'YYYYMMDD') = 1;

 

2017-10-18 Converiones 02.png

 

De esa forma, podemos identificar aquellos registros que pueden ser convertidos y aquellos que no pueden serlo. Esto resuelve muchos problemas de conversión de datos, permitiendo ignorar los registros que no poseen un dato que puede ser convertido.

 

 

¿Qué pasa cuando necesitamos procesar TODOS los registros, y utilizar algún valor especial cuando no es posible realizar la conversión?

En muchos escenarios, identificar que dato puede ser convertido y cual no puede no ser suficiente. Por eso Oracle 12C Release 2 introduce una nueva opción dentro de las funciones de conversión de datos que permite especificar un valor por defecto cuando no es posible realizar la conversión, en vez de generar un error.

 

 

Esta opcion es "DEFAULT {VALOR|NULL} ON CONVERSION ERROR" y puede ser utilizada tanto en la función "CAST" como en las funciones de conversión tradicionales ("TO_DATE", "TO_NUMBER", etc.).

 

A continuación podemos ver dos ejemplos de como utilizarlos (utilizando la clausula WITH del primer ejemplo para generar los datos)

 

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT '19000101' ON CONVERSION ERROR,'YYYYMMDD')

FROM vDatos v;

2017-10-18 Converiones 03.png

 

SELECT v.ID, v.Fecha, TO_DATE(v.Fecha DEFAULT NULL ON CONVERSION ERROR,'DD/MM/YY')

FROM vDatos v

 

2017-10-18 Converiones 04.png