Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Under what conditions does ROWNUM=1 significantly increase performance in an "exists" syle query

I read some of the discussion in this question and thought to myself that in my PL/SQL code I have "exists" style queries all over the place that don't use the ROWNUM=1 optimisation.

The questions I have are:

  1. Does the introduction of ROWNUM=1 significantly increase performance?
  2. If so, under what conditions would performance be particularly improved (e.g lots of joins, constraints on unindexed columns, large tables, large result sets)

I'm trying to determine of it is worth rewriting all of my existing queries to add a ROWNUM=1 optimisation.

The queries I'm thinking of are ones that may have multiple joins and may query large tables. They have the general form of:

SELECT 1
INTO ln_count
FROM table_1, table_2...., table_n
WHERE <various joins and conditions>;

IF ln_count > 0 THEN
  <do stuff>
END IF;

I'm considering changing them to:

SELECT 1
INTO ln_count
FROM table_1, table_2...., table_n
WHERE <various joins and conditions>
AND ROWNUM = 1;

IF <local variable> > 0 THEN
  <do stuff>
END IF;
like image 874
darreljnz Avatar asked Jul 07 '09 01:07

darreljnz


People also ask

What does Rownum 1 do in SQL?

The first row selected has a ROWNUM of 1, the second has 2, and so on. You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.

What is the Rownum clause in SQL?

ROWNUM numbers the records in a result set. The first record that meets the WHERE clause criteria in a SELECT statement is given a row number of 1, and every subsequent record meeting that same criteria increases the row number.

Which is faster in or exists in Oracle?

Exist is more faster than IN because IN doesn't use indexes at the time of fetching but Exist uses Index at the time of fetching.


1 Answers

It does improve performance significantly (tens of percent average) on queries which cannnot be solved by simple single index lookup e.g. table joins. However it has a potential to hide data/application error.

Lets have a table:

create table t (id number(10,0), padding varchar2(1000));  

--intentionally don't use PK to make the example as simple as possible. The padding is used to simulate real data load in each record

with a many records:

insert into t (id, padding)
select rownum, rpad(' ', 1000) from dual connect by level < 10000

Now if you ask something like

select 1 into ll_exists
from t where id = 5;

the DB must go through the whole table whether it found the only matching record in the first data block (which by the way we cannot know because it could be inserted by many different ways) or in the last. That's because it doesn't know that there is only one matching record. On the other hand if you use ... and rownum = 1 than it can stop traversing through data after the record is found because you told it that there is not (or not needed) another matching record.

The drawback is that with the rownum constraint you may get undeterministic results if the data contains more than one possible record. If the query was

select id into ll_id
from t where mod (id, 2) = 1
and rownum = 1;

then I may receive from the DB answer 1 as well as 3 as well as 123 ... order is not guaranteed and this is the consequence. (without the rownum clause I would get a TOO_MANY_ROWS exception. It depends on situation which one is worse)

If you really want query which tests existence then WRITE IT THAT WAY.

begin

select 'It does' 
  into ls_exists
from dual where
exists (your_original_query_without_rownum);

do_something_when_it_does_exist
exception
  when no_data_found then
    do_something_when_it_doesn't_exist
end;
like image 184
Michal Pravda Avatar answered Sep 23 '22 22:09

Michal Pravda