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.
No hay comentarios:
Publicar un comentario