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.

Solution 1

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

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Harish]

Taking column names is not the only issue I also wanted to know why its taking time when we use rownum=1.Also my query is in Oracle

[paxdiablo]

@Harish, your actual requirement seemed to be to get the column names. I provided a way of doing that that sidesteps the performance problem. I know you asked for a way to get the first row, but that's not necessary for what you're trying to do (and it's a horrible waste if, for example, it contains six 2G BLOB columns). If you still want to know why the where clause causes problems, I can't help you unfortunately. I'd prefer to be pragmatic. And the query is for Oracle, it's the Oracle equivalent of the DB2 one I mentioned (which is where my expertise lies).

[Harish]

I want the values of the first row as well.

[Harish]

Ya I doubted so and thanks for confirming so.So whats the solution?

[Jon Heller]

That's only true if there is an ORDER BY. Without an ORDER BY you'll usually see "COUNT STOPKEY" in the explain plan, which means that Oracle may stop after the first row.

[Rob van Laarhoven]

Define 'the first row'. The 'first row'is undefined if you don't use a order by. It can by definition be any row in the table. Maybe you should tell us what the real question is. I see all kind of valuable advise but you keep moving the target.