VALORES ALEATORIOS EN PL/SQL. DBMS_RANDOM.VALUE

Imaginemos que queremos generar ciertos valores aleatorios. A continuación indico las generaciones aleatorias que más uso. Obtener un listado aleatorio en PL/SQL de alguna tabla select * from tabla t where ..... ORDER BY dbms_random.value; Obtener un único registro aleatorio en PL/SQL de alguna de nuestras tablas. -- Un solo valor FROM (SELECT valor_campo FROM tabla ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM = 1; -- Todo el registro SELECT * FROM tabla T (SELECT clave FROM tabla ORDER BY DBMS_RANDOM.VALUE) subtabla WHERE t.clave = subtabla.clave AND ROWNUM = 1; Crear fecha aleatoria de los últimos 100 días por ejemplo. select sysdate-round(dbms_random.value(0,100),0) from dual; Generar número de forma aleatoria. -- Números negativos y positivos SELECT DBMS_RANDOM.random FROM dual; -- Números positivos SELECT ABS(DBMS_RANDOM.random) FROM dual; --Bueno pero queremos especificar el rango en el que estén esos números. SELECT DBMS_RANDOM.VALUE(1,30) FROM dual; -- Número no deciales SELECT ROUND(DBMS_RANDOM.VALUE(1,10)) FROM dual; La siguiente función la he experimentado hace bastante poco. No la conocía y me ha sorprendido. Generar una cadena aleatoriamente. SELECT DBMS_RANDOM.string('L', 10) FROM dual; El primer parámetros tiene el siguiente significado: 'a','A' alpha characters only (mixed CASE) 'l','L' LOWER CASE alpha characters only 'p','P' ANY printable characters 'u','U' UPPER CASE alpha characters only 'x','X' ANY alpha-numeric characters (UPPER)

EXPRESIONES REGULARES EN ORACLE

Imaginemos que se quiere saber si en un campo VARCHAR2 tiene valores sólo numéricos. Lo primero que se nos ocurre es: FUNCTION esCadenaNumerica( vCadena VARCHAR2 ) RETURN VARCHAR2 IS ------------ -- Variables ------------ nTonta NUMBER; BEGIN nTonta := TO_NUMBER( vCadena ); -- Si llego hasta aquí es que el número es válido. RETURN 'TRUE'; EXCEPTION WHEN VALUE_ERROR THEN -- El número no es válido. RETURN 'FALSE'; END;        La función devuelve un varchar para poder ser usada en una select. Esta select me daría los valores no numéricos de una columna: SELECT VVALOR FROM TABLA WHERE NombrePaquete.esCadenaNumerica( VVALOR ) = 'FALSE'        Pero si usamos EXPRESIONES REGULARES podemos optimizar el código.        Podría haber optado por hacer un bloque de pl/sql anónimo, pero he preferido buscar algo más directo y lo he encontrado. Las expresiones regulares están disponibles en la 10g y permiten hacer búsquedas de una manera parecida al like pero más potente. Además de permitir búsquedas, también se pueden hacer potentes replaces y substrs. Por ejemplo, esta select me saca los valores de una columna que no son números enteros: SELECT VVALOR FROM TABLA WHERE REGEXP_LIKE( VVALOR, '[^0-9]')        Enlaces de interés: Oracle Regular Expressions, versión 10.2 Definición de expresión regular.

VISTAS MATERIALIZADAS

Sintaxis básica para la creación de una vista materializada CREATE MATERIALIZED VIEW mi_vista_materializada [TABLESPACE mi_tablespace] [BUILD {IMMEDIATE | DEFERRED}] [REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } | {COMPLETE | FAST | FORCE} ] [{ENABLE|DISABLE} QUERY REWRITE] AS SELECT t1.campo1, t2.campo2 FROM mi_tabla1 t1 , mi_tabla2 t2 WHERE t1.campo_fk = t2.campo_pk AND        Carga de datos en la vista BUILD IMMEDIATE: Los datos de la vista se cargan en el mismo momento de la creación BUILD DEFERRED: Sólo se crea la definición, los datos se cargarán más adelante. Para realizar esta carga se puede utilizar la función REFRESH del package DBMS_MVIEW: begin dbms_mview.refresh('mi_vista_materializada'); end;        De qué manera se refrescan REFRESH COMPLETE: El refresco se hará de todos los datos de la vista materializada, la recreará completamente cada vez que se lance el refresco REFRESH FAST: El refresco será incremental, es la opción más recomendable, lo de fast ya da una idea del porqué. Este tipo de refresco tiene bastantes restricciones según el tipo de vista que se esté creando. Se pueden consultar en General Restrictions on Fast Refresh de la documentación oficial de Oracle Una de las cosas importantes a tener en cuenta es que para poder utilizar este método casi siempre es necesario haber creado antes un LOG de la Vista materializada, indicando los campos clave en los que se basará el mantenimiento de la vista. CREATE MATERIALIZED VIEW mi_vista_materializada [TABLESPACE mi_tablespace] [BUILD {IMMEDIATE | DEFERRED}] [REFRESH {ON COMMIT | ON DEMAND | [START WITH fecha_inicio] NEXT fecha_intervalo } | {COMPLETE | FAST | FORCE} ] [{ENABLE|DISABLE} QUERY REWRITE] AS SELECT t1.campo1, t2.campo2 FROM mi_tabla1 t1 , mi_tabla2 t2 WHERE t1.campo_fk = t2.campo_pk AND        REFRESH FORCE: Con este método se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE. Para saber si una vista materializada puede utilizar el método FAST, el package DBMS_MVIEW proporciona el procedure EXPLAIN_MVIEW        Activación de la reescritura de consultas ENABLE QUERY REWRITE: Se permite a la base de datos la reescritura de consultas DISABLE QUERY REWRITE: Se desactiva la reescritura de consultas La opción QUERY REWRITE es la que más vamos a utilizar si queremos las vistas materializadas para optimizar nuestro Data warehouse. Esta opción permite crear tablas agregadas en forma de vistas materializadas, y que cuando se lance una SELECT la base de datos pueda reescribirla para consultar la tabla o vista que vaya a devolver los datos solicitados en menos tiempo, todo de manera totalmente transparente al usuario Lo único que hay que hacer es crear las tablas agregadas como vistas materializadas con QUERY REWRITE habilitado.        Ejemplos de vistas materializadas Si quisiéramos crear una vista materializada de una tabla que se refresque un día a la semana, y de manera incremental haríamos lo siguiente: CREATE MATERIALIZED VIEW LOG ON mi_tabla_origen WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW mi_vista_materializada REFRESH FAST NEXT SYSDATE + 7 AS SELECT campo1, campo2, campo8 FROM mi_tabla_origen WHERE campo2 > 5000;

