Blog – Página 4 – Optimización SQL en Oracle

Mapa Mental 1: Oracle 10g RAC en Linux

En la instalación de un RAC sobre Linux es preciso tener en cuenta las características de la plataforma a los siguientes niveles:

1.- Red. ¿Existen tarjetas de red para conexiones privadas y públicas en todos los nodos? Es preciso configurarlas correctamente para que el cluster pueda manejar las tres direcciones (pública, privada y virtual). ¿Hay redundancia en las tarjetas? ¿está configurado correctamente el bonding?

2.- Almacenamiento. ¿ASM o OCFS2? O uno, u otro. Nunca los dos o una combinación de ambos en el mismo sistema, ya que puede llevar a conflictos. ¿Hay multipath? El almacenamiento con controladoras redundantes debe configurarse correctamente para evitar desastres.

3.- Sistema. ¿Todos los parches están instalados? ¿los parámetros de kernel y límites de sesión están definidos de forma persistente? ¿los privilegios sobre los dispositivos de almacenamiento tienen privilegios sobre root y oracle como corresponde?

Mapa mental 1: estrella

Quizás en estos casos pueda ayudar el siguiente mapa mental, a modo de guía paso a paso, adaptándolo a la plataforma y las características del entorno a instalar.

Mapa mental 2: vertical

Los procesos del mapa al detalle

· Verificar red public y privada

o Ejecutar /sbin/ifconfig
El resultado debe ser dos tarjetas de red visibles, una con la dirección publica y otra con la privada (mas el tcploopback)

§ Configuración Bonding
Cuando hay redundancia de tarjetas es importante conocer la configuración de bonding para hacer referencia a las tarjetas virtuales y no a las físicas.

§ Configuración interconnect
El interconnect debe estar configurado correctamente para las pruebas de caída de servidores. Los switches deben contemplar las posibles caídas y sus enrutamientos.

o Registrar entradas /etc/hosts
Hay que añadir las direcciones IP para publica, privada y virtual.

La ip virtual no será visible hasta que los servicios de cluster se inicien con el vipca al final de la instalación del clusterware.

o Ping entradas /etc/hosts
Ping a los alias públicos y privados, con y sin nombre de dominio

· Configurar storage

o Crear particiones con /sbin/fdisk
Crear particiones para OCR, voting y rawdevices para ASM.

Minimo:
120 Mb para OCR x 2
20 Mb para voting x 3

Si no se crean particiones, los discos deben estar preparados, así como sus dispositivos.

o Editar /etc/sysconfig/rawdevices

o Asignar privilegios a los rawdevices
Los dispositivos deben pertenecer al usuario Oracle y tener 660 (ocr) y 664 (voting)

Una vez creado el clusterware, los ocr pasaran a ser de root, y será necesario hacer persistentes los permisos al arranque en el fichero /etc/rc.d/rc.local

o Habilitar rawdevices nodo 1

o Propagar configuración nodo 2-3-x
El fichero /etc/sysconfig/rawdevices ha de propagarse al resto de nodos.

Una vez copiado el fichero, hay que actualizar la tabla de partición y habilitar los rawdevices en ese nodo.

services rawdevices start

o Los rawdevices están habilitados en todos los nodos

· Crear grupos y usuarios

o Crear grupos dba y oinstall

o Crear usuario oracle

o chmod y chown a usuario

o Definir .bash_profile

o Configurar secure shell
Generar claves rsa y dsa para concatenar en todos los ficheros authorized_keys de los diferentes nodos.

o Comprobar ssh transparente entre nodos

· Configuración OS

o Configuración Secure Shell
El usuario Oracle debe poder hacer ssh entre nodos sin necesidad de contraseña.

§ Crear claves rsa y dsa
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Los ficheros de $HOME/.ssh id_dsa.pub y id_rsa.pub de todos los nodos han de unirse en uno nuevo llamado authorized_keys, que se copiará a los directorios .ssh del usuario oracle para cada nodo.

§ Añadir hosts a known_hosts
En cada ssh el sistema preguntará para añadir ese nodo a la lista de know_hosts. Solo se preguntará esta vez y las siguientes conexiones serán transparentes.

§ Test ssh nodos /etc/hosts

o Configuración hangcheck-timer
Según notas 811306.1 y 726833.1
Es necesario añadir la línea

