Re: Why I too don't like the OCP

From: Jeff <jeff_at_work.com>
Date: Wed, 01 May 2002 14:05:25 GMT
Message-ID: <aaosn6$qpk$1@cronkite.cc.uga.edu>

In article <3CCEBCBE.81E0812_at_exesolutions.com>, Daniel Morgan <dmorgan_at_exesolutions.com> wrote:
>Jeff wrote:
>
>> >Unless of course they are asked to go to code reviews or to deal with
> queries
>> > that don't
>> >work because they are presumed to be the experts.
>> >
>> >I met a DBA asked to solve a problem with a query that looked like this:
>> >
>> >SELECT *
>> >FROM mytable
>> >WHERE rownum BETWEEN 10 AND 20;
>> >
>> >Guess what happened?
>> >
>> >Daniel Morgan
>> >
>>
>> Uhhhmmm...
>> He saw rownum wasn't a column in mytable and tried adding it?
>>
>> He added "AND rownum IS NOT NULL" to the WHERE clause, " just in case"?
>>
>> He quit his job, had a sex-change, and became a nun in France?
>>
>> She was furious that you (you SEXIST PIG!!) automatically assumed that SHE
> was
>> a "HE"?
>>
>> She reckoned that rownum is meaningless without the context of a query and
> its
>> resultset?
>>
>> Guessing games are FUN! :-)
>
>Based on your answer, I would suggest that you try the query against any table
> you have with more
>than 20 rows of data in it.
>
>It is an excellent reason why good DBAs must understand more than just the
> straight-forward DBA
>stuff like install, configure, backup, and restore.
>
>Daniel Morgan

I'm not sure what you mean about "my answer," since most of it was obvious humor... or so I thought. :-/

I'm fairly confident of what rownum is, how it works, and what'll happen with this query. Rownum is both a counter AND a logical column in the resultset-- two things referred to by one name--the column is assigned an incrementing value (the counter) starting with 1 at the time the row is selected (before any sorting or grouping). Since there are no rows in the resultset at the beginning of a query, rownum (the column) is both valueless and meaningless and cannot be used this way in the WHERE clause of a query. There will never be a case where rownum (the counter) satisfies the above WHERE clause, since it MUST start with 1, so setting a lower boundary is pointless. Therefore, your query is pointless--it'll always return no rows because it either assumes rownum has meaning that it doesn't (yet) or it checks for something that will never happen.

A way to accomplish what this query seems to want is to give rownum (the column) meaning by doing a sub-query first that doesn't set a lower boundary on rownum (the counter) and then using rownum (the column) for setting the lower boundary in the outer query.

Like so:
 SELECT ...
    FROM (SELECT rownum rn, ...

                   FROM mytable 
                 WHERE rownum <= 20) 

  WHERE rn >= 10;

Does this pass your mustard, Dan???? Received on Wed May 01 2002 - 09:05:25 CDT