Geek Answers Handbook

Under what conditions does ROWNUM = 1 significantly increase performance in a syle request?

I read part of the discussion on this issue and thought to myself that in my PL / SQL code I have "existing" style queries in the whole place where there isn’t. Do not use the ROWNUM = 1 optimization.

I have the following questions:

  • Implementing ROWNUM = 1 significantly improves performance?
  • If so, under what conditions will performance improve especially (for example, many joins, restrictions on raw columns, large tables, large result sets)

I am trying to determine that it is worth rewriting all my existing queries in order to add the optimization ROWNUM = 1.

The queries I think of are those that can have multiple joins and can query large tables. They have a general view:

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 am considering changing them:

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;
+5
source share
6 answers

This significantly improves productivity (by tens of percent on average) for queries that cannot be resolved by simply searching for one index, for example. table. However, it may hide the data / application error.

Lets have a table:

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

- intentionally not use a PC to make the example as simple as possible. The strip is used to simulate a real data load in each record.

with many entries:

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;

, (, , , ) . , , . , ... rownum = 1, , , , ( ) .

, rownum , .

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

DB 1, 3, 123... , . ( rownum TOO_MANY_ROWS. , )

, , .

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;
+7

, , , . , , , , .

:

" , , 97% : - ".

+5

, . , , , , , ROWNUM = 1.

, ?

, , ;)

: , . , rownum = 1, rownum = 1 , . , , .

+2

, , - :

begin

  select count(*)
    into ls_exists
    from dual
    where exists (select null from ... where ...);

  if ls_exists = 1 then
    do_something;
  else
    do_something_else;
  end if;

end;

1 0. .

SELECT NULL. ; , Oracle . SELECT, , Oracle , , . , , , .

SELECT NULL (), EXISTS , , .

+1

COUNT (1), Oracle , .

SELECT COUNT(1) FROM ....

1 , Oracle , .

SELECT 1 FROM ... WHERE ROWNUM = 1

- , . Oracle , 1 203 499 , ? , . . , .

, : * , , , * Oracle HASH JOIN NESTED LOOP,

0

ROWNUM = 1 ?

. , , Oracle, "ROWNUM = 1". , Oracle , .

COUNT(), , , - . ROWNUM = 1, , .

, (, , , , )

, ROWNUM = 1 . , ROWNUM = 1 -, , , .

0

More articles:


All Articles