jueves, 19 de octubre de 2017

Oracle VirtualBox 5.2 disponible para descargar




La nueva release de Virtual Box puede ser descargada desde el sitio oficial de Oracle y desde la página de descargas de Virtual Box.


La lista de los cambios introducidos en esta versión se encuentra disponible aqui.

Oracle 12c R2 - Conversiones de Tipo de Datos y Manejo de Error

¿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;


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;


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



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


miércoles, 18 de octubre de 2017

Oracle SQL Developer 17.3.1 disponible


Actualización #1 de la Release del tercer trimestre (17.3), contiene mas de 25 correcciones a bugs reportados por usuarios.

Se encuentra disponible para descargar aquí.

martes, 10 de octubre de 2017

Oracle 12c R2 - Mejoras a APROXIMATE QUERY

APPROX_COUNT_DISTINCT en Oracle 12.1

En muchos escenarios de negocio, se requiere conocer información acumulada (total de ventas, cantidad de clientes nuevos, etc. etc.) con un grado de certeza importante, pero sin llegar a la necesidad de que el resultado sea 100% exacto.

Por ejemplo, la gerencia de una cadena de supermercados puede decidir dejar de abrir los días domingos, basándose en la cantidad de operaciones realizadas en dichos días y comparándolo con los otros días de la semana. En dicha situación, conocer el  número exacto de operaciones de venta por cada día de la semana no es una necesidad, sino que con un resultado aproximado es posible tomar una decisión.

En Oracle 12c, se introdujo la función de agregación aproximada APPROX_COUNT_DISTINCT que permite obtener resultados aproximados en forma mas rápida y consumiendo menos recursos.

El principal inconveniente de esta nueva funcionalidad radica en la necesidad de modificar el código existente para utilizar esta nueva función, como podemos ver en el siguiente ejemplo:

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

Nuevos parámetros de Oracle 12.2

Oracle 12c Release 2 introduce tres nuevos parámetros que permiten que las funciones de agregación "normales" (aquellas que venimos utilizando en nuestro código) realicen los cálculos por aproximación, sin necesidad de modificar las consultas existentes.

Los mismos son:

approx_for_aggregation
approx_for_count_distinct
approx_for_percentile

Ejemplo

Si ejecutamos una sentencia con COUNT(DISTINCT) en nuestra base de datos, sin habilitar el uso de funciones de aproximación, y consultamos el resultado y el plan de ejecución, obtenemos lo siguiente:

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   false
approx_for_count_distinct                false
approx_for_percentile                    none

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
   1010000

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 4170058314

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |       |  4472   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    13 |       |            |          |
|   2 |   VIEW               | VW_DAG_0 |  1010K|    12M|       |  4472   (1)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |  1010K|  4931K|    11M|  4472   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T2       |  1010K|  4931K|       |  1390   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Analizando el plan de ejecución, podemos ver que que la consulta generó una vista agrupando por un HASH, para poder calcular el resultado acumulado, la cual consumió 11 Mb. de espacio temporal. El costo total de la consulta es de 4.472, correspondiendo 3.082 de ellos a dicha vista temporal.

Al habilitar el uso de funciones de agregación aproximadas por defecto (sin necesidad de cambiar la funcion COUNT() por la nueva función APROX_COUNT_DISTINCT(), la misma consulta devuelve otro resultado y nos genera el siguiente plan de ejecución:

SQL> ALTER SESSION SET approx_for_aggregation = TRUE;
Session altered.

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   true
approx_for_count_distinct                true
approx_for_percentile                    all

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
    979478

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     5 |  1390   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE APPROX|      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL   | T2   |  1010K|  4931K|  1390   (1)| 00:00:01 |
------------------------------------------------------------------------------

Podemos ver que la segunda consulta devuelve un resultado (979.478) que difiere con el resultado exacto (1.010.000), con una diferencia del 3.02% respecto al resultado exacto.

Pero en este caso, el plan de ejecución nos muestra que Oracle no necesitó agrupar los datos en una vista, reduciendo el costo total en de 4.472 a 1.390 y sin consumir los 11 Mb. de espacio temporal.

En este ejemplo, se logra una reducción del 69% del costo de la consulta sacrificando un 3% de exactitud, lo cual puede ser un valor aceptable para muchos casos.




lunes, 2 de octubre de 2017

Oracle SQL Developer 17.3 disponible




La nueva release trimestral de SQL Developer fue liberada el viernes pasado, y se encuentra disponible para descargar aquí.

Conjuntamente con SQL Developer, se encuentra disponible como descarga adicional SQLcl, la herramienta de linea de comando que reemplaza a SQL*Plus.

Oracle 12c R2 - LISTAGG y OVERFLOW

En Oracle 11gR2 se introdujo una nueva función analítica, LISTAGG, la cual ordena los datos dentro de cada grupo especificado en la cláusula ORDER BY y luego concatena los valores de la columna o expresión seleccionados.

Por ejemplo, si queremos obtener una lista de las tablas a las que tenemos acceso, junto con una lista de todas las columnas, ordenadas por nombre de columna, de cada una de tablas, podemos ejecutar la siguiente consulta:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
GROUP BY table_name;

Lo cual produce el siguiente resultado:


Pero el problema surge cuando la lista concatenada de valores supera el tamaño máximo que el lenguaje SQL soporta en Oracle, el cual es 4000 bytes. En el ejemplo, siguiente, repetimos 50 veces los datos para aumentar el tamaño del resultado de LISTAGG, obteniendo el siguiente error:

SELECT table_name,
       LISTAGG(column_name, ',') WITHIN GROUP 
                  (ORDER BY column_name) Columns
FROM all_tab_cols
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY table_name;

ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size

Oracle 12cR2 introduce una sintaxis ampliada para la función LISTAGG, la cual nos permite obviar el error, mostrando sólo los primeros 4000 bytes que la función obtiene, sin producir un error, como podemos ver a continuación (aquí "cortamos' la cadena concatenada en dos pedazos para poder ver el principio y fin de la misma):

SELECT x.table_name,
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE) 
            WITHIN GROUP (ORDER BY x.column_name),1,30) AS "Comienza con...",
       SUBSTR(LISTAGG(x.column_name, ',' ON OVERFLOW TRUNCATE)
            WITHIN GROUP (ORDER BY x.column_name),-30) AS "Termina con..."
FROM all_tab_cols x
    CROSS JOIN (SELECT level FROM dual CONNECT BY level <=50)
GROUP BY x.table_name;




Como vimos en el ejemplo anterior, la opción "ON OVERFLOW TRUNCATE" por defecto corta el resultado hasta el valor anterior cuyo tamaño no exceda los 4000 bytes, agrega "..." a continuación del último valor que puede ser mostrado, y luego muestra entre paréntesis la cantidad de caracteres que fueron truncados.

Podemos reemplazar los puntos suspensivos con cualquier cadena que deseemos utilizar como indicador para informar que el texto ha sido truncado, de la siguiente forma:

ON OVERFLOW TRUNCATE '///'

Tambien podemos indicarle que no deseamos saber el numero de caracteres truncados, de la siguiente forma:

ON OVERFLOW TRUNCATE WITHOUT COUNT


La sintaxis completa de la función LISTAGG en oracle 12c Release 2 puede encontrarse aqui.