Home » RDBMS Server » Performance Tuning » Limiting the number of hits to 1 with rownum.
Limiting the number of hits to 1 with rownum. [message #64886] Thu, 26 February 2004 01:40 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

At performance level, is it good to limit the number of hits to one when expecting only one match in a SELECT statement?

SELECT ... FROM .... WHERE ... AND rownum = 1;

or

SELECT c_1, c_2 INTO param_1, param_2 FROM ... WHERE ... AND rownum = 1;

We know that only one row will be returned! Can we gain great perfomance from using rownum and limiting the number of hits to one?

Many thanks for your help.

Regards,

Patrick Tahiri.
Re: Limiting the number of hits to 1 with rownum. [message #64888 is a reply to message #64886] Thu, 26 February 2004 03:46 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Yes, it is better to add the "rownum = 1" clause. Nevertheless, don't expect a massive performance improvement.

The test case below shows that we save one "consistent get" with rownum=1:

SQL> create table x (id number);
Table created.

SQL> insert into x values (1);
1 row created.

SQL> insert into x values (2);
1 row created.

SQL> insert into x values (3);
1 row created.

SQL> insert into x values (4);
1 row created.

SQL> insert into x values (5);
1 row created.

SQL> insert into x values (6);
1 row created.

SQL> insert into x values (7);
1 row created.

SQL> insert into x values (8);
1 row created.

SQL> insert into x values (9);
1 row created.

SQL> insert into x values (10);
1 row created.

SQL> commit;
Commit complete.

SQL>  select * from x -- warm-up the cache;
        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
10 rows selected.

SQL> set autotrace on
SQL> <b>select * from x where id = 5 and rownum = 1;</b>

        ID
----------
         5

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'X'

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

SQL> <b>select * from x where id = 5;</b>

        ID
----------
         5

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'X'

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


Best regards.

Frank
Re: Limiting the number of hits to 1 with rownum. [message #64889 is a reply to message #64888] Thu, 26 February 2004 04:01 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you very much!

Regards,

Patrick Tahiri.
Re: Limiting the number of hits to 1 with rownum. [message #64892 is a reply to message #64889] Thu, 26 February 2004 10:57 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Well, if you were expecting only one row to be returned, then you'd have a PK or a Unique index on that column. In that case Oracle optimizes it without the rownum = 1.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

Table dropped.

SQL> create table x (id number);

Table created.

SQL> alter table x add constraint x_pk primary key (id);

Table altered.

SQL> insert into x values (1);

1 row created.

SQL> insert into x values (2);

1 row created.

SQL> insert into x values (3);

1 row created.

SQL> insert into x values (4);

1 row created.

SQL> insert into x values (5);

1 row created.

SQL> insert into x values (6);

1 row created.

SQL> insert into x values (7);

1 row created.

SQL> insert into x values (8);

1 row created.

SQL> insert into x values (9);

1 row created.

SQL> insert into x values (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x -- warm-up the cache;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> set autotrace on
SQL> <b>select * from x where id = 5 and rownum = 1;</b>

        ID
----------
         5

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     INDEX (UNIQUE SCAN) OF 'X_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          <b>1  consistent gets</b>
          0  physical reads
          0  redo size
        199  bytes sent via SQL*Net to client
        204  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> <b>select * from x where id = 5;</b>

        ID
----------
         5

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'X_PK' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          <b>1  consistent gets</b>
          0  physical reads
          0  redo size
        200  bytes sent via SQL*Net to client
        189  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> 
Re: Limiting the number of hits to 1 with rownum. [message #64904 is a reply to message #64892] Sun, 29 February 2004 22:13 Go to previous message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Thank you very much!

Regards,

Patrick Tahiri.
Previous Topic: Stored Procedure - Bind Variables - Performance
Next Topic: how do i read new messages in cursor
Goto Forum:
  


Current Time: Thu Mar 28 14:44:18 CDT 2024