| Limiting the number of hits to 1 with rownum. [message #64886] |
Thu, 26 February 2004 01:40  |
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  |
Frank Naude
Messages: 4590
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 #64892 is a reply to message #64889] |
Thu, 26 February 2004 10:57 |
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
