Piensateclado: agosto 2012

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;