USO CORRECTO DEL TIPO CLOB

Como la mayoría de lenguajes de programación, PL/SQL tiene limitaciones de capacidad en los tipos de variable más típicos. Como ejemplo mencionar el VARCHAR2, que puede almacenar hasta 32K de información. La solución, cuando se va a sobrepasar este límite, es utilizar el tipo de datos CLOB.        Entonces, ¿Por qué no nos curamos en salud y utilizamos siempre el tipo de datos CLOB?        Vemos un ejemplo típico declare v_clob clob; v_cadena varchar2(200) := 'texto a concatenar'; begin for i in 1..100000 loop v_clob := v_clob || v_cadena; end loop; end;        Funcionalmente, lo anterior es perfecto, pero el coste es altísimo. La concatenación de CLOB’s es muy costosa en comparación con el resto de operaciones. Así, la utilización de un “buffer” o variable VARCHAR2 en la cual vayamos almacenando los resultados intermedios ahorra más coste.        Es decir, es mejor acumular la información en un VARCHAR2 y sólo volcarla en CLOBs cuando sea estrictamente necesario: declare v_clob clob; v_texto varchar2(32767); v_cadena varchar2(200) := 'texto a concatenar'; begin for i in 1..100000 loop if length(v_texto) + length(v_cadena) <= 32767 then v_texto := v_texto || v_cadena; else v_clob := v_clob || v_texto; v_texto := v_cadena; end if; end loop; v_clob := v_clob || v_texto; end;

RECICLADO DE TABLAS ORACLE. RECUPERACIÓN

