Friday, September 07, 2007

A little fun with you all...

Ok, this will be a two parter - I'll introduce you to the frustration that is "answer my question".

Part 1 - the question and my response and their initial response.

So, first the question - written in to me as a letter to the editor regarding a column I wrote on using rownum:


I have a question about rownum which is not mentioned.

Lets say that I want to extract a 3 records out of a result where the middle one is the query. For example I have records:

ID Value
1 Hello
2 Hi
3 Wow
4 Shrek
5 Shus
6 What?

And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.

Is there a way to do it in Oracle?

Seems straightforward, concise, clear - and a question I've received numerous times before.  Looks like someone trying to emulate an ISAM library - porting some code to Oracle.  So - I respond:

This presumes that ID is "unique"

select * 
( select *
from t
where id >= (select nvl(max(id),0)
from t
where id < :id)
order by id)
where rownum <= 3;


For example, if you copy all_Objects into a table T and add a unique/primary key constraint on object_id:

select * 
from ( select *
from t where object_id >=
(select nvl(max(object_id),0)
from t
where object_id < :id)
order by object_id)
where rownum <= 3
order by object_id

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 2 0 0
Fetch 2 0.00 0.00 0 5 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 3

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
3 COUNT STOPKEY (cr=7 pr=0 pw=0 time=333 us)
3 VIEW (cr=7 pr=0 pw=0 time=307 us)
3 TABLE ACCESS BY INDEX ROWID T (cr=7 pr=0 pw=0 time=270 us)
3 INDEX RANGE SCAN T_PK (cr=5 pr=0 pw=0 time=282 us)(object id 55414)
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=154 us)
1 FIRST ROW (cr=2 pr=0 pw=0 time=104 us)
1 INDEX RANGE SCAN (MIN/MAX) T_PK (cr=2 pr=0 pw=0 time=85 us)(object id 55414)

Basically, we start by finding the row in 'front' of the one we need in the middle - the select max(id) does that bit for us and then starting with that record - retrieve in sorted order that record and the next two (rownum <= 3). 

A logical extension of the article - pretty efficient approach and answers the question.  (note; I added nvl(max(id),0) - the NVL bit after the fact this morning.... so that it 'works' for the first record as well - presuming that ID is a whole number)


Apparently, it does not - the response was:

Hello Thomas,

Thanks for answering me.

This doesn’t help me, ....

Anyone want to guess why this didn't help them? It can be summarized in a single sentence - which I'll publish later. 



Anonymous Kyle said....

I'm guessing that ID is not unique.

That or the example he gave isn't what he actually wants to do (such as if he actually wants to pass in 4 and get the 3rd, 4th, and 5th results after the values are sorted alphabetically).

Fri Sep 07, 08:09:00 AM EDT  

Anonymous MarkB... said....

My first thought was would the analytic functions LEAD and LAG help?

Something along the lines of

FROM ( SELECT LAG ( l, 1 ) OVER ( ORDER BY l ) row_before
, l
, LEAD ( l, 1 ) OVER ( ORDER BY l ) row_after
WHERE l = 10

Fri Sep 07, 08:17:00 AM EDT  

Anonymous Mark said....

Maybe the real data he is using isn't limited to 6 records and probably isn't ordered in ascending ID's.

Fri Sep 07, 08:27:00 AM EDT  

Blogger Eriks said....

Maybe ID is of type varchar2 or he's not using Oracle at all :-)

Fri Sep 07, 08:32:00 AM EDT  

Anonymous Anonymous said....

The original poster did not clearly define what he meant by "previous" and "next" records - you assumed previous and next in relation to an ordered, unique key (as would I) - perhaps he meant the records inserted immediately before and after the target row based on some other criteria (time?, physical order on disk?)

Fri Sep 07, 09:09:00 AM EDT  

Blogger tnourse said....

My guess would be he aliased rownum as "ID" to begin with, he didn't say this was the table structure, he said this was a result set. Either that or ID is an actual column of varchar2 as a previous poster said...hmmm

Fri Sep 07, 09:30:00 AM EDT  

Blogger Joel Garry said....

It's the ultimate web display paging question?

Fri Sep 07, 09:36:00 AM EDT  

Blogger amol said....

My guess ....... the solution you gave used the famous table "t" and not the one that the poster is using

Fri Sep 07, 09:54:00 AM EDT  

Blogger Robert Vollman said....

Yeah I'm with Kyle ... ID is not unique. :)

But I also agree with Mark, my first instinct would be to point him to LAG and LEAD (or maybe RANK).

Fri Sep 07, 10:09:00 AM EDT  

Anonymous Mark Russell said....

was it the chap who wanted your eyes?

Fri Sep 07, 10:14:00 AM EDT  

Blogger MWrynn said....

"was it the chap who wanted your eyes?"


Fri Sep 07, 10:29:00 AM EDT  

Anonymous Toon Koppelaars said....

Doesn't this query require a "'alter session set "_optimizer_order_by_elimination_enabled" = false'"?

You're assuming the ORDER BY in the inline view isn't elimated...

Fri Sep 07, 10:50:00 AM EDT  

Blogger Thomas Kyte said....

rownum is sufficient to ensure the order by isn't eliminated.

Fri Sep 07, 10:54:00 AM EDT  

Anonymous Toon said....

Hmmm. So the way I should read this documentation:

"If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows"

A = you embed order by clause in a subquery.
B = [you] place the ROWNUM condition in a the top-level query.
C = you can force the ROWNUM condition to be applied after the ordering of the rows.

Is not:
(A and B) imply C
(A and B) imply (C and D)

D = you force the CBO to not eliminate unnecessary order-by's

To me, this documentation doesn't say D.

But let's not start a thread on this... I'm curiously awaiting the second post.

Fri Sep 07, 11:12:00 AM EDT  

Blogger Dominic said....

I'm guessing the table is sparse, and he only wants rows with ID in (:id-1, :id,:id+1)

Fri Sep 07, 11:38:00 AM EDT  

Anonymous Martin said....

There is no table called T in his system? :-)

