Oracle and rownum
Posted by Allan Peda on May 15, 2009
The other day I was looking at the pagination using the rownum pseudo column. Tom Kyte’s Oracle Column here has some useful hints and an excellent explanation of its dynamic nature. When I tried to recreate his query, it occurred to me that the following DQL would be more concise. I’m not sure if it is just as effective performance wise, but it is easier to read:
SQL> select T3.* from (select T2.*, rownum as outer_rownum 2 from (select T1.report_date from mytab T1 order by T1.report_date )T2 )T3 3 where T3.outer_rownum between 3 and 5;
Which reminds me; much as I have a distaste for most things Microsoft®, it seems the Common Table Expressions (CTEs) are an overlooked way to make SQL more readable. It’s almost a secret, but Oracle 10g does supports CTE syntax. Consider the following way of identifying violations of a business rule disallowing the same business unit being assigned to multiple companies (such as a Toyota Jeep being submitted in the same feed as a Chrysler Jeep):
with distinct_tab1_v as (select distinct compid as COMP_ID, unitid as UNIT_ID from tab1) select 'unit id: '|| unit_id || ' comp id: '|| comp_id from distinct_tab1_v where unit_id in ( select unit_id from distinct_tab1_v group by unit_id having count(*) > 1 ) order by comp_id, unit_id;
The meat and potatoes of this query is the isolation of a unique collection of units and businesses, which are not needed elsewhere. Rather than create a view that sits around forever, a CTE accomplishes the same effect without globally modifying the schema.
It might be easier to look at this as three queries chained together. The first orders the select, the second assigns the row numbering, then the third imposes the limits on the desired rows. I rewrote the query using a table expression so as to make it easier to alias each derived select.
The raw data we are querying is as follows:
select * from mytab order by commentary; ID DESCR COMMENTARY ---------- -------------------- ---------- 10 ten azure 5 five blue 4 four green 6 six indigo 9 nine ochre 2 two orange 1 one red 8 eight salmon 7 seven violet 3 three yellow
Selecting a subset from an ordered select is as simple (or as complex) as:
with ordered_sel as ( select id, descr, commentary from mytab order by commentary ) select numbered_sel.* from ( select ordered_sel.*, rownum as rnum from ordered_sel ) numbered_sel where numbered_sel.rnum between 2 and 5 /
Which results in:
ID DESCR COMMENTARY RNUM ---------- -------------------- ---------- ---------- 5 five blue 2 4 four green 3 6 six indigo 4 9 nine ochre 5
Yeah, SQL is weird, but after a while the logic becomes clearer.
Leave a Reply