/sbin/modprobe hangcheck-timer hangcheck_tick=1 hangcheck_margin=10 hangcheck_reboot=1

Y hacer este cambio permanente al reinicio del servidor en /etc/rc.d/rc.local

o Configurar hugepages
Según nota 361468.1
Es necesario añadir la siguiente línea a /etc/security/limits.conf

* hard memlock 5243000
* soft memlock 5243000

o Configurar kernel

o Configurar limites shell
Los limites de shell han de hacerse permanentes en el inicio de sesión. Para ello, hay que añadir la siguiente línea a /etc/pam.d/login

session required /lib/security/pam_limits.so

o rda hcve para cada nodo

o Cluvfy

§ Chequeo hardware y OS
./runcluvfy.sh stage -post hwos -n nodo1,nodo2,nodoN – verbose

§ Chequeo conectividaf
./runcluvfy.sh comp nodecon -n nodo1,nodo2,nodoN – verbose

§ Chequeo usuario
./runcluvfy.sh comp admprv -n nodo1,nodo2,nodoN – verbose -o user_equiv

§ PreCheck configuración servicios de cluster
./runcluvfy.sh stage -pre crsinst -n nodo1,nodo2,nodoN -r 10gR2

· Ejecutar runInstaller clusterware software
Definir variables de entorno para ORA_CRS_HOME

o Seguir nota 414163.1 en RHL5 para errores conocidos
Existen tres errores conocidos en la instalación de RAC 10.2.0.1 en RHL5

– ejecutar el runinstaller con -ignoreSysPrereqs
– editar vipca y svrctl para añadir la línea «unset LD_ASSUME_KERNEL»
– La ejecución de root.sh fallará en el ultimo nodo y habrá que ejecutar el vipca manualmente tras configurar las tarjetas de red con

/bin # ./oifcfg setif -global eth0/192.168.1.0:public
/bin # ./oifcfg setif -global eth1/10.10.10.0:cluster_interconnect
/bin # ./oifcfg getif
eth0 192.168.1.0 global public
eth1 10.10.10.0 global cluster_interconnect

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados.

o Instalar patchset 10.2.0.5 clusterware
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB’s que deban pararse y parchearse.

· Ejecutar runInstaller ASM software
Definir variables de entorno para ORA_ASM_HOME

o Instalar asmlib
Instalar librerias asmlib

o Configurar multipath para ASM
Seguir nota 564580.1 si hay redundancia de controladores de disco por multipath

o Creación de diskgroups
Crear dos diskgroups: uno para DATA y el otro para FRA

DG_DATA_1
DG_FRA_1

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados incluyendo los de ASM.

o Instalar patchset 10.2.0.5 ASM
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB’s que deban pararse y parchearse.

· Ejecutar runInstaller DB software
Definir variables de entorno para ORACLE_HOME

o Crear bbdd RAC usando asm
Seguir nota 564580.1 si hay redundancia de controladores de disco por multipath

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados incluyendo los de ASM y los de base de datos.

o Instalar patchset 10.2.0.5 ASM
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB’s que deban pararse y parchearse.

Plagiado.

Alguien llamado Fernando Valenzuela me ha plagiado unos siete artículos.

En la mayoría ni siquiera ha cambiado la tipografía, ni los datos de ejemplo, ni comentarios personales como dedicatorias o alusiones a las personas que motivaron la creación de esa entrada, o los responsables de las peticiones o consultas.

Me parece algo muy grave, sobre todo teniendo en cuenta que todo el contenido que hay publicado en mi blog está bajo una licencia creativa (Creative Commons) que permite prácticamente cualquier uso de mi trabajo, o incluso hacer obras derivadas, siempre y cuando se reconozca al autor, y se publique bajo una licencia similar sin provecho comercial.

En concreto, los artículos plagiados son:

Copiar una Base de datos en Windows
Original creado el 29 de Julio de 2005, y plagiado el 31 de agosto de 2009

Acceso a una bbdd MSAccess desde Oracle
Original creado el 8 de Septiembre de 2006 y plagiado el 31 de agosto de 2009

Uso de Logminer
Original creado el 21 de Marzo de 2006 y plagiado el 31 de agosto de 2009

Optimización SQL – varios artículos
Original creados el 25 de Octubre de 2005, el 17 de Octubre de 2005 , el 13 de Octubre de 2005 , y el 11 de Octubre de 2005, y plagiados el 31 de agosto de 2009 aquí y aquí.

