SQL – Página 3 – Optimización SQL en Oracle

Manejo de subconsultas en la cláusula SELECT. Parte I.

El motor SQL de Oracle es muy flexible. Se permite el uso de subconsultas en la cláusula WHERE, y HAVING, así como directamente en la cláusula FROM (se tratan como vistas) y también como forma de resolver una columna en el conjunto de resultados en la cláusula SELECT.

Lo curioso es que, dado que el formateo de filas se hace al final, de algún modo el optimizador ignora el coste de combinación de las tablas de la subconsulta, pues las trata una vez ha procesado la sentencia.

Por ejemplo, la consulta de clientes con su identificación de sexo y estado civil.

SQL> set autotrace traceonly

SQL> select nif, cn_sexo sexo, cn_ec estado

2 from clientes, sexos, estadosciviles

3 where clientes.ec_id_ec=estadosciviles.id_ec

4 and clientes.sex_id_sexo=sexos.id_sexo;

9999 filas seleccionadas.

Plan de Ejecución

———————————————————-

Plan hash value: 3779357754

————————————————————————————————

————————————————————————————————

| 0 | SELECT STATEMENT | | 10001 | 341K| 34 (6)| 00:00:01 |

|* 1 | HASH JOIN | | 10001 | 341K| 34 (6)| 00:00:01 |

| 2 | TABLE ACCESS FULL | ESTADOSCIVILES | 5 | 55 | 3 (0)| 00:00:01 |

| 3 | MERGE JOIN | | 10000 | 234K| 31 (7)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| SEXOS | 2 | 16 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | SEX_PK | 2 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 9999 | 156K| 29 (7)| 00:00:01 |

| 7 | TABLE ACCESS FULL | CLIENTES | 9999 | 156K| 27 (0)| 00:00:01 |

————————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(«CLIENTES».»EC_ID_EC»=»ESTADOSCIVILES».»ID_EC»)

6 – access(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)

filter(«CLIENTES».»SEX_ID_SEXO»=»SEXOS».»ID_SEXO»)

Estadísticas

———————————————————-

1 recursive calls

0 db block gets

108 consistent gets

1 physical reads

0 redo size

346739 bytes sent via SQL*Net to client

7846 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

9999 rows processed

La lectura de 108 bloques en memoria corresponde mayoritariamente a los bloques de la tabla CLIENTES, al índice de clave primaria de la tabla SEXOS y al acceso completo por FULL SCAN de la tabla ESTADOSCIVILES. El coste se tasa en 34.

Esta misma consulta, expresando la resolución del valor de sexo y estado civil del cliente directamente en las columnas de resultado, suponen un plan de ejecución similar, pero tasado ligeramente inferior.

SQL> select nif,

2 (select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,

3 (select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado

4 from clientes;

9999 filas seleccionadas.

Transcurrido: 00:00:00.07

Plan de Ejecución

———————————————————-

Plan hash value: 3745735041

———————————————————————————————-

———————————————————————————————-

| 0 | SELECT STATEMENT | | 9999 | 156K| 27 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| SEXOS | 1 | 8 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SEX_PK | 1 | | 0 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| ESTADOSCIVILES | 1 | 11 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | EC_PK | 1 | | 0 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | CLIENTES | 9999 | 156K| 27 (0)| 00:00:01 |

———————————————————————————————-

Predicate Information (identified by operation id):

—————————————————

2 – access(«ID_SEXO»=:B1)

4 – access(«ID_EC»=:B1)

Estadísticas

———————————————————-

1 recursive calls

0 db block gets

766 consistent gets

0 physical reads

0 redo size

380260 bytes sent via SQL*Net to client

7846 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

9999 rows processed

Prácticamente, los costes de acceso y gestión de la tabla SEXOS y ESTADOSCIVILES parecen omitirse en el cálculo (el anterior plan medía 34, menos 3 de acceso a cada tabla y 1 de acceso al índice), pero el volumen total de bloques leídos en memoria se multiplica por siete.

Traza de ejecución con uso de joins

select nif, cn_sexo sexo, cn_ec estado

from clientes, sexos, estadosciviles

where clientes.ec_id_ec=estadosciviles.id_ec

and clientes.sex_id_sexo=sexos.id_sexo

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 668 0.01 0.04 1 108 0 9999

——- —— ——– ———- ———- ———- ———- ———-

total 670 0.01 0.04 1 108 0 9999

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 91 (VUELOS)

Number of plan statistics captured: 1

Rows Execution Plan

——- —————————————————

0 SELECT STATEMENT MODE: ALL_ROWS

9999 HASH JOIN

5 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘ESTADOSCIVILES’ (TABLE)

9999 MERGE JOIN

2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)

2 INDEX MODE: ANALYZED (FULL SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))

9999 SORT (JOIN)

9999 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)

Traza de ejecución con subconsultas en la cláusula SELECT

select nif,

(select cn_sexo from sexos where id_sexo=clientes.sex_id_sexo) sexo,

