Tuesday, March 23, 2010

WHERE rownum = 1

Or it's evil twin, WHERE rownum < 2

I've seen this a lot over the past few years. When I say a lot, I mean approaching infinity a lot. Well, it feels like it anyway. I'm allowed to exaggerate.

I'm pretty much convinced that this is a bug. I see it and immediately say, WTF?

The only thing I could see it being used for is some sort of EXISTS functionality, like this:
DECLARE
l_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM user_line_of_business
WHERE username = 'KPEDERSEN'
AND rownum < 2;

IF l_count = 1 THEN
do_something;
ELSE
raise_some_error;
END IF;
END;
I have multiple records in the table for KPEDERSEN, I just need to know if one exists. This would add a STOPKEY (command?) to the query plan and force it to...(ah, who am I kidding, I don't know what I am talking about...yet).

BTW, here's the table definition and data if you want to try it out.
CREATE TABLE user_line_of_business
(
username VARCHAR2(30),
line_of_business VARCHAR2(3)
);

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'TEN' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'OSX' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'KPEDERSEN',
'BOL' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'JKURAMOT',
'OSX' );

INSERT INTO user_line_of_business
( username,
line_of_business )
VALUES
( 'JPIWOWAR',
'OSX' );
So the EXISTS functionality is OK. Not great, not something I'd really like to see, but it works.

How about this though?

What if I, say, I was filtering queries based on a users line of business? Just to add, I am using something like APPLICATION CONTEXT to set the variable so it will hold only one value (please, just go with me on this...I know there is a wway around). My point is, the variable only holds one value. I promise, I am not making this scenario up.
DECLARE
l_lob VARCHAR2(5);
BEGIN
SELECT line_of_business
INTO l_lob
FROM user_line_of_business
WHERE username = 'KPEDERSEN'
AND rownum < 2;

dbms_output.put_line( 'l_lob: ' || l_lob );
END;
Which returns TEN. TEN might be the right answer.

What happens if you run that again? Well, you'll probably get the same result.

However, say this table grows and username KPEDERSEN gets more records? Do you think you could guarantee that TEN would be returned each and every time?

The short answer (and the one I am able to provide) is NO. You can't guarantee the order of the rows returned without explicitly putting an ORDER BY clause on there.

WHERE rownum = 1 or WHERE rownum < 2 are the devil (which contains evil).

8 comments:

Jeffrey Kemp said...

ROWNUM is not evil, it's just the victim of a large amount of abuse. I pity it.

I can't count how many times I've seen queries like this:

SELECT o.order_id,
(SELECT c.name
FROM customers c
WHERE c.cust_id = o.cust_id
AND ROWNUM = 1) cn
FROM orders o;

... because customers.cust_id is not unique, so the developer just fixed the "too many rows" "bug" with the "pick a record, any record" method.

oraclenerd said...

You are right of course. I wonder if you could classify with Tom's autonomous transactions and triggers?

It's not so much a bug as it reveals a bug...in the design or data model.

Jeffrey Kemp said...

Same. These things are intended for use in certain situations, and the fact they are so easily abused does not IMO make them "evil".

EAV data models, or empty WHEN OTHERS THEN triggers - now *that's* evil :)

oraclenerd said...

Would you qualify my example above (EXISTS) as one of those uses?

EAV? We've all come up with that idea at one point or another...thankfully I've read enough before hand to actually implement.

WHEN others THEN - I don't like it. 99% of the time you can (or should) know what possible errors will be thrown. Then again, who does exhaustive unit testing?

Jeffrey Kemp said...

No, I often use ROWNUM for quick EXISTS checks. It's also useful for a simple "TOP n" type query - where you sort in a subquery, then apply the STOPKEY optimisation outside of that - which often allows the optimiser to use less memory for sorting.

Noons said...

Using Lewis C's new term:

ROWNUM=1 is not evil, it's neavil.

(I love that word!) ;)

The thing to recall is that in earlier releases of Oracle, ROWNUM=1 and its variations were not guaranteed to return just one row from the inner query.

In many cases, it returned all rows and filtered the result set for just the first one!

Which of course achieved precisely nothing in terms of reducing workload!

Nowadays of course it returns one row only, BEFORE the entire result set is evaluated. Ie: the "stop-key" optimisation works.

Which of course has created a new kind of short-cut for bad design, as described.

I do recall seeing in many instances "select DISTINCT ..." slapped into almost every join to avoid doing due dilligence in one's joins and query predicates.

It's slowly being replaced by this one.

Ah well, I'd rather have the ROWNUM=1 than the awfull ubiquitous DISTINCT!

oraclenerd said...

You had to bring up DISTINCT didn't you?

Another one of these "little" indicators that something is amiss.

STOPKEY = Optimization, that's the word I was looking for.

LewisC said...

I'm with you guys. Definitely neavil. Has use cases but when I see it in code my first reaction is "you don't CARE which record you get back? Why?!?!"

LewisC