Encriptación de datos desde aplicación.

En todos los ejemplos que he encontrado sobre encriptación y desencriptación de datos en Oracle, siempre se usan procedimientos PL/SQL para establecer la seguridad en la base de datos. No he encontrado un sólo ejemplo que permita hacer un insert «encriptado» y una consulta «desencriptada».

Imaginando el siguiente escenario: Cada usuario tiene una «palabra secreta» para desencriptar su propia información. En la base de datos todo se registra encriptado.

Para ello, Oracle ofrece dos paquetes:

– DBMS_OBFUSCATION_TOOLKIT. A partir de Oracle8i, que soporta encriptación DES y triple DES (Data Encription Standard), y con ciertas limitaciones (por ejemplo, los datos a encriptar han de ser un múltiplo de 8 bytes).

– DBMS_CRYPTO. A partir de Oracle10g. Soporta más formas de encriptación, como la AES (Advanced Encription Standard), que sustituye el anterior DES y no hay limitación con el número de carácteres.

Para mas información, la documentación de Oracle ofrece esta comparativa de funcionalidades.

Uso de DBMS_OBFUSCATION_TOOLKIT

El siguiente ejemplo muestra la encriptación de la palabra «SECRETO» (8 bytes) y genera un error al intentar encriptar «SECRETITOS!» (11 bytes)

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>‘SECRETO!’,key_string=>’clavedesencript’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’SECRETO!’,KEY_STRING=>’CLAVED
——————————————————————————–
lr??

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>‘SECRETITOS!’,key_string=>’clavedesencript’) from dual;
select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETITOS!’,key_string=>’clavedesencript’) from dual
*
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT_FFI», line 21
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT», line 126

El error ORA-28232 corresponde a la longitud inadecuada de la cadena a encriptar. ‘SECRETITOS!» tiene 11 carácteres y el paquete está limitado a múltiplos de 8 bytes. Por ejemplo, el número de una tarjeta de crédito.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1234567812345678′,
2 key_string=>’clavedesencript’)
3 from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’1234567812345678′,KEY_STRING=
——————————————————————————–
}??X??

De modo que la desencriptación funciona de igual modo, usando la función DESDecrypt

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
SECRETO!

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
1111222233334444

y si se utiliza una clave distinta, la información no se desencriptará adecuadamente.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_ERRONEA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
???! k

…ó producirá un error.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_MALA’) from dual;
ERROR:
ORA-29275: partial multibyte character

no rows selected

Uso de DBMS_CRYPTO

El siguiente ejemplo muestra la encriptación de la palabra «SECRETITOS!» (11 bytes) usando una suite de encriptación que ya viene implementada. En concreto es la DES_CBC_PCKS5, que contiene encriptación DES, encadenamiento de cifrado de bloques y modificadores de relleno PCKS5.

Es preciso, para invocar correctamente a este paquete, realizar una conversión a RAW de las cadenas a encriptar. He utilizado para ello el paquete UTL_RAW y la función UTL_I18N.STRING_TO_RAW.

SQL> select DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
2 typ => 4353,
3 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
4 )
5 from dual;

DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.STRING_TO_RAW(‘SECRETITOS!’,’AL32UTF8′),TYP=>4
——————————————————————————–
1BA7F933C2CAD0C7F4FDA685775BE0E7

Y la desencriptación de la información, con la función DECRYPT.

