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).

Labels: design, oradb, plsql, wtf

  ¶ 3/23/2010 07:36:00 PM