Variables en SQL*Plus (parte 3)

Anteriormente repasando variables en sqlplus, vimos las variables de sustitución y las de usuario. Finalmente llegamos al tercer tipo de variables, las variables bind (o de ligadura).

VARIABLES BIND

Estas variables tienen la particularidad de poder ser definidas en sqlplus, usadas y/o asignadas dentro de nuestros programas PL/SQL y luego leídas una vez terminado el programa. Son convenientes cuando queremos tener comunicación entre scripts de sqlplus y bloques de PL/SQL.

Las variables bind se definen así:

SQL> VARIABLE bvar NUMBER;

Pueden utilizarse también los tipos: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, BINARY_FLOAT y BINARY_DOUBLE

Si queremos asignar estas variables antes de llamar a nuestros bloques de PL/SQL, debemos hacerlo desde otro bloque BEGIN END o simplemente usando la sentencia EXEC:

SQL> exec :bvar := 1;

PL/SQL procedure successfully completed.

Las variables dentro de PL/SQL se referencian con dos puntos seguidos del nombre de la misma.
Ahora ya podemos utilizar las variables dentro de nuestro programa e imprimir el resultado:

SQL> BEGIN
   2   IF :bvar = 1 THEN
   3     :bvar := 0;
   4   ELSE
   5     :bvar := 1;
   6   END IF;
   7 END;
   8 /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
0

SQL> /

PL/SQL procedure successfully completed.

SQL> print bvar

BVAR
----------
1

Y con esto culminamos el repaso de variables en sql*plus. Finalmente como complemento, veremos brevemente los cursores referenciales los cuales son también útiles para recuperar un conjunto de tuplas de nuestros SELECTs, desde los programas PL/SQL hacia fuera, y así tener más flexibilidad.

REFCURSORS

Se declaran como una variable bind, pero colocamos REFCURSOR en el tipo:

SQL> VARIABLE rcur REFCURSOR;

SQL> begin
2 OPEN :rcur FOR select segment_name, segment_type from user_segments order by segment_name;
3 end;
4 /

PL/SQL procedure successfully completed.

A diferencia de los cursores tradicionales que se abren y requieren del uso de fetch, al abrir estos cursores bind ya son ejecutados automáticamente y sus resultados almacenados en memoria. Solo resta imprimir las filas de la consulta.

SQL> print rcur

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
ACUMULADO_CONSUMO    TABLE
BIG_TABLE            TABLE
BIG_TABLE_PK         INDEX
CLIENTE              TABLE
COUNTRY_C_ID_PK      INDEX
CUENTA               TABLE
INVENTARIO           TABLE
IX_T                 INDEX
MUNICIPIO            TABLE
NDX_ACUMCON          INDEX
NDX_ACUMRENT         INDEX
NDX_ACUMTERC         INDEX
NDX_ACUMVAR          INDEX

13 rows selected.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 2) - Sobre variables de sustitución

Update condicional

¿Cómo se hace un UPDATE condicional?
Digamos que tengo una tabla t con 3 campos a, b y c. Quiero que si a=1 entonces actualizo b con algún valor, y si a=2 actualizo c.

Alguien de mi equipo se encontró con este dilema y lo resolvió de forma muy práctica: dos updates, uno para actualizar aquellos valores con a=1 y otro para actualizar los a=2:

1 BEGIN
2    UPDATE t
3   SET b=valor
4   WHERE a=1;
5
6   UPDATE t
7   SET c=valor
8   WHERE a=2;
9 END;

Otro programador del equipo refutó: -¿Para qué ejecutar dos sentencias update si en su lugar puedes hacer una?

1 BEGIN
2   UPDATE t
3   SET B=DECODE(A,1,valor,2,B),
4   C=DECODE(A,1,C,2,valor);
5 END;


¿Quién de los dos tiene razón?

...depende.

Dado que la performance parece ser lo que está en juego, la primera pregunta que cabe es:

¿Tiene la tabla t índice por A?

Si la tabla no tiene índice, entonces debemos recorrerla por entero. En el primer caso recorremos la tabla dos veces, mientras que en el segundo caso lo hacemos solamente una! Por lo tanto la primera solución en este escenario es el doble más lenta que la segunda. Al notar que la tabla no tiene índices, el Programador 2 asiente la cabeza con orgullo.

Inmediatamente el Programador 1 reacciona: -Pongámosle un índice a t entonces!
(el ávido Programador 1 sabe que la segunda consulta no puede sacar provecho de un índice por lo que su victoria parece asegurada)

Y aquí es donde cabe la segunda pregunta:

¿Cuál es la cardinalidad de la columna A?

Sería de gran ayuda saber si 1 y 2 son la mayoría de los valores que hay en la columna A, porque en ese caso, el optimizador va a continuar prefiriendo recorrer toda la tabla! Si la cardinalidad de los valores 1 y 2 son un porcentaje muy bajo del conjunto de valores distintos, entonces recién ahí los índices van a ser utilizados.
Cuando el índice es utilizado por la escasa presencia de valores 1 y 2, los tiempos de la primer solución es abismalmente inferior.

Conclusión:

La solución 1 es mejor si existen índice sobre la columna A y su cardinalidad es alta (hay pocos 1 y 2 en A).
La solución 2 es mejor si no existen índices o existe y la cardinalidad de A es baja para valores 1 y 2 (significa que 1 y 2 ocurren mucho en A).

Ver también:
Mis índices no funcionan!
Indices condicionales

tic... toc

¿Cómo medir la performance de un código PL/SQL?