SQL> select UTL_RAW.cast_to_varchar2(
2 DBMS_CRYPTO.DECRYPT(
3 DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
4 typ => 4353,
5 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
6 ),
7 typ => 4353,
8 key => UTL_I18N.STRING_TO_RAW (‘clavedesdecript’, ‘AL32UTF8’)
9 )
10 )
11 from dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT(DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.S
——————————————————————————–
SECRETITOS!

Para más información sobre las múltiples formas de encriptación y uso de claves, lo mejor es consultar la documentación del paquete DBMS_CRYPTO.

Oracle10g Data Miner.

Una de las cosas más interesantes en la explotación de una base de datos es, finalmente, la minería de éstos.

El concepto de «minería de datos» se basa en el análisis de los datos con fines predictivos, para encontrar patrones ocultos en éstos… ¿quien podría adivinar que a una determinada hora o un determinado día de la semana se consume un determinado producto? ¿o que un producto orientado a hombres (cuchillas de afeitar) pasa a ser usado por mujeres solteras de un rango de edad?

Una predicción de este tipo podría sugerir la creación de una nueva linea de producto, ofertas, etc.

Hace tiempo impartí una conferencia sobre cómo implementar un modelo de base de datos de reservas en vuelos, desde un diagrama entidad-relación concreto hasta la explotación de datos históricos en el futuro. La aplicación pasaba por varias etapas (diseño, implementación, uso/cargas de datos, paso a histórico y reporting), y aunque los datos eran cargas completamente aleatorias, sucedían ciertos patrones interesantes.

Los casados tomaban vuelos a Roma, los solteros a Milán, «Air France» viajaba con los vuelos a medio llenar, y otras compañías apenas tenían uno o dos vuelos con pérdidas…

De modo que se me ocurrió hacer una prueba de minería de datos, para buscar una predicción que no pudiera verse «a simple vista». Éste fue el resultado:

Ejemplo de Uso – Oracle Data Miner – Parte 1/2

Ejemplo de Uso – Oracle Data Miner – Parte 2/2

Al final, la predicción (absurda) sobre los datos aleatorios fue una relación de probabilidades en las cuales un determinado tipo de persona (hombre/mujer, de una edad, de una ciudad) reservaría billetes con precios superiores a la media.

Acceso a una bbdd MSAccess desde Oracle.

Es posible acceder a bases de datos no oracle de forma transparente con Oracle Transparent Gateways.

Esta funcionalidad permite configurar una entrada DNS de ODBC como si se tratara de un servicio Oracle cualquiera. De este modo, mediante un dblink, un usuario podría acceder a una tabla de una base de datos access (o SQLServer o MySQL, por ejemplo) desde una query o desde PL/SQL.

La arquitectura de servicios heterogeneos se basa en la siguiente configuración:

Se especifica una entrada de TNS en el fichero tnsnames.ora que resuelva una conexión al host, puerto y nombre de servicio determinado, con el parámetro HS=OK (Oracle Heterogeneous Services).

Por otro lado, el listener destino tiene definido para ese servicio la invocación a un programa llamado «hsodbc» que, mediante un fichero de parámetros llamado init.ora accede al DNS asociado a la base de datos MSAccess concreta.

De este modo, y creando un dblink, cualquier usuario podría hacer una select que cruzara datos de su esquema Oracle y de una bbdd Access.

PASOS A SEGUIR
1.- Instalar en el diccionario los servicios heterogeneos.
2.- Configurar el tnsnames.ora
3.- Configurar el listener.
4.- Comprobar que el listener y el tnsnames.ora funcionan correctamente.
5.- Configurar el ODBC para la bbdd access.
6.- Crear el DBLINK hacia la bbdd access.
7.- Crear el fichero init.ora
8.- Ejecutar una consulta mediante ese dblink.

1.- Instalar en el diccionario los servicios heterogeneos
@?/rdbms/admin/caths.sql

NOTA: puede que estas vistas estén instaladas ya en la bbdd.

2.- Configurar el tnsnames.ora
bbdd_access =
   (DESCRIPTION=
      (ADDRESS= (PROTOCOL=tcp)
               (HOST=192.168.2.4)
               (PORT=1521)
     )
     (CONNECT_DATA =
         (SERVICE_NAME=odbc_access)
     )
     (HS = OK)
)

3.- Configurar el listener. (la entrada en negrita es la que hay que añadir)…

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
       (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:oracleora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:oracleora92)
      (SID_NAME = orcl)
    )
     (SID_DESC =
      (ORACLE_HOME = D:oracleora92)
      (SID_NAME = odbc_access)
       (PROGRAM = hsodbc)
    )
  )

4.- Comprobar que el listener y el tnsnames.ora funciona correctamente.

C:Documents and SettingsAdministrador>tnsping bbdd_access

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 – Production on 08-SEP-2006 18:19:42

Archivos de parßmetros utilizados:
D:oracleora92networkadminsqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.4) (PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=odbc_access)) (HS = OK))
Realizado correctamente (50 mseg)

5.- Configurar el ODBC para la bbdd access.
La base de datos access utilizada en el ejemplo tiene una única tabla con cuatro filas de ejemplo…

create table ejemplo
(id counter,
valor text)

