|
Imaginemos que queremos generar ciertos valores aleatorios. A continuación indico las generaciones aleatorias que más uso.
Obtener un listado aleatorio en PL/SQL de alguna tabla
Obtener un único registro aleatorio en PL/SQL de alguna de nuestras tablas.
Crear fecha aleatoria de los últimos 100 días por ejemplo.
Generar número de forma aleatoria.
La siguiente función la he experimentado hace bastante poco. No la conocía y me ha sorprendido. Generar una cadena aleatoriamente.
El primer parámetros tiene el siguiente significado: |
VALORES ALEATORIOS EN PL/SQL. DBMS_RANDOM.VALUE
EXPRESIONES REGULARES EN ORACLE
|
Imaginemos que se quiere saber si en un campo VARCHAR2 tiene valores sólo numéricos. Lo primero que se nos ocurre es:
La función devuelve un varchar para poder ser usada en una select. Esta select me daría los valores no numéricos de una columna:
Pero si usamos EXPRESIONES REGULARES podemos optimizar el código. Podría haber optado por hacer un bloque de pl/sql anónimo, pero he preferido buscar algo más directo y lo he encontrado. Las expresiones regulares están disponibles en la 10g y permiten hacer búsquedas de una manera parecida al like pero más potente. Además de permitir búsquedas, también se pueden hacer potentes replaces y substrs. Por ejemplo, esta select me saca los valores de una columna que no son números enteros:
|
VISTAS MATERIALIZADAS
|
REFRESH FORCE:
Con este método se indica que si es posible se utilice el metodo FAST, y si no el COMPLETE.
|
USO CORRECTO DEL TIPO CLOB
|
Como la mayoría de lenguajes de programación, PL/SQL tiene limitaciones de capacidad en los tipos de variable más típicos. Como ejemplo mencionar el VARCHAR2, que puede almacenar hasta 32K de información. La solución, cuando se va a sobrepasar este límite, es utilizar el tipo de datos CLOB. Entonces, ¿Por qué no nos curamos en salud y utilizamos siempre el tipo de datos CLOB? Vemos un ejemplo típico
Funcionalmente, lo anterior es perfecto, pero el coste es altísimo. La concatenación de CLOB’s es muy costosa en comparación con el resto de operaciones. Así, la utilización de un “buffer” o variable VARCHAR2 en la cual vayamos almacenando los resultados intermedios ahorra más coste. Es decir, es mejor acumular la información en un VARCHAR2 y sólo volcarla en CLOBs cuando sea estrictamente necesario:
|
RECICLADO DE TABLAS ORACLE. RECUPERACIÓN
|
Horror!!! hemos eliminado una tabla de la base de datos que contenía datos importantes.
Antes de buscar información sobre como alistarse en la legión extranjera vamos a realizar una consulta:
Si encontramos nuestra tabla en uno de los registros, no todo está perdido. Podemos recuperarla con la instrucción flashback (la segunda línea recupera la tabla, pero con otro nombre):
|
Problema de codificación en la carga de los XML.
|
A veces ocurre que cuando se trabaja con xml dependiendo de la codificación puede que nuestro código no funcione correctamente. La solución filtrar los datos del xml antes de insertarlos en un campo XMLTYPE de una tabla de la base de datos. Por tanto, para un Insert into TABLA (xxx,xxx,campoXMLTYPE) VALUES('xxx','xxxx',XMLTYPE(getClobDocumento e_directorio,v_ruta||pe_Fichero,null)) Siendo pe_directorio el directorio virtual de base de datos en el que se encuentran los xml. V_ruta es la ruta es la ruta desde el directorio virtual hasta el fichero, si está al mismo nivel no es necesario. Pe_fichero es el nombre del fichero.
|
Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL
|
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:
|
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.
Uso de Rollback Segments por sentencias SELECT
En alguna ocasión algún lector me ha preguntado, con cierta sorpresa, acerca del por qué una sentencia SELECT le fallaba con el mensaje de error "No es posible ampliar el segmento de rollback" ("Unable to extend rollback segment"). La sorpresa proviene del hecho de que son muchos los desarrolladores PL/SQL los que piensan que los segmentos de rollback sólo se utilizan cuando se emplean sentencias PLSQL en las que se modifican o actualizan datos dentro de la base de datos Oracle. Bajo este tipo de pensamiento es normal que, cuando se produce el error mencionado anteriormente al ejecutar una sentencia SELECT, uno se pregunte: ¿utiliza la base de datos Oracle segmentos de rollback al ejecutar sentencias SELECT?
Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback? La pregunta formulada de esta manera seguro que nos ayudará a comprender mejor este artículo, ya que el verbo "utilizar" usado en la primera pregunta no es lo suficientemente específico.
En PL/SQL,todas las sentencias SELECT tienen el potencial de utilizar los datos de rollback o undo (o datos de vuelta atrás). La base de datos Oracle a la hora de procesar cualquier tipo de consulta utiliza los segmentos de rollback para producir conjuntos de resultados de lectura consistente (la lectura consistente es una característica de las bases de datos Oracle que asegura que todos los registros dentro de un conjunto de resultados, cuando son presentados a una aplicación PLSQL o a cualquier otro tipo de aplicación, provengan de un mismo instante de tiempo).
No obstante, este uso de los segmentos de rollback no va a causar nunca por sí solo un error como el antes mencionado (ORA-01650 Unable to extend rollback segment... u ORA-01651 Unable to extend undo segment... ). Sin embargo, sí que puede provocar un error del tipo "ORA-01555 Snapshot too old".
Para que una sentencia SELECT pueda generar los errores ORA-01650 u ORA-01651, es necesario que esté generando segmentos de rollback y los motivos pueden ser los siguientes:
Lo más corriente es que la causa sea que la sentencia SELECT contenga una cláusula FOR UPDATE, cuando esto ocurre la base de datos Oracle bloquea todos los registros necesarios antes de que la sentencia SELECT empiece a devolver resultados, y bloquear un registro en la base de datos Oracle implica modificar un bloque de la base de datos para registrar dicho bloqueo. Por otro lado, cada vez que se modifica un bloque de la base de datos, se necesita generar un undo para esa operación.
Demostrar este hecho es muy sencillo. Creemos una sesión SQL y ejecutemos los siguientes comandos sin que haya ningún otro usuario utilizando la base de datos:
Según queríamos confirmar, podemos ver que la ejecución de la sentencia SELECT FOR UPDATE ha generado 932 bloques de undo (tened en cuenta que nombre_tabla debe ser una tabla que exista en vuestra base de datos Oracle).
Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback?
En PL/SQL,
No obstante, este uso de los segmentos de rollback no va a causar nunca por sí solo un error como el antes mencionado (ORA-01650 Unable to extend rollback segment... u ORA-01651 Unable to extend undo segment... ). Sin embargo, sí que puede provocar un error del tipo "ORA-01555 Snapshot too old".
Para que una sentencia SELECT pueda generar los errores ORA-01650 u ORA-01651, es necesario que esté generando segmentos de rollback y los motivos pueden ser los siguientes:
- La sentencia SELECT contiene la cláusula FOR UPDATE.
- La funcionalidad de auditoría está habilitada.
- La sentencia SELECT invoca a algún tipo de transacción que escribe en la base de datos Oracle.
Lo más corriente es que la causa sea que la sentencia SELECT contenga una cláusula FOR UPDATE, cuando esto ocurre la base de datos Oracle bloquea todos los registros necesarios antes de que la sentencia SELECT empiece a devolver resultados, y bloquear un registro en la base de datos Oracle implica modificar un bloque de la base de datos para registrar dicho bloqueo. Por otro lado, cada vez que se modifica un bloque de la base de datos, se necesita generar un undo para esa operación.
SQL> SELECT used_ublk FROM v$transaction
No rows selected
SQL> BEGIN
2 FOR cursor IN (
3 SELECT * FROM nombre_tabla
4 FOR UPDATE)
5 LOOP null;
6 END LOOP;
7 END;
10 /
PL/SQL procedure successfully completed.
SQL> SELECT used_ublk FROM v$transaction
USED_UBLK
---------
932
SQL> COMMIT;Según queríamos confirmar, podemos ver que la ejecución de la sentencia SELECT FOR UPDATE ha generado 932 bloques de undo (tened en cuenta que nombre_tabla debe ser una tabla que exista en vuestra base de datos Oracle).
Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP (conversión de fechas a caracteres y viceversa)
Pero antes de hablar de estas funciones, resulta conveniente conocer como se puede obtener desde PLSQL el valor de la fecha y el tiempo actual. Seguro que la gran mayoría de vosotros, si sois programadores PL/SQL, ya conocéis la clásica función SYSDATE, función que, sin duda, es la más empleada en este sentido. No obstante, la base de datos Oracle ofrece la posibilidad de utilizar otras funciones que proporcionan diferentes variantes del valor de la fecha y el tiempo actual y que veremos a continuación.
Las funciones PLSQL de las que hablo son:
En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.
En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.
También debéis daros cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.
Conversión de fechas a carácter
De igual manera que con la función TO_CHAR es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.
Veamos algunos ejemplos:
Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).
Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:
En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:
En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.
También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.
Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1
Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052
Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM
Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.
Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012
Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21
Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.
A continuación os dejo un ejemplo con varios usos de la función TO_DATE.
Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.
Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":
Desde el punto de vista de la programación PL/SQL, conviene tener mucha precaución a la hora de utilizar las funciones TO_DATE o TO_TIMESTAMP, sobre todo si se utilizan sin especificar una máscara concreta (es decir, utilizarán la máscara que emplea la base de datos Oracle por defecto), ya que cambios posteriores del parámetro de base de datos NLS_DATE_FORMAT pueden provocar errores en nuestro programa.
Las funciones PLSQL de las que hablo son:
FUNCION SQL ZONA HORARIA TIPO DE DATO DEVUELTO CURRENT_DATE Sesión DATE CURRENT_TIMESTAMP Sesión TIMESTAMP WITH TIME ZONE LOCALTIMESTAMP Sesión TIMESTAMP SYSDATE Servidor de BBDD DATE SYSTIMESTAMP Servidor de BBDD TIMESTAMP WITH TIME ZONE
En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.
BEGIN DBMS_OUTPUT.put_line (SYSDATE); DBMS_OUTPUT.put_line (SYSTIMESTAMP); DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP); END; / 21-FEB-12 21-FEB-12 19.21.17.437000000 AM -05:00 -000000000 00:00:00.437000000
En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.
También debéis daros cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.
Conversión de fechas a carácter
De igual manera que con la función TO_CHAR es posible convertir números a caracteres
Veamos algunos ejemplos:
Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).
BEGIN DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE)); DBMS_OUTPUT.put_line (TO_CHAR (SYSTIMESTAMP)); END; / 21-FEB-12 21-FEB-12 19.21.17.437000000 AM -05:00
Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'Day, DD "de" Month "de" YYYY'));
END;
/
Martes , 21 de Febrero de 2012En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'Day, DDth Month YYYY',
'NLS_DATE_LANGUAGE=English'));
END;
/
Tuesday , 21ST February 2012En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.
BEGIN
DBMS_OUTPUT.put_line (
TO_CHAR (SYSDATE,
'FMDay, DD "de" Month "de" YYYY'));
END;
/
Martes, 21 de Febrero de 2012También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.
Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1
Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052
Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM
Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.
Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012
Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21
Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.
A continuación os dejo un ejemplo con varios usos de la función TO_DATE.
DECLARE
l_date DATE;
BEGIN
l_date := TO_DATE ('21-FEB-2012');
l_date := TO_DATE ('022112', 'MMDDYY');
l_date := TO_DATE ('21-Feb-12', 'DD-Mon-YY HH:MI:SS');
l_date := TO_DATE ('Feb/21/12', 'Mon/DD/YY HH:MI:SS');
l_date := TO_DATE ('Febrero.21.2012', 'Month.DD.YYYY HH:MI:SS');
END ;Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.
Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":
SELECT TO_DATE ('21-02-2012', 'DD-MON-YYYY')
FROM dual;Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL
De vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.
No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.
La consulta SQL era la siguiente:
Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:
En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:
Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.
De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:
El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.
No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.
Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.
No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.
La consulta SQL era la siguiente:
SELECT COUNT(*)
INTO v_count
FROM tabla1 t1, tabla2 t2
WHERE t1.id = t2.id(+)
AND t2.otro_id = v_otro_id;
Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:
SELECT COUNT(*)
INTO v_count
FROM ...;
IF v_count > 0 THEN
ejecutar_algo();
ENF IF;
En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:
ejecutar_algo();
Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.
De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:
SELECT COUNT(*)
INTO v_count
FROM tabla1 t1, tabla2 t2
WHERE t1.id = t2.id
AND t2.otro_id = v_otro_id;
El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.
No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.
Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.
Diferencias entre COUNT(1) y COUNT(*)
Miles y miles y miles de veces veo que se arman consultas SQL que contiene el COUNT(1). Este es un viejo mito. Muchos piensan que colocar COUNT(1) en vez del COUNT(*) mejora la performance de la consulta.... pero en realidad mejora la performance?
Veamos...
Bien, ahora ejecutemos las 2 consultas:
Veamos lo que nos muestra el TKPROF:
Observamos que en las 2 consultas tenemos la misma cantidad de lecturas de bloques (disk, query, current) y el mismo tiempo de elapsed y cpu.
Las 2 consultas son idénticas y no hay un incremento en la performance por utilizar el COUNT(1) en vez del COUNT(*).
Incluso podría poner cualquier cosa en el COUNT... obtendríamos los mismos resultados:
Bien, por último veamos los siguiente. Qué sucede si realizo un COUNT de alguna de las columnas de la tabla? Veamos...
La consulta nos devolvió la cantidad de registros totales de la tabla. Ahora veamos qué me devuelve la consulta si la columna TEXTO tiene valores nulos.
Como podemos observar, el COUNT sobre una columna sólo cuenta la cantidad de valores que no son nulos. Por lo tanto, tenemos que tener cuidado a la hora de realizar un COUNT y tener en cuenta estos pequeños detalles.
Veamos...
SQL_9iR2> CREATE TABLE test AS
2 SELECT level id, 'texto_'||level texto
3 FROM dual
4 CONNECT BY level <= 100000 ; Table created. SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;
PL/SQL procedure successfully completed.
Bien, ahora ejecutemos las 2 consultas:
SQL_9iR2> ALTER SESSION SET SQL_TRACE = TRUE ;
Session altered.
SQL_9iR2> SELECT COUNT(1)
2 FROM test ;
COUNT(1)
----------
100000
1 row selected.
SQL_9iR2> SELECT COUNT(*)
2 FROM test ;
COUNT(*)
----------
100000
1 row selected.
SQL_9iR2> ALTER SESSION SET SQL_TRACE = FALSE ;
Session altered.
Veamos lo que nos muestra el TKPROF:
select count(1)
from
test
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.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1
select count(*)
from
test
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.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1
Observamos que en las 2 consultas tenemos la misma cantidad de lecturas de bloques (disk, query, current) y el mismo tiempo de elapsed y cpu.
Las 2 consultas son idénticas y no hay un incremento en la performance por utilizar el COUNT(1) en vez del COUNT(*).
Incluso podría poner cualquier cosa en el COUNT... obtendríamos los mismos resultados:
SELECT COUNT(2222222)
FROM
TEST
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.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1
SELECT COUNT('EJEMPLO')
FROM
TEST
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.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1
SELECT COUNT(12345)
FROM
TEST
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.01 0.01 0 306 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 306 0 1
Bien, por último veamos los siguiente. Qué sucede si realizo un COUNT de alguna de las columnas de la tabla? Veamos...
SQL_9iR2> SELECT COUNT(texto)
2 FROM test ;
COUNT(TEXTO)
------------
100000
1 row selected.
La consulta nos devolvió la cantidad de registros totales de la tabla. Ahora veamos qué me devuelve la consulta si la columna TEXTO tiene valores nulos.
SQL_9iR2> UPDATE test
2 SET texto = NULL
3 WHERE id > 50000 ;
50000 rows updated.
SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(USER,'TEST') ;
PL/SQL procedure successfully completed.
SQL_9iR2> SELECT COUNT(texto)
2 FROM test ;
COUNT(TEXTO)
------------
50000
1 row selected.
Como podemos observar, el COUNT sobre una columna sólo cuenta la cantidad de valores que no son nulos. Por lo tanto, tenemos que tener cuidado a la hora de realizar un COUNT y tener en cuenta estos pequeños detalles.
Suscribirse a:
Entradas (Atom)