Re: Limitting result without ROWNUM

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 20 Jan 2007 16:50:25 -0800
Message-ID: <1169340625.884443@bubbleator.drizzle.com>

Charles Hooper wrote:
> DA Morgan wrote:

>> Charles Hooper wrote:
>>> Method #3:
>>> SELECT
>>>   R.ID,
>>>   FIRSTNAME,
>>>   LASTNAME,
>>>   HEIGHT,
>>>   AGE
>>> FROM
>>>   (SELECT
>>>     ID,
>>>     ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION
>>>   FROM
>>>     T1) R,
>>>   T1
>>> WHERE
>>>   R.POSITION<=10
>>>   AND R.ID=T1.ID;
>>>
>>> The results of the above methods look something like this:
>>> ID FIRSTNAME  LASTNAME   HEIGHT     AGE
>>> ---------------------------------------
>>>  2 SUSAN      SMITH          65      20
>>>  3 DOROTHY    SMITH          62      21
>>>  4 JOHN       SMITH          72      35
>>>  5 DAVID      SMITH          73      34
>>>  7 ROBERT     SMITH          76      45
>>> 10 SUSAN      JOHNSON        65.5    20
>>> 11 DOROTHY    JOHNSON        62.5    21
>>> 12 JOHN       JOHNSON        72.5    35
>>> 13 DAVID      JOHNSON        73.5    34
>>> 15 ROBERT     JOHNSON        79      45
>>>
>>> Charles Hooper
>>> PC Support Specialist
>>> K&M Machine-Fabricating, Inc.
>> Impressive but an impressive contrivance. But ROW_NUMBER, ROWNUM, the
>> difference is? ;-)
>>
>> Why not just build a table with ten rows and then use it the way we
>> use dual as a source of a single result?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> Good question, what is the difference between ROW_NUMBER and ROWNUM,
> other than ROWNUM was specifically prohibited, while ROW_NUMBER was
> not? Let's try an experiment:
> SELECT
> ID,
> ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION,
> ROWNUM
> FROM
> T1
> ORDER BY
> HEIGHT DESC;
>
> In the above, I used the analytical function ROW_NUMBER(), which sorted
> by height in descending order, and I requested the pseudo column ROWNUM
> and applied an ORDER BY HEIGHT DESC clause. So, ROW_NUMBER and ROWNUM
> should return the same values, right?
>
> ID POSITION ROWNUM
> ---------- ---------- ----------
> 15 1 15
> 7 2 7
> 13 3 13
> 5 4 5
> 12 5 12
> 4 6 4
> 10 7 10
> 2 8 2
> 11 9 11
> 3 10 3
> 9 11 9
> 1 12 1
> 6 13 6
> 14 14 14
> 16 15 16
> 8 16 8
>
> The above, unexplained result used to cause me much confusion from time
> to time. Until I realized, after reading an article written by Tom
> Kyte in Oracle Magazine, that the ROWNUM is generated before the ORDER
> BY clause takes effect.
>
> To obtain the correct values for ROWNUM, we need to slide the previous
> results into an inline view:
> SELECT
> ID,
> POSITION,
> ROWNUM1,
> ROWNUM ROWNUM2
> FROM
> (SELECT
> ID,
> ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION,
> ROWNUM ROWNUM1
> FROM
> T1
> ORDER BY
> HEIGHT DESC);
>
> ID POSITION ROWNUM1 ROWNUM2
> ---------- ---------- ---------- ----------
> 15 1 15 1
> 7 2 7 2
> 13 3 13 3
> 5 4 5 4
> 12 5 12 5
> 4 6 4 6
> 10 7 10 7
> 2 8 2 8
> 11 9 11 9
> 3 10 3 10
> 9 11 9 11
> 1 12 1 12
> 6 13 6 13
> 14 14 14 14
> 16 15 16 15
> 8 16 8 16
>
> So possibily, the requirement not to use ROWNUM was a request at better
> optimization, by requiring one less inline view to obtain the answer.
> :-)
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Possibly. Though personally I am more inclined to think the instructor needs to be taken to the woodshed. ;-) My take on the exercise is that it was an exercise in contrivance.

Here's the roughly comparable question I gave my students last quarter for comparison:
Rerun the SQL statement for question 7 and obtain an EXPLAIN PLAN using DBMS_XPLAN. Now rewrite the query in another form and run an EXPLAIN PLAN on it also. Submit both EXPLAIN PLANS as well as the SQL and an explanation as to which SQL statement you would choose and why.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jan 20 2007 - 18:50:25 CST