martes, 31 de marzo de 2020

Capacitaciones y Certificaciones Oracle Cloud Gratuitas!

Oracle University anunció ayer la disponibilidad de más de 50 horas de capacitación en línea y 6 exámenes de certificación. Para aprovecharlas, solo hace falta tener una cuenta de Oracle y seguir los siguientes pasos:


Luego, solo es necesario registrarse en el path de capacitación deseado y completar los pasos del mismo, por ejemplo:



Este anuncio se suma al anuncio de la disponibilidad de material de capacitación de Oracle Cloud y la rebaja en los precios de los exámenes de certificación, los cuales pueden ser consultados aquí.

viernes, 13 de marzo de 2020

Oracle 20c - SQL Macros

Las macros SQL son nuevas en la base de datos 20c, y permiten a los desarrolladores de SQL encapsular ciertos procesamientos complejos dentro de una macro que luego se usará en cualquier lugar dentro de instrucciones SQL.

Existen dos tipos de macros SQL, de tipo ESCALAR y de tipo TABLA

  1. Expresiones ESCALARES utilizadas en SELECT o en clausulas WHERE, HAVING, GROUP BY u ORDER BY
  2. Expresiones de TABLA utilizadas en una cláusula FROM

Las macros de SQL se crean mediante funciones PL/SQL, como veremos en los ejemplos posteriores.

Macros de tipo ESCALAR

Proporcionan una sintaxis más flexible generando código SQL reutilizable y transparente para el optimizador de SQL, lo cual trae las siguientes ventajas:

  • El optimizador de SQL puede transformar el código para una ejecución eficiente.
  • La consulta dentro de la macro puede combinarse con una consulta externa, a diferencia de una función PL/SQL que debe ejecutarse en el contexto de PL/SQL.
  • Sin cambio de contexto entre PL/SQL y SQL.
  • La consulta dentro de la función PL/SQL ahora se ejecuta bajo la misma snapshot que la consulta externa, evitando inconsistencias.
  • Los predicados de la cláusula WHERE que usan macros escalares pueden ser "empujados" para la evaluación de predicados de almacenamiento en Exadata - no es posible con las funciones PL/SQL.

Ejemplo

A continuación vamos a crear una macro mediante la definición de la funcion "fnControlRango", la cual verifica si un valor X pertenece a un rango dado. De ser así, devuelve el valor de X, en caso contrario devuelve el valor mínimo o máximo del rango:

CREATE FUNCTION fnControlRango (minval number, x number, maxval number)
RETURN VARCHAR2 SQL_MACRO(SCALAR)
IS BEGIN
    RETURN ‘least(greatest(x, minval), maxval)’;
END;

Hay tres cosas que pueden llamar la atención
  1. La clausula "SQL_MACRO(SCALAR)" le indica que la función PL/SQL es una Macro, y que es de tipo Escalar.
  2. El tipo de datos devuelto es "VARCHAR2", ya que la función no devuelve el resultado de la operación sino el texto SQL a ejecutar.
  3. Lo que se devuelve con la sentencia "RETURN" es una cadena de caracteres que representa el texto a usar al momento de ejecutar la sentencia que usa la Macro.

Una vez creada la función, la podemos usar en sentencias SQL de la siguiente manera:

SELECT Nombre, 
       Apellido, 
       LimiteCredito, 
       fnControlRango (10000, LimiteCredito, 20000) AS NuevoLimite1,
       fnControlRango (12000, LimiteCredito, 22000) AS NuevoLimite2,
       fnControlRango (15000, LimiteCredito, 25000) AS NuevoLimite3
FROM Clientes;

Suponiendo que tenemos los siguientes datos:


El resultado que veremos es el siguiente:


La ventaja de usar una Macro en vez de una función PL/SQL, radica en que la misma se "traduce" a código SQL y se ejecuta en el contexto SQL, sin necesidad de hacer un context switch con PL/SQL en cada ejecución. La sentencia que le llega al optimizador es la siguiente:

SELECT Nombre, 
       Apellido, 
       LimiteCredito, 
       least(greatest(LimiteCredito, 10000), 20000) AS NuevoLimite1,
       least(greatest(LimiteCredito, 12000), 22000) AS NuevoLimite2,
       least(greatest(LimiteCredito, 15000), 25000) AS NuevoLimite3