Es preciso crear un ODBC para acceder a la bbdd access determinada. Desde Windows esta opción se configura en Panel de control, Herramientas administrativas, Administrador ODBC y la pestaña DNS de sistema.

desde ahí se crea el driver odbc_access vinculado a la base de datos que uso: «test_hs.mdb» con esta tabla de ejemplo.

NOTA: esta bbdd access tiene password «tesths»

6.- Crear el DBLINK hacia la bbdd access.

SQL> CREATE DATABASE LINK bbdd_access
2 CONNECT TO ADMIN IDENTIFIED BY TESTHS
3 USING ‘bbdd_access’;

Enlace con la base de datos creado.

7.- Crear el fichero init.ora en el directorio de ORACLE_HOME /hs/admin/

— FICHERO initODBC_ACCESS.ora
#
# HS init parameters
#

HS_FDS_CONNECT_INFO = odbc_access
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = odbc_access.trc
HS_AUTOREGISTER = TRUE

8.- Lanzar la query via ese dblink

SQL> column «valor» format a40
SQL> set pages 1000
SQL> set lines 120
SQL> select * from ejemplo@bbdd_access;

        id valor
———- —————————————-
         1 dato1
         2 dato2
         3 dato3
         4 dato4

Oracle Enterprise Manager. Ejemplo de uso.

Oracle Enterprise Manager (OEMGR) es una herramienta que permite monitorizar bases de datos Oracle mediante dos tipos de conexiones:
– Standalone (o modo autónomo)
– Mediante OMS (Oracle Management Server)

El modo Standalone funciona como una aplicación estándar cliente/servidor, mientras que la conexión a un OMS utiliza un repositorio intermedio con información global sobre las bases de datos registradas.

(ver el video en pantalla completa)

00:15 – Autentificación con el OMS.

00:30 – Packs de aplicaciones disponibles en OEMGR

01:00 – Conexión a una base de datos y cambios en la SGA (memória). Monitorización de sesiones.

02:00 – Monitorización y gestión de tablespaces. Añadir un datafile a un tablespace y visualización del SQL generado.

03:30 – Monitorización de varias bases de datos de forma global. Sobre estas bases de datos OEMGR permite implementar trabajos (jobs) y alertas que actuen ante cualquier cambio de interés a monitorizar.

03:55 – Creación de una alerta sobre una bbdd.

05:30 – La alerta, creada correctamente, hace visualizar en el mapa una bandera verde que confirma que todo funciona correctamente.

Funciones Analíticas.

Hay veces en las que encuentro consultas SQL a las que llamo «query ataque de pánico», porque puedo imaginarme el sufrimiento del que la programó. Con mucha frecuencia, este tipo de consultas aparecen en casos en las que una función analítica hubiera resuelto toda la complejidad del problema.

Al detalle. Una consulta del tipo «Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de…» suele consultarse con una subconsulta para cada «fecha de…».

Éste ejemplo, o el típico «Los tres contratos más recientes, las cinco últimas cancelaciones, etc.» siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones… y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.

…evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.

Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.

Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).

FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)

Las funciones analíticas están descritas en la documentación de Oracle y entre éstas, por ejemplo, se hallan MAX(), MIN(), RANK(), etc.

Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:

SELECT     a.ID_FACTURA,
           a.FALINEA_AUX – b.minCount + 1 ID_FALINEA,
           a.ID_CLIENT,
           a.ID_COMPTEFACT,
           a.PRODUCT_ID,
           a.ID_PRCATPRODUCTE,
           a.DS_PRNUMSERVEI,
           a.ID_FACONCEPTE,
           a.DT_FAFACTURACIO,
           a.NUM_FAIMPORTCONCEPTE,
           a.PRODUCT_LABEL,
           a.DT_MOVIMENT,
           a.FG_TIPUSOPERACIO,
           a.asset_id,
           a.PRODUCT_ATTR_VALUE
FROM vw_ci_linia_factura_tmp a,
     (select t.id_factura,
             t.dt_fafacturacio,
             min(t.falinea_aux) minCount
      from vw_ci_linia_factura_tmp t
      group by t.id_factura,
               t.dt_fafacturacio
     ) b
WHERE a.id_factura = b.id_factura
ORDER BY a.id_factura, a.FALINEA_AUX – b.minCount + 1 ASC;

