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

Question

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;

Answer 1

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;