where rownum=1 query taking time in Oracle

Question

I am trying to execute a query like

select * from tableName where rownum=1

This query is basically to fetch the column names of the table.There are more than million records in the table.When I put the above condition its taking so much time to fetch the first row.Is there any alternate to get the first row.

Answer 1

This question has already been answered, I will just provide an explanation as to why sometimes a filter ROWNUM=1 or ROWNUM <= 1 may result in a long response time.

When encountering a ROWNUM filter (on a single table), the optimizer will produce a FULL SCAN with COUNT STOPKEY. This means that Oracle will start to read rows until it encounters the first N rows (here N=1). A full scan reads blocks from the first extent to the high water mark. Oracle has no way to determine which blocks contain rows and which don't beforehand, all blocks will therefore be read until N rows are found. If the first blocks are empty, it could result in many reads.

Consider the following:

SQL> /* rows will take a lot of space because of the CHAR column */
SQL> create table example (id number, fill char(2000));

Table created

SQL> insert into example 
  2     select rownum, 'x' from all_objects where rownum <= 100000;

100000 rows inserted

SQL> commit;

Commit complete

SQL> delete from example where id <= 99000;

99000 rows deleted

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from example where rownum = 1;

Elapsed: 00:00:05.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=2015)    
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (FULL) OF 'EXAMPLE' (TABLE) (Cost=7 Card=1588 [..])

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      33211  consistent gets
      25901  physical reads
          0  redo size
       2237  bytes sent via SQL*Net to client
        278  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see the number of consistent gets is extremely high (for a single row). This situation could be encountered in some cases where for example, you insert rows with the /*+APPEND*/ hint (thus above high water mark), and you also delete the oldest rows periodically, resulting in a lot of empty space at the beginning of the segment.

Answer 2

Try this:

select * from tableName where rownum<=1

There are some weird ROWNUM bugs, sometimes changing the query very slightly will fix it. I've seen this happen before, but I can't reproduce it.

Here are some discussions of similar issues: http://jonathanlewis.wordpress.com/2008/03/09/cursor_sharing/ and http://forums.oracle.com/forums/thread.jspa?threadID=946740&tstart=1

Answer 3

I think you're slightly missing the concept of ROWNUM - according to Oracle docs: "ROWNUM is a pseudo-column that returns a row's position in a result set. ROWNUM is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause." So it returns ANY row that it consideres #1 in the result set which in your case will contain 1M rows.

You may want to check out a ROWID pseudo-column: http://psoug.org/reference/pseudocols.html

Answer 4

Surely Oracle has meta-data tables that you can use to get column names, like the sysibm.syscolumns table in DB2?

And, after a quick web search, that appears to be the case: see ALL_TAB_COLUMNS.

I'd use those rather than go to the actual table, something like (untested):

SELECT   COLUMN_NAME
FROM     ALL_TAB_COLUMNS
WHERE    TABLE_NAME = "MYTABLE"
ORDER BY COLUMN_NAME;

---

If you are hell-bent on finding out why your query is slow, you should revert to the standard method: asking your DBMS to explain the execution plan of the query for you. For Oracle, see section 9 of this document.

There's a conversation over at Ask Tom - Oracle that seems to suggest the row numbers are created after the select phase, which may mean the query is retrieving all rows anyway. The explain will probably help establish that. If it contains FULL without COUNT STOPKEY, then that may explain the performance.

Beyond that, my knowledge of Oracle specifics diminishes and you will have to analyse the explain further.

Answer 5

I've recently had the same problem you're describing: I want one row from the very large table as a quick, dirty, simple introspection, and "where rownum=1" alone behaves very poorly. Below is a remedy which worked for me.

Select the max() of the first term of some index, and then use it to choose some small fraction of all rows with "rownum=1". Suppose my table has some index on numerical "group-id", and compare this:

 select * from my_table where rownum = 1;
 -- Elapsed: 00:00:23.69

with this:

 select * from my_table where rownum = 1
    and group_id = (select max(group_id) from my_table);
 -- Elapsed: 00:00:00.01

Answer 6

Your query is doing a full table scan and then returning the first row.

Try

SELECT * FROM table WHERE primary_key = primary_key_value;

The first row, particularly as it pertains to ROWNUM, is arbitrarily decided by Oracle. It may not be the same from query to query, unless you provide an ORDER BY clause.

So, picking a primary key value to filter by is as good a method as any to get a single row.