(select cn_ec from estadosciviles where id_ec=clientes.ec_id_ec) estado

from clientes

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 668 0.00 0.01 0 766 0 9999

——- —— ——– ———- ———- ———- ———- ———-

total 670 0.00 0.01 0 766 0 9999

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 91 (VUELOS)

Number of plan statistics captured: 1

Rows Execution Plan

——- —————————————————

0 SELECT STATEMENT MODE: ALL_ROWS

2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘SEXOS’ (TABLE)

2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF ‘SEX_PK’ (INDEX (UNIQUE))

5 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF ‘ESTADOSCIVILES’ (TABLE)

5 INDEX MODE: ANALYZED (UNIQUE SCAN) OF ‘EC_PK’ (INDEX (UNIQUE) )

9999 TABLE ACCESS MODE: ANALYZED (FULL) OF ‘CLIENTES’ (TABLE)

La ejecución mediante subconsultas parece indicar al optimizador que no es necesario ordenar la tabla CLIENTES para facilitar las joins, y que el obtener por cada fila desde memoria las filas correspondientes al sexo y al estado civil resulta óptimo.

Es algo muy parecido a una ejecución en estrella. La diferencia está en el cálculo de un hash para resolver los valores de sexo y estado civil, o el acceso directo a los bloques. Aunque la segunda ejecución parezca ejecutarse en menos tiempo, hay que tener en cuenta el alto consumo de bloques en memoria, cosa que no sucedería en una ejecución en estrella.

En este caso, la ejecución con subconsultas en la cláusula SELECT sustituyendo las joins ha supuesto una mejora del rendimiento. En la parte II publicaré un caso completamente opuesto. Mientras que los costes en el plan de ejecución aparentemente van siendo cada vez menores, el rendimiento real de las ejecuciones resulta cada vez peor.

Optimización SQL en Oracle. En venta, próximamente!

En breve estará a la venta mi libro «Optimización SQL en Oracle».

Durante los últimos dos años he estado escribiendo este libro que resume, a mi modo de ver, todo lo que un administrador o programador debería conocer para optimizar código SQL.

En él describo cómo funciona el optimizador y cómo se comporta el servidor para escoger los mejores planes de ejecución, los aspectos a considerar para crear tablas de diferentes tipos (tablas IOT, clusters, tablas particionadas, etc.) y lo mismo relativo a los índices. Herramientas para optimizar SQL, desde asesores a las herramientas «manuales» como explain plan, tkprof, autotrace, generación de trazas, análisis de AWR, etc.

Además, también dedico un apartado a los entornos datawarehouse, a optimización SQL de código ineficiente con casos prácticos resueltos, y un glosario completo de hints con ejemplos de su uso y «maluso», y sus consecuencias para el rendimiento.

Este libro responde preguntas y cuestiones habituales como el motivo por qué no siempre es eficiente acceder a las tablas usando índices, escenarios ineficientes, usos incorrectos de tipos de datos y sus consecuencias en la optimización, uso correcto del paralelismo, el particionamiento, las vistas materializadas, jerarquías, dimensiones, consecuencias de usar NOLOGGING, como tratar subconsultas, uso de IN y EXISTS, DISTINCT, ordenaciones, etc.

El esquema del libro es el siguiente:

Por el momento está en fase BETA, pendiente de revisión técnica. Para esta revisión cuento con dos administradores de los más fuertes de España, y vamos a asegurarnos que en las más de 300 páginas no se nos escapa un error.

Me gustaría decir, como los de Valve, «When it’s done, it’s done» como fecha de publicación, pero espero que en cosa de un par de meses pueda estar disponible a la venta.

Estoy contento porque se trata del primer libro en español que trata exclusivamente de optimización SQL y todo su universo. Muchos libros (principalmente en inglés) tratan de aspectos del rendimiento, sobre todo del motor (memoria, procesos) o se centran exclusivamente en administración o programación, pero éste es el primer libro que conozco absolutamente específico, en español, con ejemplos en español, tablas con nombres en cristiano (vuelos, reservas, etc.), sin ser una traducción de una obra en inglés o un copia/pega de partes de la documentación de Oracle.

Yo estoy satisfecho del resultado, y espero que pueda ser de utilidad. Estoy seguro de que incluso los usuarios más avanzados se sorprenderán aprendiendo cosas nuevas, o redefiniendo conceptos, o encontrando una forma práctica y accesible de resumir las funcionalidades y componentes que afectan a la eficiencia del servidor de base de datos.

Os dejo unas imágenes del libro, en fase BETA, listo para revisarlo y corregirlo antes de sacarlo a la luz.

El misterioso caso del error de «privilegios insuficientes» en la creación de vista materializada dentro de un procedimiento PL/SQL.

Recuerdo aquella vez que me pidieron crear un usuario para una aplicación en desarrollo. El usuario debía ser capaz de crear vistas y vistas materializadas, de modo que usé la siguiente sintaxis.