Muchas veces nos encontramos con procedimientos lentos de muchos pasos, con llamadas a funciones, DML, loops con cursores, etc, que queremos revisar. Como SYSDATE nos da la cantidad de segundos como mínima fracción de tiempo, no nos es de mucha ayuda consultarlo entre líneas.

Afortunadamente, contamos con la función get_time del paquete dbms_utility para obtener la cantidad de centésimas de segundo que transcurren entre dos instantes de tiempo.
A sabiendas de eso, debemos incluir los statements que invoquen a dichas funciones y desplieguen los tiempos por la salida, en un archivo o en una tabla de logs.

Con el fin de evitar duplicar código y además aprovechar las bondades del cacheado de paquetes en el área de memoria compartida, creé un pequeño paquete exclusivo para ese fin. El mismo tiene dos procedimientos: tic y toc. Cuando quiero comenzar a medir tiempo, invoco a tic (con un mensaje para registro). Cuando quiero medir el tiempo transcurrido, uso toc. Así de simple.

CREATE OR REPLACE PACKAGE crono AS
  v_tic PLS_INTEGER;
  evento VARCHAR2(100);
  PROCEDURE tic(p_evento IN VARCHAR2);
  PROCEDURE toc;
END crono;
/

CREATE OR REPLACE PACKAGE BODY crono AS
 PROCEDURE tic(p_evento IN VARCHAR2) IS
 BEGIN
   v_tic := DBMS_UTILITY.GET_TIME;
   evento := p_evento;
   DBMS_OUTPUT.PUT_LINE('TIC: '||p_evento);
 END tic;

 PROCEDURE toc IS
   df pls_integer := CAST(DBMS_UTILITY.GET_TIME AS PLS_INTEGER)-v_tic;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('TOC: '||evento||', T(1/100s): '||df);
 END toc;
END crono;
/

El uso del paquete en un procedimiento cualquiera puede ser:

SQL> begin
2     crono.tic('creo tabla');
3     execute immediate 'CREATE TABLE testtable as select * from user_tables';
4     crono.toc;
5     crono.tic('elimino registros');
6     execute immediate 'delete from testtable';
7     crono.toc;
8     crono.tic('hago commit');
9     commit;
10     crono.toc;
11     crono.tic('borro tabla');
12     execute immediate 'drop table testtable';
13     crono.toc;
14 end;
15 /
TIC: creo tabla
TOC: creo tabla, T(1/100s): 34
TIC: elimino registros
TOC: elimino registros, T(1/100s): 5
TIC: hago commit
TOC: hago commit, T(1/100s): 0
TIC: borro tabla
TOC: borro tabla, T(1/100s): 50

Procedimiento PL/SQL terminado correctamente.

Aunque esta experiencia es meramente demostrativa, notar que no pasó un segundo en toda la operación, y sin embargo estamos sacando medidas ilustrativas en cada paso.

Es una solución práctica y disponible para todos los usuarios de la base de datos, solo tengo que dar permisos de ejecución a los desarrolladores o a public. Un sinónimo puede simplificar aún más las llamadas a tic y a toc.

Pese al mínimo costo de salida por pantalla, una desventaja es que el procedimiento tiene que terminar exitosamente para obtener las medidas (dbms_output despliega su salida al final del proceso). Una alternativa para lograr la medición "on the run", es implementar la salida a archivo (utilizando el paquete utl_file), o insertando en una tabla (con una transacción autónoma para no hacer commit sobre la actual).
Estos cambios pueden ser transparentes al usuario, ya que solo hará falta recompilar el body del paquete. Las ventajas están a la vista.

Ver también:
Uso del paquete utl_file

El usuario Scott no existe

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.

Limpiando código DDL

A propósito de Un extractor de DDL en archivos separados, me preguntaron como evitaba exportar las cláusulas de storage en Oracle 9i, únicamente dejando las que especifican los tablespaces.

SQL> create table t (a int);

Tabla creada.

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

CREATE TABLE "LFERNANDEZ"."T"
( "A" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

En la práctica, las cláusulas PCTFREE, PCTUSED, STORAGE, etc, no son útiles durante el desarrollo, sino cuando los objetos son puestos en producción. Es allí donde el detalle en la especificación de storage de los objetos tiene un impacto significativo sobre la performance de los sistemas.

Repasando las opciones disponibles en el paquete dbms_metadata y en particular la función set_transform_param, encontramos que podemos generar el código de asignación de tablespace pasando el valor 'TABLESPACE' como segundo parámetro y TRUE como tercero:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', TRUE);

Desafortunadamente, esta opción es ignorada cuando deshabilitamos todo el STORAGE usando:
dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

No tenemos alternativa entre: o generar todo el storage, o no generarlo en absoluto.

El camino que termina siendo elegido es el de generar todo el código DDL y luego procesar el archivo de salida con algun lenguaje con buenas cualidades para procesamiento de texto, como por ejemplo Perl o AWK.

No se requiere ser experto en AWK para programar un sencillo script que elimine algunas líneas de archivos:

BEGIN {flag=2}
/^ *) PCTFREE/{print ")"; flag=0}
/^ *PCTFREE/{flag=0}
flag==0 && $0~/)$/{flag=1}
flag==2
flag==1{flag=2}

Esta es la idea: uso flags para saber cuándo imprimir y cuándo no, luego tengo dos posibles comienzos con PCTFREE, el primero para tablas comunes y el segundo para el caso de tablas particionadas (las cuales no son antecedidas por un paréntesis).

Cualquier comentario para mejorar la performance de este script será bienvenido!

Ver también
Un extractor de DDL en archivos separados