En este caso, la necesidad de obtener la primera línea de factura para cada linea de factura, parece que obliga a cargar dos veces la vista (y su conjunto de tablas) en memoria y hacer una JOIN entre ambas.

No es necesario. Los costes de ejecución se reducen a la mitad.

SELECT     a.ID_FACTURA,
           a.FALINEA_AUX – min(falinea_aux) over
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,
           a.ID_CLIENT,
           a.ID_COMPTEFACT,
           a.PRODUCT_ID,
           a.ID_PRCATPRODUCTE,
           a.DS_PRNUMSERVEI,
           a.ID_FACONCEPTE,
           a.DT_FAFACTURACIO,
           a.NUM_FAIMPORTCONCEPTE,
           a.PRODUCT_LABEL,
           a.DT_MOVIMENT,
           a.FG_TIPUSOPERACIO,
           a.asset_id,
        a.PRODUCT_ATTR_VALUE
FROM sta_vw_ci_linia_factura_tmp a
ORDER BY 1,2 ASC;

Hablemos de Constraints.

Las constraints son reglas que ofrecen una garantía de integridad en la información vinculada. Existen 5 tipos de constraints.

–   Primary key
–   Foreign key
–   Unique key
–   Not Null
–   Check

Las constraints tienen, básicamente, tres propósitos:

1.- Forzar integridad. Una constraint con estado ENABLED evita que, sobre las tablas, puedan modificarse valores de forma que no se satisfaga la regla de la constraint.

2.- Validar la información. Una constraint con estado DISABLED VALIDATE realiza una validación sobre los datos, certificando que la información contenida en las tablas satisface la regla de la constraint.

3.- Asumir la validez de la información. Una constraint con estado RELY no verifica la información, ni fuerza la integridad para las modificaciones y asume que los datos introducidos satisfacen la regla de la constraint.

Las constraints, por tanto, pueden estar ENABLED o DISABLED.
Las constraints habilitadas cumplen el propósito 1. Ningún dato introducido o modificado viola la integridad de la regla de la constraint. De este modo, sobre una primary key ENABLED no se permite un insert sobre una fila ya existente. No obstante, si la constraint está en el estado ENABLED NOVALIDATE, no se permitirán cambios que violen la regla, pero no se verificarán que los datos existentes la cumplan.

Las constraints, por tanto, pueden estar en estado VALIDATE o NOVALIDATE independientemente de si están ENABLED o DISABLED.
Las constraints con VALIDATE cumplen el propósito 2: La información existente está validada. No obstante, si la constraint está deshabilitada con DISABLED VALIDATE, no se permitirán cambios sobre las columnas afectadas, ya que aunque la constraint no está habilitada, certifica que los datos existentes son válidos y cumplen con la regla de la constraint.

Las constraints, por tanto, pueden estar en estado RELY o NORELY (también conocido como BELIEF) cuando las constraints están en estado DISABLED NOVALIDATE.
Las constraints en ese estado cumplen con el propósito 3: Asumen que la información es correcta y aportan al optimizador información válida para el uso de vistas materializadas, optimización de queries, etc. De este modo, por ejemplo, al optimizador le puede resultar mucho más fácil encontrar tablas FACT o DIMENSION por las constraints de foreign key entre las tablas.

Las constraints DISABLED NOVALIDATE RELY ni validan la información de la tabla, ni comprueban que las modificaciones cumplen las reglas de las restricciones.

Ejemplo 1: Una restricción de clave primaria habilitada con la opción de NOVALIDATE. No verifica la integridad referencial de las filas, pero no permite que las nuevas filas puedan violar la restricción.

SQL> create table valores(
  2   id number,
  3   valor varchar2(50),
  4   descrip varchar2(300),
  5   CONSTRAINT PK_VALORES PRIMARY KEY (id) deferrable initially immediate);

Tabla creada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO’,’Primer elemento, tabla vacía’);

1 fila creada.

SQL> insert into valores values (1,’ELEMENTO UNO»’,’Primer elemento, repetido’);

1 fila creada.

SQL> insert into valores values (1,’ELEMENTO UNO»»’,’Primer elemento, otra vez repetido’);

1 fila creada.

SQL> alter table valores enable novalidate primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO»»»’,’Primer elemento, violando PK, repetido’);
insert into valores values (1,’ELEMENTO UNO»»»’,’Primer elemento, violando PK, repetido’)
*
ERROR en lÝnea 1:
ORA-00001: restricci¾n ·nica (DW_VIAJES.PK_VALORES) violada