Fri Sep 07, 12:04:00 PM EDT  

Blogger Alen Oblak said....

He doesn't have an index on the ID column so the query takes too long to execute?

Fri Sep 07, 02:17:00 PM EDT  

Anonymous Gabe said....


D = you force the CBO to not eliminate unnecessary order-by's

But why would the order by be determined to be unnecessary in the first place?

Oh, and I vote for the wrong datatype for ID.

Fri Sep 07, 03:12:00 PM EDT  

Anonymous Anonymous said....

My guess is to read the original question:
'extract a 3 records out of a result where the middle one is the query'

Notice he says 'result', and not 'table'.

He doesn't want 3 records out of a table that uses an ID as a unique key.

He wants prior/specified/post records of any location within the RESULT of another query, using some value ID as a designator into result set.

Tom's answer is only usable if the result is placed into a table first, then queried again.

The problem of this of course, would be that the resulting three records would be different depending on how the original query was ordered, and whether you utilized any activities (joins, unions, etc.) which may include any hidden ordering of the original data. It could be said, that no two executions would result in the same answer then.

However, it is possible if (as stated in a previous comment) that the original ROWNUM id's were tagged along somehow throughout the entire process to ensure the resulting tables weren't out-of-order.

A messy concept to be sure...

Fri Sep 07, 03:13:00 PM EDT  

Anonymous Anonymous said....

How about "I'm running SQL Server 2005."?


Fri Sep 07, 03:44:00 PM EDT  

Blogger Peter K said....

Anonymous said...

How about "I'm running SQL Server 2005."?


Hahaha (LOL, ROTFLMAO, etc).

My guess was with "Anonymous" where the original poster was looking at rownum-1, rownum, rownum+1 type of results.

Fri Sep 07, 04:12:00 PM EDT  

Anonymous Anonymous said....

Dan is right, he's not using Oracle.

Just like the "please show me how to delete dups without using rownum".

Rownum is the hint Tom is giving, because it's specific to Oracle.


Fri Sep 07, 04:14:00 PM EDT  

Anonymous Anonymous said....

Assuming I'm right, you may thank William of Ockam.

And yell at Tom for messing with our heads!



Fri Sep 07, 04:31:00 PM EDT  

Blogger Alberto Dell'Era said....

My "Boccia di Cristallo" divines that He wanted the previous, current and next value, ordered by id.

That is

SQL> select id, lag (value) over(order by (id)) prev, value, lead (value) over (order by (id)) next from t;

---------- --------------- --------------- ---------------
1 Hello Hi
2 Hello Hi Wow
3 Hi Wow Shrek
4 Wow Shrek Shus
5 Shrek Shus What?
6 Shus What?

Fri Sep 07, 05:46:00 PM EDT  

Anonymous Robert said....

Quick Q:
Wasn't it the case under 8i that
ROWNUM is assigned in the LAST step, when the rows are piping out ?

My memory may be wrong -
I think this was what you said the last time you talked about this in Oracle magazine few years ago.

Sat Sep 08, 01:23:00 PM EDT  

Anonymous Jimmy Green said....

Here's my guess:

He has a query that returns a result set of so many rows.
However, he only wants a particular row, and its previous and next rows from the result set.
But he is unable to modify the query for some reason.

He needs a magical solution, and I have it here:

alter system set show_me_only_the_stuff_i_really_want_to_see=true;


alter system set dont_show_me_stuff_i_dont_want_to_see=true;

Mon Sep 17, 09:54:00 AM EDT  

Anonymous Anonymous said....

Very Good article , this article make some interesting points.
Tactical Flashlights
r c helicopter
video game
Tactical Flashlight

Mon Jun 23, 02:49:00 AM EDT  


<< Home