Horror!!! hemos eliminado una tabla de la base de datos que contenía datos importantes. drop table prueba;        Antes de buscar información sobre como alistarse en la legión extranjera vamos a realizar una consulta: select * from recyclebin;        Si encontramos nuestra tabla en uno de los registros, no todo está perdido. Podemos recuperarla con la instrucción flashback (la segunda línea recupera la tabla, pero con otro nombre): flashback table prueba to before drop; flashback table prueba to before drop rename to prueba2;

Problema de codificación en la carga de los XML.

A veces ocurre que cuando se trabaja con xml dependiendo de la codificación puede que nuestro código no funcione correctamente. La solución filtrar los datos del xml antes de insertarlos en un campo XMLTYPE de una tabla de la base de datos. Por tanto, para un Insert into TABLA (xxx,xxx,campoXMLTYPE) VALUES('xxx','xxxx',XMLTYPE(getClobDocumento e_directorio,v_ruta||pe_Fichero,null))        Siendo pe_directorio el directorio virtual de base de datos en el que se encuentran los xml. V_ruta es la ruta es la ruta desde el directorio virtual hasta el fichero, si está al mismo nivel no es necesario. Pe_fichero es el nombre del fichero. Ejemplo de declaración FUNCTION getClobDocumento(pe_nombre_directorio VARCHAR2, pe_filename in varchar2, pe_charset in varchar2 default NULL) RETURN CLOB deterministic is file bfile := bfilename(pe_nombre_directorio,pe_filename); charContent CLOB := ' '; targetFile bfile; lang_ctx number := DBMS_LOB.default_lang_ctx; charset_id number := 0; src_offset number := 1 ; dst_offset number := 1 ; warning number; begin if pe_charset is not null then charset_id := NLS_CHARSET_ID(pe_charset); end if; targetFile := file; DBMS_LOB.fileopen(targetFile, DBMS_LOB.file_readonly); DBMS_LOB.LOADCLOBFROMFILE(charContent, targetFile, DBMS_LOB.getLength(targetFile), src_offset, dst_offset,charset_id, lang_ctx,warning); DBMS_LOB.fileclose(targetFile); return charContent; END getClobDocumento;

Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL

Las fechas son un tipo de datos del PL/SQL considerablemente más complejo que un tipo carácter o un tipo numérico. Una fecha o momento de tiempo está compuesto de múltiples campos (año, mes, día, hora, minutos, etcétera) y, además, existen un buen número de normas para determinar si una fecha es válida o no (los años bisiestos, los cambios de hora, etcétera). Como consecuencia de todo esto, en PLSQL resulta habitual tener que: Declarar constantes y variables de tipo fecha o tiempo. Utilizar funciones para modificar dichas variables y mostrarlas en el formato deseado por el usuario. Manipular fechas y tiempos para realizar cálculos variados.        Este artículo será el primero de una serie en los que explicaré todo lo que un programador PL/SQL necesita conocer para trabajar con los diferentes tipos de datos asociados con fechas y momentos de tiempo (DATE, TIMESTAMP e INTERVAL). Los tipos DATE, TIMESTAMP e INTERVAL Afortunadamente la base de datos Oracle y el PLSQL proporciona diferentes tipos de datos que permiten manejar fechas y momentos de tiempo, almacenando ambos tipos de información en un formato interno estándar. Las bases de datos Oracle permiten utilizar tres tipos de datos diferentes para trabajar con fechas y momentos de tiempo: DATE: este tipo de dato permite almacenar una fecha y un tiempo hasta el nivel de segundos. No incluye información sobre la zona horaria. Es el tipo de dato que más se utiliza para trabajar con fechas dentro de cualquier aplicación Oracle. TIMESTAMP: se trata de un tipo de dato similar al DATE pero con dos diferencias clave, permiten almacenar y manipular momentos de tiempo hasta la mil millonésima de segundo (con una precisión de 9 decimales), y también es posible asociarle una zona horaria de tal manera que la base de datos Oracle tendrá en cuenta dicha zona horaria cuando manipulemos y realicemos cálculos utilizando este tipo de dato. INTERVAL: mientras que los tipos DATE y TIMESTAMP indican un momento específico de tiempo, INTERVAL almacena y permite trabajar con duraciones de tiempo, siendo posible definir intervalos de tiempo en términos de años y meses, o de días y segundos. Ejemplo de declaración DECLARE l_hoy_date DATE := SYSDATE; l_hoy_timestamp TIMESTAMP := SYSTIMESTAMP; l_hoy_timetzone TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; l_interval_ym INTERVAL YEAR (4) TO MONTH := '2011-11'; l_interval_ds INTERVAL DAY (2) TO SECOND := '15 00:30:44'; BEGIN null; END; Como comentario os diré que resulta poco usual que un programador de PLSQL tenga que utilizar los tipos TIMESTAMP e INTERVAL con zona horaria, algo que, por cierto, resulta algo complicado y se necesitan conocer algunas funcionalidades avanzadas. ¿Cómo escoger el tipo de dato fecha adecuado?        Ante la diversidad de tipos de dato fecha y momentos de tiempo que ofrece la base de datos Oracle, al escribir nuestro código PL/SQL puede resultarnos complicado decidirnos por un tipo de dato u otro. Estas son las normas que yo utilizo para decantarme por uno u otro: Utilizar el tipo TIMESTAMP cuando es necesario controlar momentos de tiempo por debajo de la fracción de segundo. En general es posible utilizar el tipo TIMESTAMP en lugar del tipo DATE, ya que la base de datos es capaz de distinguir cuando un TIMESTAMP no almacena fracciones de segundo, reservando sólo 7 bytes de almacenamiento para dicho dato, exactamente lo mismo que para un dato DATE. Cuando un TIMESTAMP contiene fracciones de segundo, entonces la base de datos Oracle necesita 11 bytes de almacenamiento. Utilizar TIMESTAMP WITH TIME ZONE cuando sea necesario realizar un seguimiento de la zona horaria de la sesión en la que el dato fue introducido. Utilizar TIMESTAMP WITH LOCAL TIME ZONE cuando la base de datos Oracle tenga que convertir automáticamente tiempos entre bases de datos y sesiones que operan bajo diferentes zonas horarias. Utilizar DATE cuando sea necesario mantener la compatibilidad con una aplicación que fue escrita antes de que el tipo de dato TIMESTAMP fuera introducido. En nuestro código PL/SQL siempre deberemos utilizar tipos de datos que se correspondan, o que al menos sean compatibles, con el tipo de dato asociado con el campo de la tabla que queramos almacenar en la variable correspondiente. Debemos ser conscientes de que si el campo de una tabla es tipo TIMESTAMP y lo almacenamos en una variable tipo DATE, podremos estar perdiendo información (en este caso la relativa a la zona horaria o a las fracciones de segundo).

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL: FOR selrec IN (SELECT * FROM tabla_enorme ORDER BY muchas columnas) LOOP -- Gran cantidad de código que omito y al final: UPDATE tabla_enorme SET ... WHERE clave_primaria = selrec.clave_primaria; COMMIT; END LOOP; El presente código es un extracto de un código PLSQL que uno de los lectores de este blog me envió por correo electrónico indicándome que presentaba graves problemas de rendimiento, cosa que desde un primer momento a mi no me extrañó, considerando que la tabla tabla_enorme contenía más de 30 millones de registros. Es el típico ejemplo de código PL/SQL que para mejorar su rendimiento necesita que se aplique la técnica o funcionalidad de BULK COLLECT (que traducido directamente a castellano sería similar a decir "recogida a granel" pero que aquí traduciremos por procesamiento masivo). Antes de profundizar en el tema, debemos considerar que si leemos los datos de una base de datos Oracle sin necesidad de enviar datos de vuelta a la misma base de datos, no es necesario aplicar la técnica del BULK COLLECT. Es decir, el siguiente código PL/SQL es perfectamente utilizable y no debería generar problemas de rendimiento: FOR x IN (SELECT * FROM tabla_enorme t WHERE ...) LOOP DBMS_OUTPUT.PUT_LINE (x.col1||...||x.colN); END LOOP; En el ejemplo vemos que estamos leyendo los datos utilizando un SELECT, pero que, aun tratándose de una operación registro a registro, no se utilizan para ser retornados a la base de datos utilizando un INSERT, UPDATE o DELETE. La sentencia SQL SELECT ya utiliza, de por sí, la funcionalidad de procesamiento masivo, ya que, desde la versión 10g de la base de datos Oracle, el código "FOR x IN (SELECT ...)", aunque no sea visible para el desarrollador, ha sido optimizado para almacenar internamente en matrices de 100 registros los resultados que devuelve dicho SELECT. Sin embargo, veamos que ocurre con este otro ejemplo de código PL/SQL: FOR x IN (SELECT clave_primaria, col1, col2 FROM tabla_enorme) LOOP x.col1 := calculos(x.col1, x.col2); UPDATE tabla_enorme SET col1 = x.col1 WHERE clave_primaria = x.clave_primaria; COMMIT; END LOOP; En este código PLSQL, la sentencia SELECT estará utilizando la funcionalidad de procesamiento masivo incorporada con la versión 10g, pero no ocurrirá lo mismo con el procesamiento de la sentencia UPDATE. La pregunta ahora es, ¿cómo podemos mejorar el rendimiento de un código tan sencillo? El primer cambio que realizaremos es algo bastante sencillo: FOR x IN (SELECT rowid, col1, col2 FROM tabla_enorme) LOOP x.col1 := calculos(x.col1, x.col2); UPDATE tabla_enorme SET col1 = x.col1 WHERE rowid = x.rowid; END LOOP; El nuevo código lee cada registro de la tabla_enorme, realiza algún tipo de procesado de los datos y, finalmente, realiza el UPDATE de un campo de la misma tabla por ROWID. ¿Qué hemos conseguido con respecto al primer código?, habremos evitado realizar 30 millones de veces un UNIQUE SCAN sobre el índice de la clave primaria, ya que estaremos accediendo a la tabla por ROWID. Acceder a la tabla a través del índice de la clave primaria puede implicar de tres a cinco operaciones de entrada/salida (I/O) por iteración, por lo que acceder a la tabla por ROWID en una tabla tan grande nos puede ahorrar más de cien millones de operaciones de entrada/salida. Otro cambio que observaréis es que hemos eliminado el COMMIT después de cada iteración. Aparte de que realizar un COMMIT después de que cada registro es procesado resultará lento y reducirá bastante el rendimiento, si en mitad del procesamiento se produce algún error, habremos dejado la base de datos Oracle en un estado bastante inconsistente, por no decir corrupto (con media tabla_enorme actualizada y la otra sin actualizar). Pero esto que hemos hecho no tiene en realidad nada que ver con la técnica o funcionalidad de procesamiento masivo. Veamos el siguiente código PL/SQL en el que hemos, por fin, utilizado un FETCH con la cláusula BULK COLLECT: CREATE OR REPLACE PROCEDURE procesamiento_masivo AS TYPE matriz_rowid IS TABLE OF ROWID; TYPE matriz_col1 IS TABLE OF tabla_enorme.col1%TYPE; TYPE matriz_col2 IS TABLE OF tabla_enorme.col2%TYPE; CURSOR cur IS SELECT rowid, col1, col2 FROM tabla_enorme; m_rowid matriz_rowid; m_col1 matriz_col1; m_col2 matriz_col2; contador NUMBER := 100; BEGIN OPEN cur; LOOP FETCH cur BULK COLLECT INTO m_rowid, m_col1, m_col2 LIMIT contador; FOR i IN 1 .. m_rowid.count LOOP m_col1(i) := calculos(m_col1(i), m_col2(i)); END LOOP; FORALL i IN 1 .. m_rowid.count UPDATE tabla_enorme SET col1 = m_col1(i) WHERE rowid = m_rowid(i); EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END; El nuevo código utiliza si ningún tipo de restricción la funcionalidad de procesamiento masivo BULK COLLECT, de manera que los registros se procesan de cien en cien (valor que podremos cambiar con sólo asignar un valor diferente a la variable contador). Y, una vez procesados, lo que hacemos es un UPDATE masivo (bulk update) utilizando la sentencia PL/SQL FORALL. Es fácil observar que el código PLSQL que utiliza la funcionalidad BULK COLLECT es bastante más complicado y mucho menos intuitivo que el original, pero los resultados a nivel de rendimiento van a ser realmente sorprendentes. Para tablas con 30 millones de registros, utilizar la funcionalidad de procesamiento masivo puede hacer que nuestro código se ejecute entre diez y veinte veces más rápido que sin utilizar dicha funcionalidad (ojo que, a veces, la mejora de rendimiento puede ser incluso hasta superior).