SQL> create user desarrollo identified by desarrollo;
User created.
SQL> grant connect, resource, create view, create materialized view to desarrollo;
Grant succeeded.

De este modo, creía yo, garantizaba que el usuario podría tener ese privilegio de forma explícita, y no mediante un rol, y así descartaba errores que podrían producirse por la no herencia de privilegios a través de roles en el uso de PL/SQL.

No obstante, el usuario vino a mi mesa a decirme: «No puedo crear vistas materializadas. Privilegios insuficientes».
-¿Cómo es posible? – pregunté sorprendido. – Te aseguro que el usuario tiene privilegios para crearlas.

De modo que abrí una consola de sqlplus y ejecuté:

SQL> create materialized view test as select * from dual;
Materialized view created.

– ¿Ves? El usuario tiene privilegios.
– Ya, pero es que yo lo ejecuto dentro de un procedimiento PL/SQL, mediante el comando EXECUTE IMMEDIATE. – contestó.
– Vamos a probar.

SQL> drop materialized view test;
Materialized view dropped.
SQL> begin
2 execute immediate ‘create materialized view test as select * from dual’;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> desc test
Name Null? Type
—————————————– ——– —————————-
DUMMY VARCHAR2(1)

– Pues no lo entiendo – argumentaba, sin salir de su asombro- mi procedimiento PL/SQL da error al ejecutarlo, y la sintaxis ejecutada en una consola de SQL no da errores.

¿qué podría ser? ¿por qué una vista materializada daba error en un procedimiento y no en un bloque anónimo?. Decidí hacer la prueba y comprobarlo por mi mismo. No podía creerlo.

SQL> drop materialized view test;

Materialized view dropped.

SQL> create procedure crea_mv_test as

2 begin

3 execute immediate ‘create materialized view test as select * from dual’;

4 end;

5 /

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at «DESARROLLO.CREA_MV_TEST», line 3

ORA-06512: at line 1

El usuario tenía privilegios explícitos para crear una vista materializada, incluso desde un bloque anónimo, pero no tenía privilegios suficientes para crearla desde dentro de un procedimiento PL/SQL.

No tenía sentido. El mismo usuario podía lanzar esa sintaxis desde la línea de comandos de sqlplus, dentro de un bloque anónimo, pero no en la creación de un procedimiento PL/SQL.

Entonces vi la respuesta, como una luz guiando mis dedos a añadir la cláusula authid current_user.

Así fue. La ejecución del código funcionaba cuando se especificaba claramente que los privilegios debían ser los del usuario conectado y no los del creador del procedimiento.

SQL> create or replace procedure crea_mv_test authid current_user as

2 begin

3 execute immediate ‘create materialized view test as select * from dual’;

4 end;

5 /

Procedure created.

SQL> exec crea_mv_test;

PL/SQL procedure successfully completed.

– Añade esta cláusula al procedure y vuelve a ejecutarlo.

– Funciona! – Exclamó. – ¿Cómo es posible?

– Porque la cláusula authid current_user está heredando los privilegios del usuario que ejecuta el procedimiento, y no los privilegios que tiene el usuario creador del procedimiento.

– Pero, ¡Si se trata del mismo usuario! ¿qué sentido tiene?

Yo ya no atendía a sus planteamientos. Había resuelto el problema. El código se compilaba y ejecutaba correctamente. Las vistas materializabas se creaban todas en sucesión, sin mostrar ningún error. Mi trabajo estaba hecho. Pasó el tiempo, y nunca sabré si lo sucedido aquel día respondía a, lo que se suele llamar en los círculos técnicos, un «expected behaviour».

«La query que tumba la máquina».

Hace unos años publiqué un artículo llamado «PL/SQL y ejecuciones en host» en el que describía el paso a paso para poder, desde PL/SQL, ejecutar código en el sistema operativo.

Oracle no permite que los procedimientos y funciones puedan acceder al host, pero sí permite llamadas a funciones externas implementadas con C o PASCAL, y redireccionadas como librerías mediante un objeto library.

Mi intención inicial fue la de crear un procedimiento PL/SQL que realizara un backup en caliente del servidor, realizase un export, import, o cualquier otra invocación a un ejecutable residente en el sistema operativo.

Hoy he visto una configuración similar en una base de datos en un entorno de producción, que realizan la misma implementación pero mediante una función.

create or replace
FUNCTION sysrun (syscomm IN VARCHAR2)
  RETURN BINARY_INTEGER
  AS LANGUAGE C
  NAME «sysrun»
  LIBRARY shell_lib
  PARAMETERS(syscomm string);

De este modo, la función retorna un 0 si la llamada al SHELL_LIB se ha ejecutado correctamente, o un número si ha habido un error.

Hice esta prueba, y pude constatar un mito: La consulta que tumba el servidor existe!!.

SQL> select sysrun('sudo reboot') from dual; Broadcast message from root (Thu Jan 27 13:16:34 2011): The system is going down for reboot NOW!

SYSTEM.SYSRUN('SUDOREBOOT') --------------------------- 0

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.

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

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’);