Thanks for the question, Thiyanesh.

Asked: September 20, 2016 - 6:58 am UTC

Last updated: September 21, 2016 - 10:09 pm UTC

Version: 11G higher

Viewed 10K+ times! This question is

You Asked

Hi Chris/Connar,

I have been checking on lot of contents in the Internet to find a "Simple Answer" for this and my final resort is AskTom.

For tuning of the our PLSQL programs for our various application we have been using "ROWNUM=1" condition in WHERE clause when we just need to check if the record is available in the system or not.

My question is,

--> Is "ROWNUM=1" always makes a query faster ?
If not can you provide us the scenarios which makes the queries to run slower than the actual SQL without it.

Thanks in Advance.

and Connor said...

rownum=1 is good ... for the times when it makes sense to use it.

For example, we've got x=0 at the "start" and "end" of a table and we want to do an existence check.

SQL> create table t as
  2  select 0 x, rpad('y',100) y
  3  from dual ;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL>
SQL> insert into t
  2  select 0 x, rpad('y',100) y
  3  from dual ;

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set autotrace on stat
SQL> select * from t where x = 0;

         X Y
---------- ----------------------------------------------------------------------------------------------------
         0 y
         0 y


Statistics
----------------------------------------------------------
          2  recursive calls
          1  db block gets
        239  consistent gets
          1  physical reads
        184  redo size
        852  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>
SQL> set autotrace on stat
SQL> select * from t where x = 0 and rownum = 1;

         X Y
---------- ----------------------------------------------------------------------------------------------------
         0 y


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        698  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>

The 'rownum=1' makes it faster because we get to *stop* after the first row.

But, what if having that second occurrence of x=0 is a major problem - something that we should know about because it should never occur. In that case, we *want* the query to return 2 rows (or crash) because something is wrong. Adding 'rownum=1' has in effect hidden that problem from us.

Another place to be careful is in complex queries, because typically any (sub)query with a rownum clause must be resolved before it can merged with other parts of the query. Hence it limits the optimizer choices.

Hope this helps.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clarification....

Shimmy, September 20, 2016 - 7:01 pm UTC

So in PL/SQL when we have a code like the one below, is it better to always put ROWNUM = 1?(assume there is a PRIMARY KEY on emp_no, so we will never get TOO_MANY_ROWS)

DECLARE
   L_emp_name   VARCHAR2(200);
   L_emp_no     NUMBER(10);
BEGIN
   SELECT emp_name
   INTO L_emp_name   
   FROM emp
   WHERE emp_no = L_emp_no;
END;

September 21, 2016 - 10:03 pm UTC

No - for a primary key, we will *know* that there will be one row.

Thiyanesh Kamaraj, September 21, 2016 - 5:23 am UTC

Using EXISTS

Evan, September 21, 2016 - 7:11 pm UTC

I often use EXISTS to test whether a row exists in the database. I select from dual and add an exists condition that tests for the row. I often use this approach in PL/SQL because I can use max and nvl to return a single Y / N value into a variable that indicates if a condition exists.

For example:

DECLARE
v_exists char(1);
BEGIN
SELECT nvl(max('Y'), 'N')
INTO v_exists
FROM dual
WHERE EXISTS
(
SELECT 1
FROM t
WHERE x = 0
);

dbms_output.put_line('v_exists = ' || v_exists);
END;

I'm not sure if this is a good approach or not. Some time ago I wondered what the best approach was for testing whether a row exists and I did some searching on the Internet. From what I found, it seems like using rownum = 1 or exists are the best options whereas using count(*), which a lot of developers use, can cause performance problems, especially if the table has a lot of rows, and should be avoided.

Any thoughts...?

September 21, 2016 - 10:09 pm UTC

They are pretty much equivalent.

I too like the "exists" usage model, because it is like "self-documenting code". I am checking for the existence of something.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Clarification....

Using EXISTS

More to Explore

Performance

Is ROWNUM=1 on queries makes them faster all the time ?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Clarification....

Using EXISTS

More to Explore

Performance