Last page
April 18, 2008 - 8am Central time zone
Reviewer: A reader
What if I want to see the last page order by asc without having to click next many times?
Followup April 18, 2008 - 10am Central time zone:
then you would order by DESC and view the first page
as stated....
very simple.
Last page in ASC not DESC order
April 18, 2008 - 1pm Central time zone
Reviewer: A reader
Let's say my result set is the numbers 1 to 15 and my page size is 5.
If I order by DESC and show the first page I'll see records 15 down to 11 but I want to see them in
ASCENDING order, from 11 to 15 which is the last page in ASC order.
Followup April 18, 2008 - 2pm Central time zone:
and you cannot figure out how to fix that?
you turn the monitor upside down of course!!
first - so what if it is backwards - really, you want the end of the result set, why does it in general MATTER.
and if you cannot figure out how to make the data sort the way you want on screen... hmmmm
select *
from (select a.*, rownum rnum from (YOUR_QUERY_HERE sorted desc)
where rownum <= :MAX_ROW)
where rnum >= :MIN_ROW
and you get min/max = 1/5 (get: 15, 14, 13, 12, 11)
what would happen if you take the above query Q and:
select * from (Q) order by whatever ASC;
you'd now have 11, 12, 13, 14, 15 - and it is pretty cheap to sort 5, 10, 15 rows...
jumping to last (incomplete) page (with usually less rows than page size)
April 23, 2008 - 9am Central time zone
Reviewer: Frank from Berlin, Germany
I have recently discovered a (TKPROF) equivalent query for paging I can easily extend for containing the last_page mark in the first row.
The paging query looks as follows (note that a FIRST_ROWS hint OR a rownum clause is needed for performance (using indexes on the sort column and stop keys just as the standard double outer select would)):
SELECT *
FROM (
SELECT row_number() over (order by SORT_COLUMN) sort,
a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...)
WHERE sort BETWEEN MIN_ROWS AND MAX_ROWS
and rownum<=(MAX_ROWS-MIN_ROWS)+1
ORDER BY sort;
or (equivalent):
SELECT /*+FIRST_ROWS(PAGE_SIZE)*/ *
FROM (
SELECT row_number() over (order by SORT_COLUMN) sort,
a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...)
WHERE sort BETWEEN MIN_ROWS AND MAX_ROWS
ORDER BY sort;
so you can extend that to:
SELECT v.*, case when sort=1 then sum_all-mod(sum_all,PAGE_SIZE)+1 else null end as last_page_start
FROM (
SELECT row_number() over (order by SORT_COLUMN) sort,
sum(1) over (order by SORT_COLUMN desc) sum_all,
a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...) v
WHERE sort BETWEEN MIN_ROWS AND MAX_ROWS
and rownum<=(MAX_ROWS-MIN_ROWS)+1
ORDER BY sort;
making the above query:
SELECT
b.sort, owner, table_name, status, case when sort=1 then sum_all-mod(sum_all,50)+1 else null
end as last_page_start
FROM (
SELECT row_number() over (order by tablespace_name, table_name) sort,
sum(1) over (order by tablespace_name desc, table_name desc) sum_all,
a.owner, a.table_name, a.status
FROM all_tables a
WHERE owner = 'SYS') b
WHERE sort BETWEEN 1 AND 50
and rownum<=(50-1)+1
ORDER BY sort;
Followup April 28, 2008 - 9am Central time zone:
ugh, I've no idea why people want to do the maximum amount of work possible. Can you send me some of your spare CPU, Memory and Disk - you must have a ton....
SELECT /*+FIRST_ROWS(PAGE_SIZE)*/ *
FROM (
SELECT row_number() over (order by SORT_COLUMN) sort,
a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...)
WHERE sort BETWEEN MIN_ROWS AND MAX_ROWS
ORDER BY sort;
two comments:
in order to know "min_rows and max_rows", you had to have counted the rows - that is a huge mistake.
in order to get "sort between" those two, you have to - well, read the entire result set - that is as big of a mistake.
the only 'smart' answer is:
if you WANT THE LAST PAGE, you don't really want the last page. You want the first page after reversing the sort.
Period.
Going to the last page is not useful.
Going to the first page - that is useful
small comment on last review
April 23, 2008 - 9am Central time zone
Reviewer: Frank from Berlin, Germany
you can exchange
sum(1)
by
row_number()
and get the same results.
commenting the last followup
April 29, 2008 - 9am Central time zone
Reviewer: A reader
I agree with you, from a technical perspective. period.
Look at todays internet and you'll find different implementations the users are used to.
commenting your followup:
SELECT /*+FIRST_ROWS(PAGE_SIZE)*/ *
FROM (
SELECT row_number() over (order by SORT_COLUMN) sort,
a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...)
WHERE sort BETWEEN MIN_ROWS AND MAX_ROWS
ORDER BY sort;
is actually of the same cost as
SELECT * FROM (
SELECT * FROM (
SELECT a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...
ORDER BY SORT_COLUMN)
WHERE rownum <= MAX_ROWS )
WHERE rownum >= MIN_ROWS
ORDER BY SORT_COLUMN;
but in my opinion it is
- more readable
- you have more flexibility (you might use rank or whatever window function)
but most importantly and most interesting:
The database does not retrieve the full result set. It uses a window sort stop key and then another stop key for the result (due to the first_rows hint) making the queries equivalent in cost.
I think the min/max was just a misunderstanding. They are just referencing your upper and lower bound for the page you are retrieving. I do not need to count rows before and I agree it would be a mistake.
Followup April 29, 2008 - 10am Central time zone:
yes, if you use row_number() that is fine, they are pretty much the same.
except your second query is very wrong
SELECT * FROM (
SELECT * FROM (
SELECT a.*, b.*, ROWNUM RNUM
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...
ORDER BY SORT_COLUMN)
WHERE rownum <= MAX_ROWS )
WHERE RNUM >= MIN_ROWS
ORDER BY SORT_COLUMN;
rownum does the same thing - about not returning the entire result set (top-n query optimization). that is why with rownum it is IMPORTANT to have two layers of inline view - "where rownum < :x" and then where RNUM (not rownum - you need to alias rownum in there and reuse it, rownum > :Y where Y is greater than zero is NEVER TRUE)
these two cover rownum and analytics in detail for this purpose:
http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Small correction...
April 30, 2008 - 5am Central time zone
Reviewer: A reader
Small correction on
SELECT * FROM (
SELECT * FROM (
SELECT a.*, b.*, ROWNUM RNUM
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...
ORDER BY SORT_COLUMN)
WHERE rownum <= MAX_ROWS )
WHERE RNUM >= MIN_ROWS
ORDER BY SORT_COLUMN;
It should be:
SELECT * FROM (
SELECT x.*, ROWNUM RNUM
FROM (
SELECT a.*, b.*
FROM YOUR_TABLE1 a, YOUR_TABLE2 b, ...
WHERE a.COLUMN1 = b.COLUMN2 ...
ORDER BY SORT_COLUMN) x
WHERE rownum <= MAX_ROWS )
WHERE RNUM >= MIN_ROWS
ORDER BY SORT_COLUMN;
Followup April 30, 2008 - 10am Central time zone:
doh, errata for the errata :)
Correct position of FIRST_ROWS(n) HINT
May 14, 2008 - 8am Central time zone
Reviewer: Sandro from Italy
Where is correct position of FIRST_ROWS(n) hint? At "inner" or "external" select?
I think is correct
select *
from ( select /*+ first_rows(PAGE_SIZE) */
row_number() over (order by SORT_COLUMN) sort,
...
...
...
in place of
select /*+ first_rows(PAGE_SIZE) */ *
from ( select
row_number() over (order by SORT_COLUMN) sort,
...
...
...
It is true?
Followup May 14, 2008 - 3pm Central time zone:
it is how many rows the query will return, so outside block is correct. But it would have a similar effect on the inside as well.
|