Re: A better way to execute this query?

dewey_at_centerline.com (Devan F. Dewey) writes:
>In article <WHITE.92Oct26125702_at_macaw.titan.tsd.arlut.utexas.edu>,
>white_at_titan.tsd.arlut.utexas.edu (Eric White) wrote:
>>
>> folks, my SQL query evaluation knowledge is a bit rusty. The Select
>> statement below works, but is inefficient. Is there a better method
>> of selecting items between ROWNUM ranges than I do below?
>>
>> SQL> select rownum from tacfire_rx_data_attributes
>> 2 where rxa_retry_count is not null and rownum < 40 minus
>> 3 select rownum from tacfire_rx_data_attributes
>> 4 where rxa_retry_count is not null and rownum < 20;
>select rownum from tacfire_rx_data_attributes
> where rxa_retry_count is not null
> and rownum between 20 and 40;

The problem with 'where rownum between 20 and 40' is that rownum will never have a chance to become 1 because it will never be between 20 and 40. The way you chose (rownum <40 minus rownum<20) works at least, but you are depending on the ORDER of data which is a no-no in SQL. ROWNUM is assigned BEFORE the sort so don't think about adding an ORDER BY clause.

One of the best ways is to create a temporary table  (create table junk as select ...)
and then select the first 20 rows.

>
> Devan F. Dewey | Senior Systems Analyst
> CenterLine Software | dewey_at_centerline.com
> 10 Fawcett Street | "Leme esplain - no dere is
> Cambridge, MA 02138 | too much. Leme sum up."
> -Inigo Montoya

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.