SQL> select * from valores;

        ID VALOR                                    DESCRIP
———- —————————————- —————————————-
         1 ELEMENTO UNO                             Primer elemento, tabla vacía
         1 ELEMENTO UNO’                            Primer elemento, repetido
         1 ELEMENTO UNO»                           Primer elemento, otra vez repetido

Ejemplo 2: Una restricción de clave primaria deshabilitada con la opción de VALIDATE. Verifica la integridad referencial de las filas, pero como está deshabilitada, no permite DML sobre la columna.

SQL> truncate table valores;

Tabla truncada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,’ELEMENTO UNO’,’Primer elemento, tabla vacía’);

1 fila creada.

SQL> insert into valores values (2,’ELEMENTO DOS’,’Segundo elemento, ID distinto.’);

1 fila creada.

SQL> insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’);

1 fila creada.

SQL> alter table valores disable validate primary key;

Tabla modificada.

SQL> insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’);
insert into valores values (3,’ELEMENTO TRES’,’Tercer elemento, ID distinto.’)
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

SQL> insert into valores values (4,’ELEMENTO CUATRO’,’Cuarto elemento, ID distinto.’);
insert into valores values (4,’ELEMENTO CUATRO’,’Cuarto elemento, ID distinto.’)
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

Ejemplo 3: Una restricción de clave ajena deshabilitada. Permite la entrada de valores no existentes en la PK. La opción de enable constraint con la cláusula EXCEPTIONS INTO registra en la tabla exceptions los identificadores de las filas que no cumplen la condición de la constraint.

SQL> create table master (id number primary key);

Tabla creada.

SQL> create table detail (id number primary key, valor varchar2(50),
2 master_id number constraint fk_master_id references master(id));

Tabla creada.

SQL> insert into master select rownum from user_tables;

40 filas creadas.

SQL> alter table detail disable constraint fk_master_id;

Tabla modificada.

SQL> insert into detail select rownum, object_name, mod(rownum,45)+1 from user_objects;

68 filas creadas.

SQL> alter table detail enable constraint fk_master_id exceptions into exceptions;
alter table detail enable constraint fk_master_id exceptions into exceptions
*
ERROR en línea 1:
ORA-02298: no se puede validar (ORADBA.FK_MASTER_ID) – claves principales no encontradas

SQL> select count(*) from exceptions;

COUNT(*)
———-
5
SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT
—————— —————————— —————————— ——————
AAAY/nAAJAAAd7KAAn ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAo ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAp ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAq ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAr ORADBA                         DETAIL                         FK_MASTER_ID

Uso de logminer.

En alguna ocasión he necesitado recuperar una transacción validada con COMMIT, pero no deseada o errónea. Pongo un ejemplo:

– Usuario borra una fila.
– Por accidente.
– No sabe de qué fila se trata, pero ‘algo’ ha dicho «registro borrado».
– (…borrado y validado).
– Ni idea de qué datos tenía la fila.
– Ni él, ni nadie.
– Desde ese momento, la fila X ha desaparecido sin rastro.

Con variaciones, éste caso es bastante frecuente, y si el usuario que ha borrado el registro es lo suficientemente rápido, existe una posibilidad de recuperar los datos perdidos.

Generalmente, todas las transacciones convencionales procesan la información pasando por las áreas de redo log. En estos archivos está toda la información correspondiente al nuevo cambio. Oracle basa toda su actividad en el buen funcionamiento del registro de redo log y, por tanto, en esta área únicamente almacena los datos físicos elementales para rehacer la operación. Por tanto, no se almacena información sobre los valores anteriores.

Lo cual sí sucede en el área de rollback, y ¡la gestión de rollback también pasa por redolog!.

Oracle dispone de un paquete, el LOG MINER que permite rastrear los redo logs (online y archivados) junto con la información de rollback disponible también en éstos. Una vez ejecutado el paquete que crea el fichero de diccionario de objetos, Oracle es capaz de interpretar esos redologs y mostrar información tan válida como el SQL generado y el SQL inverso para «reparar» la acción.

SQL> connect / as sysdba
Conectado.
SQL> show parameters utl

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string c:oraclefiles

SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>’dictionary.ora’, DICTIONARY_LOCATION => ‘c:oraclefiles’);