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:
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;
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With