|
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:
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.
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:
|
Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL
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:
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:
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:
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:
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:
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). |
COMO ENVIAR UN CORREO EN PL/SQL
COMO ENVIAR UN CORREO EN PL/SQL
Como hemos dicho en el objetivo de este articulo, cuando el tablespace se esté quedando sin espacio libre, se recibirá un correo.
Vamos a proporcionar un procedimiento para poder enviar un correo.
CREATE OR REPLACE PROCEDURE SEND_MAIL(SENDER IN VARCHAR2, RECIPIENT IN VARCHAR2, SUBJECT IN VARCHAR2, MESSAGE IN VARCHAR2) IS
– SENDER: direccion de correo de quien envia el mail
– RECIPIENT: dirreción de correo a la que va dirigida el mail
– SUBJECT: Es el asunto del correo
– ESSAGE: es el texto del mensaje
mailhost CONSTANT VARCHAR2(30) := ‘mail.server.es’; — servidor de correo , sustituir cadena por una valida
mesg VARCHAR2(1000); — texto del mensaje
mail_conn UTL_SMTP.CONNECTION; — conexion con el servidor smtp
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := ‘Date: ‘ ||
TO_CHAR( SYSDATE, ‘dd Mon yy hh24:mi:ss’ ) || CHR(13) || CHR(10) ||
‘From: <’|| Sender ||’>’ || CHR(13) || CHR(10) ||
‘Subject: ‘|| Subject || CHR(13) || CHR(10)||
‘To: ‘||Recipient || CHR(13) || CHR(10) || ” || CHR(13) || CHR(10) || Message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, Sender);
utl_smtp.rcpt(mail_conn, Recipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004,SQLERRM);
END send_mail;
Sustituir mail.server.es por un servidor smtp valido
UTILIZANDO EL ROWNUM EN MOTORES ORACLE
Limitando el resultado
ROWNUM es una columna mágica que existe en Oracle que genera cierta confusión entre los desarrolladores. Sin embargo, cuando uno aprende qué es y cómo funciona, puede ser extremadamente útil. Se usa principalmente para dos cosas: 1. Para obtener los primeros N registros. Esto es similar a utilizar la cláusula LIMIT, disponible en otras base de datos. 2. Para paginar queries, típico en ambientes stateless como la Web. Veremos cada una de éstas utilidades luego de explicar como funciona ROWNUM.
Como funciona ROWNUM
ROWNUM es una pseudocolumna (no una columna real) que esta disponible en los resultados de los queries. ROWNUM tendra asignados los numero 1,2,3,...,N donde N es el numero de filas en el resultado. Un valor ROWNUM no es asignado permanentemente a una fila (este es un error de concepto comun). Una fila en la tabla NO tiene ningun numero asignado; no se puede pedir que te devuelva la fila 5 de una tabla, no existe tal cosa.
Otra cosa que suele confundir mucho es saber cuando se asigna el ROWNUM. El valor de ROWNUM es asignado a una fila luego de que paso la fase de predicado del query pero antes que el query pase por algun ordenamiento o agregacion. Ademas, un valor ROWNUM es incrementado solamente luego de ser asginado, lo que explica por que el siguiente query no devuelve ninguna fila:
select * from t where ROWNUM > 1
Debido a que ROWNUM > 1 no es verdadero para la primer columna, ROWNUM nunca avanza a 2. Por ello, ningun valor de ROWNUM es mayor que 1. Considere un query con la siguiente estructura:
select ...,ROWNUM from t where group by having order by ;
Lo anterior es procesado de la siguiente manera:
- La clausula FROM/WHERE va primero.
- ROWNUM es asignado e incrementado para cada fila obtenida en el FROM/WHERE.
- El SELECT es aplicado.
- El GROUP BY es aplicado.
- El HAVING es aplicado.
- El ORDER BY es aplicado.
Es por eso que el query expresado de la siguiente manera es casi un error:
select * from emp where ROWNUM <= 5 order by sal desc;
La intención era principalmente obtener las cinco personas mejor pagas en una top-N query. Lo que el query va a retornar verdaderamente son los primeros cinco registros aleatorios ( Los primeros cinco que encuentre ) ordenados por salario. El pseudocódigo procedimental para esta query sería el siguiente:
ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP
Este código obtiene los cinco primeros registros y los ordena. Un query con WHERE ROWNUM = 5 o WHERE ROWNUM > 5 no tiene sentido. Esto es porque un valor ROWNUM se asigna a una fila durante la evaluación del predicado y se incremente solo después de que una fila es obtenida por la cláusula WHERE. Aquí tenemos la versión correcta del query:
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Esta versión ordenará la tabla EMP por salario de manera descendente y retornará los cinco primeros registros que encuentre. La base de datos es lo suficientemente inteligente como para no ordenar realmente el conjunto entero de resultados, pero conceptualmente es lo que sucede.
Procesamiento de Top-N queries con ROWNUM
Un top-N query, generalmente se realiza pensando en alguna consulta compleja, ordenándola y posteriormente recuperando las primeras N filas (top N). ROWNUM tiene una optimización top-N que facilita este tipo de consulta. Puede usar ROWNUM para evitar un ordenamiento masivo de un conjunto muy grande de registros.Primero se mostrará esta idea conceptualmente y después a través de un ejemplo. Suponga que tiene una consulta de esta forma:
select ... from ... where ... order by columns;
Asuma que este query retorna muchos datos: miles, cientos de miles, o más filas. Sin embargo, usted está interesado solamente en los top-N—, por ejemplo, los 10 o 100 primeros. Hay dos formas de hacerlo:
- Hacer que la aplicación cliente corra dicho query y extraiga sólo las primeras N filas.
- Usar el query como una vista inline, y usar ROWNUM para limitar los resultados, como en SELECT * FROM (su_consulta_aqui) WHERE ROWNUM <= N.
La segunda aproximación es por mucho superior a la primera debido a dos razones. La primera es que requiere menos trabajo de parte del cliente, porque la base de datos se ocupan de limitar el resultset. La más importante es el procesamiento especial que puede hacer la base de datos para darle sólo las N primeras filas. Al usar el top-N query, se da a la base de datos una información extra. Usted le ha dicho: "Estoy interesado solamente en obtener N filas; no me importa el resto." Ahora, eso no suena nada del otro mundo hasta que se piensa en el trabajo que implica para el servidor hacer el trabajo de ordenamiento. Demos un recorrido a las dos aproximaciones con un query de muestra:
select * from t order by unindexed_column;
Ahora asuma que T es una tabla grande, con más de un millón de registros, y cada registro es "gordo", digamos, 100 o más bytes.
También asuma que UNINDEXED_COLUMN es, como su nombre lo implica, una columna que no está indexada y asuma que usted está interesado en obtener solamente las 10 primeras filas. La base de datos Oracle realizaría lo siguiente:
1. Un full scan sobre la tabla T. 2. Ordenar T por UNINDEXED_COLUMN. Esto es un ordenamiento completo. 3. Posiblemente sobrepase el área de memoria y necesite hacer swappings de datos temporalmente a disco. 4. Hace un merge con los datos temporalmente bajados a disco para obtener los 10 primeros registros cuando sean solicitados. 5. Limpia los datos temporales cuando se dejan de utilizar.
Ahora, esto es mucho trabajo de E/S. La base de datos Oracle muy probablemente ha copiado toda la tabla en TEMP y la ha borrado, sólo para obtener las 10 primeras filas.
Posteriormente, veamos conceptualmente lo que Oracle puede hacer con un top-N query:
select * from (select * from t order by unindexed_column) where ROWNUM < :N;
En este caso, Oracle hará los siguientes pasos: 1. Un barrido completo sobre T, como en el anterior caso (este paso no se puede omitir). 2. En un arreglo de :N elementos (se presume que están cargados en memoria esta vez), ordene sólo :N filas. Las primeras N filas poblarán este arreglo de filas de manera ordenada. Cuando las primeras N+1 filas son traídas, se compararán con la última fila en el arreglo. Si llega hasta la posición N+1 en el arreglo, ésta se descarta. De otra forma, se adiciona al arreglo y se ordena y una de las filas que estaba en el arreglo se descarta. El área ordenada almacena N filas máximo, así, en vez de ordenar un millón, se ordenan N filas. Este aparentemente insignificante detalle de usar el concepto de arreglo y ordenar sólo N filas puede llevar a grandes ganancias en performance y uso de recursos. Requiere mucha menos RAM ordenar 10 filas que lo que toma ordenar un millón de filas ( sin mencionar el uso de espacio en TEMP ).
Usando la siguiente tabla T, puede ver que aunque ambas aproximaciones obtienen los mismos resultados, usan cantidades radicalmente diferentes de recursos:
create table t
as
select dbms_random.value(1,1000000)
id, rpad('*',40,'*' ) data from dual
connect by level <= 100000;
begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/
Ahora, habilitando tracing a través de:
exec dbms_monitor.session_trace_enable (waits=>true);
Ejecute su top-N query con ROWNUM:
select * from (select * from t order by id) where rownum <= 10;
Y finalmente ejecute una consulta "do-it-yourself" que extraiga solo los 10 primeros registros:
declare
cursor c is
select *
from t
order by id;
l_rec c%rowtype;
begin
open c;
for i in 1 .. 10
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/
Después de ejecutar esta consulta, puede usar TKPROF para formatear el archive de trace resultante y verificar lo que sucedió. Primero, examine el top-N query, como se muestra en el Listado de código 1.
Listado de código 1: Top-N query usando ROWNUM select * from (select * from t order by id) where rownum <= 10
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 2 0.04 0.04 0 949 0 10 -------- -------- ------- ------- ------- -------- -------- ------ total 4 0.04 0.04 0 949 0 10 Rows Row Source Operation ----------------- --------------------------------------------------- 10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us) 10 VIEW (cr=949 pr=0 pw=0 time=46979 us) 10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us) 100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)
El query leyó toda la tabla ( porque debía hacerlo ), pero usando el paso SORT ORDER BY STOPKEY, pudo limitar el uso de espacio temporal a solamente 10 filas. Note que la línea final del resultado en Row Source Operation muestra que la consulta hizo 949 I/Os lógicos, en total (cr=949), no hizo lecturas o escrituras físicas (pr=0 and pw=0), y tomó 400066 millonésimas de segundo (0.04 segundos). Compare eso contra la alternativa “do-it-yourself” mostrada en el Listado de Código 2.
Listado de Código 2: Consulta Do-it-yourself con ROWNUM SELECT * FROM T ORDER BY ID
call count cpu elapsed disk query current rows -------- -------- ------- ------- ------- -------- -------- ------ Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 10 0.35 0.40 155 949 6 10 -------- -------- ------- ------- ------- -------- -------- ------ total 13 0.36 0.40 155 949 6 10 Rows Row Source Operation ----------------- --------------------------------------------------- 10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us) 100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)
Los tiempos transcurridos incluyen esperar por los siguientes eventos:
Event waited on Times ------------------------------ ------------ direct path write temp 33 direct path read temp 5
Como podemos ver, este resultado es muy diferente. Los tiempos de CPU son significativamente mayores, y las líneas resultantes del Row Source Operation dan una muestra de ello. Acá se ha realizado un ordenamiento en disco, el cual se puede ver con el parámetro pw=891 (physical writes). Su consulta realizó algunas lecturas y escrituras directas – el ordenamiento de los 100,000 registros (en vez de hacer los 10 que finalmente son los que nos interesan) se realizó en disco— adicionando un uso considerable a recursos de ejecución de su consulta.
Queries paginados con ROWNUM
Otro uso de ROWNUM es la paginación. En este caso, se usa ROWNUM para obtener N filas a través de M en un result set. La estructura general es la siguiente:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
donde:
- FIRST_ROWS(N) le dice al optimizador, "Che, estoy interesado en obtener las primeras filas, y obtendré N de ellas tan rápido como sea posible."
- :MAX_ROW_TO_FETCH se coloca para indicar la última fila del result set a traer – si usted quería traer 50 a 60 filas del result set, debe ponerlo en 60.
- :MIN_ROW_TO_FETCH se coloca para indicar la primera fila del result set a traer, así, para obtener las filas 50 a 60, debe ponerlo en 50.
El concepto detrás de este escenario es que un usuario con un browser ha hecho una búsqueda y está esperando por los resultados. Es imperativo retornar la primera página resultante ( y la segunda, y así sucesivamente ) tan rápido como sea posible. Si se fija más detenidamente en la consulta, notará que incorpora un top-N query (obtiene las primeras :MAX_ROW_TO_FETCH filas de su consulta ) y en consecuencia se beneficia de la optimización top-N. Yendo más allá, retorna al cliente, sólo los resultados específicos de interés — remueve cualquier fila anterior que no sea de interés. Un detalle importante sobre el uso de consultas paginadas es que la cláusula ORDER BY debe ordenar por algún elemento único. Si lo que está ordenando no es único, debe adicionar algo al final del ORDER BY para hacerlo así. Si ordena 100 registros 100 por la columna SALARY, por ejemplo, y todos ellos tienen el mismo valor de SALARY, entonces, pedir las filas de la 20 a 25 realmente no tiene ningún sentido. Para ver esto, use una pequeña tabla con muchos valores de ID duplicados:
SQL> create table t 2 as 3 select mod(level,5) id, trunc(dbms_random.value(1,100)) data 4 from dual 5 connect by level <= 10000; Table created.
Después, consulte filas de la 148 a la 150 y 151 después de ordenar por la columna ID:
SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id) a 8 where rownum <= 150 9 ) 10 where rnum >= 148;
ID DATA RNUM ------- ---------- ----------- 0 38 148 0 64 149 0 53 150
SQL> SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id) a 8 where rownum <= 151 9 ) 10 where rnum >= 148;
ID DATA RNUM ------- ---------- ----------- 0 59 148 0 38 149 0 64 150 0 53 151
Note que en este caso, que una vez para la fila 148, el resultado retornó DATA=38, y que la próxima vez, el resultado retornó DATA=59. Ambas consultas están retornando exactamente la respuesta correcta, dado que ha pedido: Ordene los datos por ID, lance las primeras 147 filas, y retorne las siguientes 3 a 4 filas. Ambos lo hacen, pero como el ID tiene tantos valores duplicados, la consulta no puede hacerlo determinísticamente – el mismo ordenamiento no se asegura de ejecución en ejecución para la consulta. Con el propósito de corregir esto, necesita adicionar algo único al ORDER BY. En este caso, use ROWID:
SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id, rowid) a 8 where rownum <= 150 9 ) 10 where rnum >= 148;
ID DATA RNUM ------- ---------- ----------- 0 45 148 0 99 149 0 41 150
SQL> SQL> select * 2 from 3 (select a.*, rownum rnum 4 from 5 (select id, data 6 from t 7 order by id, rowid) a 8 where rownum <= 151 9 ) 10 where rnum >= 148;
ID DATA RNUM ------- ---------- ----------- 0 45 148 0 99 149 0 41 150 0 45 151
Ahora el query es deterministico. ROWID es unico dentro de la tabla, por lo que si se usa ORDER BY ID y luego dentro de ID se usa ORDER BY ROWID, las filas tendran un orden deterministico y el query de paginacion devolvera deterministicamente las filas como se espera.
CONCLUSIONES SOBRE ROWNUM
Con lo anterior, podemos deducir lo siguiente de ROWNUM:
- Como ROWNUM es asignado, para que puedan escribirse queries libre de errores.
- Como afecta el procesamiento de un query, para que puedan paginar los resultados en las aplicaciones.
- Como puede reducir el tiempo de ejecucion de un query, para que los queries top-N no consuman demasiado recursos y puedan ejecutar mucho mas rapido.