order by + rownum

[Stary Kapec]

Hi,
I wonder if the following SELECT is correct

SELECT *
FROM (SELECT * 
      FROM table_name
      ORDER BY col_name)
WHERE ROWNUM <= 100

The strange thing is that the inner select, alone, executes in 60s
but the whole statement as above in only 2s.
Why is that and is it correct?

[stu derby]

Originally posted by Jasiek Motyka:
Hi,
I wonder if the following SELECT is correct

SELECT *
FROM (SELECT * 
      FROM table_name
      ORDER BY col_name)
WHERE ROWNUM <= 100

The strange thing is that the inner select, alone, executes in 60s
but the whole statement as above in only 2s.
Why is that and is it correct?

You're probably not timing what you think you are.

First, on many databases (particularly Oracle), there are 3 phases to processing a query, known as: parse, execute, fetch

"parse" is the work done for query setup, it is effectively the same for both of your queries.

"execute" is the work done to produce the internal result; it is effectively the same for both of your queries. It is dominated by the logical I/O to produce the result; in this case, the inner query, everything else is insignificant.

"fetch" is the work done to get the internal result and put it on the network for you to get; the nested version is going to be substantially faster because the work gets to stop when the rownum limit is reached.

However, with result sets of any size, one of the biggest factors is the network transfer time; I would guess the difference in network I/O accounts for the majority of the difference in the time.

By the way, with some database (such as Oracle), you can improve your network performance by using setFetchSize() of Statement (or PreparedStatement) to make the driver use bigger buffers (some databases already use really big buffers though, find out what yours is using before you play with this).

[Stary Kapec]

Thanks ;)