FROM Clientes;

El uso de la macro reduce y simplifica el código que tenemos que escribir, facilita la corrección de la misma (ya que alcanza con modificar la función que define la macro y no todos los lugares donde se emplea) sin perjudicar la performance.



Macros de tipo TABLA

Las macros de tipo Tabla permiten generar:

  • Vistas parametrizadas. En las mismas las tablas utilizadas en las consultas están fijas se arreglan dentro de la definición de la macro, y se pasan argumentos para seleccionar filas de esas tablas. El uso común de estas vistas parametrizadas es cuando los argumentos escalares se usan para seleccionar un subconjunto de las filas que luego se agregan

  • Vistas polimórficas; En las mismas, los argumentos representan uno o mas nombres de tabla (también puede tener argumentos adicionales de tipo escalar). Las tablas de entrada se utilizan dentro de la consulta devuelta por macro.

Ejemplo de Macro de Tabla usada para vista Parametrizada

A continuación, creamos una función que define una macro de tipo Tabla, que nos devuelve una sentencia SELECT sobre las tablas Regiones y Clientes, filtrando por el ID de la region y a la vez agrupando por el mismo.


CREATE FUNCTION fnLimitePromedio (pID_Region number)
RETURN varchar2 SQL_MACRO(TABLE)
IS 
BEGIN
    RETURN q’[SELECT r.ID_Region, ANY_VALUE(r.Region) AS Region,
                     AVG(c.LimiteCredito) AS CreditoPromedio,
                     COUNT(c.ID_Cliente) AS NroClientes
              FROM Regiones r JOIN Clientes c ON c.ID_Region = r.ID_Region
              WHERE r.ID_Region = :pID_Region
              GROUP BY r.ID_Region]’;
END fnLimitePromedio;

El paso siguiente es usar esta macro como si fuera una vista que acepta un parámetro:

WITH Norte AS
    (SELECT ID_Region
     FROM Regiones
     WHERE ID_Pais = 1)
SELECT *
FROM fnLimitePromedio(Norte);


Ejemplo de Macro de Tabla usada para vista Polimórfica

A continuación, creamos una función que define una macro de tipo Tabla, que nos devuelve una sentencia SELECT sobre una tabla que pasamos como parámetro, limitando la cantidad de filas devueltas mediante el uso de otro parámetro.


CREATE FUNCTION fnPedirNFilas
(
    pTabla DBMS_TF.Table_t,
 pFilas NUMBER DEFAULT 10
)
RETURN VARCHAR2 SQL_MACRO(TABLE)
AS
BEGIN
RETURN q’[SELECT *
          FROM pTabla
          WHERE rownum <= pFilas’;
END fnPedirNFilas;

El paso siguiente es usar esta macro como si fuera una vista normal, pasando el nombre de la tabla y la cantidad de filas como parámetros:

SELECT * 
FROM fnPedirNFilas(pTabla=>Clientes, pFilas=> 20);

En este caso, al optimizador le llega una consulta de la siguiente forma:

SELECT * 
FROM (SELECT * FROM Clientes WHERE rownum <= 20);






lunes, 2 de marzo de 2020

Oracle Standard Edition 2 incluirá una opción de Alta Disponibilidad



Así lo anunció hoy Markus Michalewicz, Director de Producto de Oracle, en el blog de "Maximum Availability Architecture".

Oracle va a incluir una solución de alta disponibilidad para las bases de datos Standard Edition 2 que utilizan Oracle Clusterware, proporcionando una solución de failover en clúster basada en la infraestructura de Grid de Oracle.

Esto permitirá utilizar funcionalidades de alta disponibilidad y las soluciones de administración de almacenamiento que ya forman parte de Oracle Grid Infrastructure, como Oracle Automatic Storage Management (ASM) y Oracle ASM Cluster File System (ACFS), sin costos adicionales.

Esta nueva funcionalidad (sumadas a otras mejoras recientes a SE2 como Oracle Machine Learning, Oracle Spatial y Oracle Graph) estará disponible durante este año calendario como parte de un Release Update (RU) tanto para la version de base de datos Oracle 20c como para Oracle 19c.