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.