Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Limitting result without ROWNUM

4 views
Skip to first unread message

Matthias Matker

unread,
Jan 19, 2007, 3:41:21 PM1/19/07
to
Hello together,

I have to limit the number of results without using ROWNUM or something
like that. One hint was to use "JOIN".

I have to select the 10 biggest persons from a table "persons"

id, firstname, lastname, size, age


WITHOUT using ROWNUM or "limitter" like this.

Do you have any idea?

Thanks a lot.

Vladimir M. Zakharychev

unread,
Jan 19, 2007, 3:59:08 PM1/19/07
to

Strange requirement - care to explain why? I'd suggest using RANK() or
DENSE_RANK() analytic functions for this, but you might consider them
"limiters", too, so... How about just fetching the first 10 rows of the
result set and ignoring the rest?

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Matthias Matker

unread,
Jan 19, 2007, 4:07:31 PM1/19/07
to
Vladimir M. Zakharychev schrieb:

>> I have to limit the number of results without using ROWNUM or something
>> like that. One hint was to use "JOIN".
>>
>> I have to select the 10 biggest persons from a table "persons"
>>
>> id, firstname, lastname, size, age
>>
>>
>> WITHOUT using ROWNUM or "limitter" like this.
>>
>> Do you have any idea?
>>
>> Thanks a lot.
>
> Strange requirement - care to explain why?

It forbidden to use such "limiters", we ( students) should do this via a
kind of "JOIN". We MUST do so.
I have tried it for hours...

> I'd suggest using RANK() or
> DENSE_RANK() analytic functions for this, but you might consider them
> "limiters", too, so...

That's right ;-)

> How about just fetching the first 10 rows of the
> result set and ignoring the rest?

Cannot act like this.

Thanks for your quick reply.

Mark D Powell

unread,
Jan 19, 2007, 7:24:06 PM1/19/07
to

You can use a subquery to find the max(value) where the value selected
is not equal to the max(value) on the same table. This will give you
the second highest value. If you were to nest the subquery repeatedly
you can find the Nth highest value. Then you could just select the
rows where size >= the sub-selected 10th value. This is a horrible way
to write a query.

Learning assignments with restrictions like this are misguided at best
and in my opinion do not promote learning how to solve relational
database information retrieval problems efficiently.

HTH -- Mark D Powell --

DA Morgan

unread,
Jan 19, 2007, 9:06:37 PM1/19/07
to

Giving students problems that have no bearing on the real-world is a
huge waste of time.

Perhaps your instructor would like to contact me. ;-)
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Charles Hooper

unread,
Jan 19, 2007, 10:49:18 PM1/19/07
to

OK, if I understand:
* No ROWNUM may be used
* Hint is that we should use a join

I agree with Daniel Morgan, and others who responded - ask the
instructor, what is the business case for such a request.

Can it be done? Maybe, let's do some setup:

CREATE TABLE T1 (
ID NUMBER(10),
FIRSTNAME VARCHAR2(20),
LASTNAME VARCHAR2(20),
HEIGHT NUMBER(10,2),
AGE NUMBER(10));

INSERT INTO T1 VALUES (1,'MARY','SMITH',60,15);
INSERT INTO T1 VALUES (2,'SUSAN','SMITH',65,20);
INSERT INTO T1 VALUES (3,'DOROTHY','SMITH',62,21);
INSERT INTO T1 VALUES (4,'JOHN','SMITH',72,35);
INSERT INTO T1 VALUES (5,'DAVID','SMITH',73,34);
INSERT INTO T1 VALUES (6,'JOSEPH','SMITH',59,13);
INSERT INTO T1 VALUES (7,'ROBERT','SMITH',76,45);
INSERT INTO T1 VALUES (8,'JAMES','SMITH',48,8);

INSERT INTO T1 VALUES (9,'MARY','JOHNSON',60.5,15);
INSERT INTO T1 VALUES (10,'SUSAN','JOHNSON',65.5,20);
INSERT INTO T1 VALUES (11,'DOROTHY','JOHNSON',62.5,21);
INSERT INTO T1 VALUES (12,'JOHN','JOHNSON',72.5,35);
INSERT INTO T1 VALUES (13,'DAVID','JOHNSON',73.5,34);
INSERT INTO T1 VALUES (14,'JOSEPH','JOHNSON',50,13);
INSERT INTO T1 VALUES (15,'ROBERT','JOHNSON',79,45);
INSERT INTO T1 VALUES (16,'JAMES','JOHNSON',48.5,8);

COMMIT;

First experiment:
SELECT
MIN(HEIGHT) OVER (ORDER BY HEIGHT DESC) MIN_HEIGHT
FROM
T1;

MIN_HEIGHT
----------
79
76
73.5
73
72.5
72
65.5
65
62.5
62
60.5
60
59
50
48.5
48

Interesting, useful?

Method #1:
SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT
LAG(ID,1,-1) OVER (ORDER BY HEIGHT DESC) SIGNAL,
HEIGHT END_HEIGHT,
LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) START_HEIGHT
FROM
T1) R,
T1
WHERE
R.SIGNAL=-1
AND T1.HEIGHT BETWEEN R.START_HEIGHT AND R.END_HEIGHT;

Method #2:
SELECT
T1.ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT
ID,
PERCENT_RANK() OVER (ORDER BY T1.HEIGHT DESC) POSITION,
TR.TOTAL_ROWS
FROM
(SELECT
COUNT(*) TOTAL_ROWS
FROM
T1) TR,
T1) TR,
T1
WHERE
TR.POSITION<=(10/TR.TOTAL_ROWS)
AND TR.ID=T1.ID;

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

Are you not permitted to use inline views and analytical functions too?
There must be additional ways to complete this task.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Frank van Bortel

unread,
Jan 20, 2007, 5:40:53 AM1/20/07
to
Charles Hooper schreef:

> First experiment:
> SELECT
> MIN(HEIGHT) OVER (ORDER BY HEIGHT DESC) MIN_HEIGHT
> FROM
> T1;
>
> MIN_HEIGHT
> ----------
> 79
> 76
> 73.5
> 73
> 72.5
> 72
> 65.5
> 65
> 62.5
> 62
> 60.5
> 60
> 59
> 50
> 48.5
> 48
>
> Interesting, useful?
>

What's this - all midgets?
Could the world at least adhere to the use of standards?
Meter would be a fine example, and in case of body length, the
1/100th part of that, the centimeter: cm.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Charles Hooper

unread,
Jan 20, 2007, 7:05:00 AM1/20/07
to

Is the length of one's big toe not a standard? It would assure
equality in measurement for infants, senior citizens, and midgets. :-)

OK, now to develop the solution for a translation system. In Google
search for:
79 inches in cm

One hit: 79 inches = 200.66 centimeters

Oracle provides a means of requesting web page content. Write a SQL
statement that queries Google to translate the big toe measurement to
an equality statement; it is not permitted to simply multiply the big
toe measurements by 2.54. That sounds like a decent request for the
instructor who asked the students to develop a completely unscalable
solution to a problem that could have been handled quite efficiently
using ROWNUM.

Frank van Bortel

unread,
Jan 20, 2007, 7:56:33 AM1/20/07
to
Charles Hooper schreef:

Your toe or mine ?
What about hammer-toes?

>
> OK, now to develop the solution for a translation system. In Google
> search for:
> 79 inches in cm

Ah - it was in inches - not centimeters!


>
> One hit: 79 inches = 200.66 centimeters

Giants, I'd say, but I'm only 1.77. Meters, that is :)

>
> Oracle provides a means of requesting web page content. Write a SQL
> statement that queries Google to translate the big toe measurement to
> an equality statement; it is not permitted to simply multiply the big
> toe measurements by 2.54. That sounds like a decent request for the
> instructor who asked the students to develop a completely unscalable
> solution to a problem that could have been handled quite efficiently
> using ROWNUM.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

And shows how to manipulate html, using pl/sql, and the capabilities
of the rdbms as data retrieval system, and the use of google which is
always beneficial for students.

But I'll make one statement in favor of the instructor: he was thinking
outside the box. Or at least, trying to get his students, this ng, in
fact the whole world to look at things differently.
And that is what -in my book- is education is all about.

There's more than one way to Rome.
It's not better, it's not worse, it's just different (thanks, YFUNL/Bob)

Charles Hooper

unread,
Jan 20, 2007, 8:24:06 AM1/20/07
to

Method #4:


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT

ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE,
(FIRST_VALUE(HEIGHT) OVER (ORDER BY HEIGHT DESC))*HEIGHT MY_PROD,
MAX_PROD
FROM
(SELECT
MAX(PROD) MAX_PROD
FROM
(SELECT
HEIGHT*LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) PROD
FROM
T1)),
T1)
WHERE
MY_PROD>=MAX_PROD;

Method #5:


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM

T1
MINUS


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT

RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1)
ROW_PERCENT,
(COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1)
CUT_OFF,


ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM

T1)
WHERE
ROW_PERCENT<=CUT_OFF;

Method #6:


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT

RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1)
ROW_PERCENT,
(COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1)
CUT_OFF,


ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM

T1)
WHERE
ROW_PERCENT>CUT_OFF;

Method #7:
SELECT
T1.ID,
T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT,
T1.AGE
FROM
(SELECT
MAX(COUNTER) COUNTER
FROM
(SELECT
LEVEL COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=10)) C,
(SELECT
RANK() OVER (ORDER BY HEIGHT DESC) RANKING,


ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM

T1) T1
WHERE
T1.RANKING<=C.COUNTER;

Method #8:


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
(SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM

T1
ORDER BY
HEIGHT DESC)
WHERE
ROWNUM<=10;

I guess the one above is disqualified, since it uses ROWNUM and is
reasonably efficient.

Charles Hooper

unread,
Jan 20, 2007, 9:11:14 AM1/20/07
to
Frank van Bortel wrote:
> Charles Hooper schreef:
> > Oracle provides a means of requesting web page content. Write a SQL
> > statement that queries Google to translate the big toe measurement to
> > an equality statement; it is not permitted to simply multiply the big
> > toe measurements by 2.54. That sounds like a decent request for the
> > instructor who asked the students to develop a completely unscalable
> > solution to a problem that could have been handled quite efficiently
> > using ROWNUM.
> >
>
> And shows how to manipulate html, using pl/sql, and the capabilities
> of the rdbms as data retrieval system, and the use of google which is
> always beneficial for students.
>
> But I'll make one statement in favor of the instructor: he was thinking
> outside the box. Or at least, trying to get his students, this ng, in
> fact the whole world to look at things differently.
> And that is what -in my book- is education is all about.
>
> There's more than one way to Rome.
> It's not better, it's not worse, it's just different (thanks, YFUNL/Bob)
> --
> Regards,
> Frank van Bortel

I think that your are right on target Frank with what the instructor
was attempting to accomplish.

I am beginning to think that Daniel Morgan's comment in this thread was
meant to provoke thinking outside the box in order to show how
inefficient the other methods are compared with the simple method using
ROWNUM, while in the process, discovering the benefits of analytical
functions when solving complex problems. If that was the case, it was
quite clever.

Gerard H. Pille

unread,
Jan 20, 2007, 10:00:48 AM1/20/07
to

select * from persons
where size >= (
select max(size) from persons c1
where 10 > (select count(*) from persons c2 where c2.size > c1.size)
)

I'm trying to select those persons whose height is greater or equal to the height of a person
that does not have 10 persons that are bigger.

This may result in more than 10 rows, if the tenth rank has some persons of equal height.

Matthias Matker

unread,
Jan 20, 2007, 10:57:17 AM1/20/07
to
Matthias Matker schrieb:

> I have to limit the number of results without using ROWNUM or something
> like that. One hint was to use "JOIN".
>
> I have to select the 10 biggest persons from a table "persons"
>
> id, firstname, lastname, size, age

Thank you for all your replies.

I will check them soon. ;-)

Matze

Charles Hooper

unread,
Jan 20, 2007, 11:06:19 AM1/20/07
to

It looks like you supplied another method, but it only returned one row
when applied to the sample data that I provided. I modified your SQL
statement as follows to work with my sample data:
select * from T1
where HEIGHT >= (
select max(HEIGHT) from T1 c1
where 10 > (select count(*) from T1 c2 where c2.HEIGHT >
c1.HEIGHT)
);


Method #9:


SELECT
T1.ID,
T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT,
T1.AGE
FROM
(SELECT

ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE,

COUNT(*) OVER (ORDER BY HEIGHT DESC) POSITION
FROM
T1) T1,


(SELECT
LEVEL COUNTER
FROM
DUAL
CONNECT BY

LEVEL<=10) C
WHERE
T1.POSITION=C.COUNTER;

Method #10:


SELECT
ID,
FIRSTNAME,
LASTNAME,
HEIGHT,
AGE
FROM
T1

WHERE
HEIGHT>
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT <
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT <
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT <
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT <
(SELECT
MAX(HEIGHT)
FROM
T1
WHERE
HEIGHT<
(SELECT
MAX(HEIGHT)
FROM
T1
)))))))))));

It is starting to look like the instructor asked a very open ended
question.

Dieter Noeth

unread,
Jan 20, 2007, 11:29:32 AM1/20/07
to
Matthias Matker wrote:

> I have to limit the number of results without using ROWNUM or something
> like that. One hint was to use "JOIN".
>
> I have to select the 10 biggest persons from a table "persons"

As nobody used a "JOIN" yet, here it is:

SELECT t1.*
FROM t1 JOIN t1 AS t2 ON t1.HEIGHT <= t2.HEIGHT
GROUP BY t1.ID, t1.FIRSTNAME,t1.LASTNAME, t1.HEIGHT, t1.AGE
HAVING COUNT(*) <= 10

Don't ask about performance...

Dieter

DA Morgan

unread,
Jan 20, 2007, 1:11:06 PM1/20/07
to

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?

Charles Hooper

unread,
Jan 20, 2007, 5:37:13 PM1/20/07
to
DA Morgan wrote:
> > 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
> damo...@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.
:-)

DA Morgan

unread,
Jan 20, 2007, 7:50:25 PM1/20/07
to

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.

Gerard H. Pille

unread,
Jan 22, 2007, 3:49:49 AM1/22/07
to
Charles Hooper schreef:

OMG, my bad!

select * from T1
where height >= (
select MIN(height) from T1 c1
where 10 > (select count(*) from T1 c2 where c2.height >
c1.height)
);

I'm trying to select those persons whose height is greater or equal to

the height of THE SMALLEST person that does not have 10 persons that
are bigger.

Charles Hooper

unread,
Jan 22, 2007, 6:15:59 AM1/22/07
to

Nice solution - one that I had not considered. Above is the 11th
solution provided to this problem.

Dieter Noeth provided solution #12 (I had to change "t1 AS t2" to "t1
t2" before it would execute)

Method #13 (loosely inspired by the above SQL statement):


SELECT
T1.ID,
T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT,
T1.AGE
FROM

T1,
(SELECT
T1.HEIGHT,
COUNT(*) RANKING
FROM
T1,
T1 T2
WHERE
T1.HEIGHT<=T2.HEIGHT
GROUP BY
T1.HEIGHT
HAVING
COUNT(*) BETWEEN 1 AND 10) T2
WHERE
T1.HEIGHT=T2.HEIGHT;

Method #14 (a slight modification of Method #13):


SELECT
T1.ID,
T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT,
T1.AGE
FROM

T1,
(SELECT
T1.HEIGHT,
COUNT(*) OVER (PARTITION BY 1) TOTAL_COUNT,
COUNT(*) RANKING
FROM
T1,
T1 T2
WHERE
T1.HEIGHT>T2.HEIGHT
GROUP BY
T1.HEIGHT) T2
WHERE
(T2.TOTAL_COUNT-T2.RANKING+1) BETWEEN 1 AND 10
AND T2.HEIGHT=T1.HEIGHT;

Gerard H. Pille

unread,
Jan 22, 2007, 6:57:24 AM1/22/07
to

Charles Hooper schreef:

>
> Nice solution - one that I had not considered. Above is the 11th
> solution provided to this problem.
>
> Dieter Noeth provided solution #12 (I had to change "t1 AS t2" to "t1
> t2" before it would execute)
>
> Method #13 (loosely inspired by the above SQL statement):

> Charles Hooper


> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Come on Charles, don't keep us waiting: trace them and let us know
which one provides the best performance. Adding in index on size would
probably help mine.

Charles Hooper

unread,
Jan 22, 2007, 7:38:51 AM1/22/07
to

Don't be surprised if Oracle "fixed" some of the SQL solutions. The
results will likely be different with a much larger data set - raw
output follows:

--------SQL 1--------

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

SQL_ID gcrmpdaxsaq0v, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM (SELECT

LAG(ID,1,-1) OVER (ORDER BY HEIGHT DESC) SIGNAL, HEIGHT END_HEIGHT,
LEAD
(HEIGHT,9,0) OVER (ORDER

BY HEIGHT DESC) START_HEIGHT FROM T1) R, T1 WHERE R.SIGNAL=-1
AND T1
.HEIGHT BETWEEN

R.START_HEIGHT AND R.END_HEIGHT

Plan hash value: 965685642

--------------------------------------------------------------------------------
----------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Bu
ffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
----------------------------------

| 1 | NESTED LOOPS | | 1 | 1 | 10
|00:00:00.01 |
15 | | | |

| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
8 | | | |

|* 3 | VIEW | | 16 | 1 | 10
|00:00:00.01 |
7 | | | |

| 4 | WINDOW SORT | | 16 | 1 | 256
|00:00:00.01 |
7 | 2048 | 2048 | 2048 (0)|

| 5 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

--------------------------------------------------------------------------------
----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(("R"."SIGNAL"=(-1) AND "T1"."HEIGHT">="R"."START_HEIGHT"
AND
"T1"."HEIGHT"<="R"."END_HEIGHT"))

--------SQL 2--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID bhrbngtwk242x, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ID, FIRSTNAME,
LASTNAME, HEIGH
T, AGE FROM (SELECT

ID, PERCENT_RANK() OVER (ORDER BY T1.HEIGHT DESC) POSITION,
TR.TOTAL_ROW

S FROM (SELECT

COUNT(*) TOTAL_ROWS FROM T1) TR, T1) TR, T1 WHERE
TR.POSITION<
=(10/TR.TOTAL_ROWS) AND

TR.ID=T1.ID

Plan hash value: 3064809015

--------------------------------------------------------------------------------
--------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time
| Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
--------------------------------------

|* 1 | HASH JOIN | | 1 | 1 | 10
|00:00:00.01
| 21 | 963K| 963K| 1045K (0)|

| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

|* 3 | VIEW | | 1 | 1 | 10
|00:00:00.01
| 14 | | | |

| 4 | WINDOW SORT | | 1 | 1 | 16
|00:00:00.01
| 14 | 2048 | 2048 | 2048 (0)|

| 5 | MERGE JOIN CARTESIAN | | 1 | 1 | 16
|00:00:00.01
| 14 | | | |

| 6 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

| 7 | BUFFER SORT | | 16 | 1 | 16
|00:00:00.01
| 7 | 9216 | 9216 | 8192 (0)|

| 8 | VIEW | | 1 | 1 | 1
|00:00:00.01
| 7 | | | |

| 9 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01
| 7 | | | |

| 10 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

--------------------------------------------------------------------------------
--------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("TR"."ID"="T1"."ID")

3 - filter("TR"."POSITION"<=10/"TR"."TOTAL_ROWS")

--------SQL 3--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID 27sb5vv9m0p9d, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ R.ID, FIRSTNAME, LASTNAME,
HEIGHT
, AGE FROM (SELECT ID,

ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION FROM T1) R,
T1 WHE
RE R.POSITION<=10 AND

R.ID=T1.ID

Plan hash value: 1204849371

--------------------------------------------------------------------------------
---------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time
| Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
---------------------------------------

|* 1 | HASH JOIN | | 1 | 1 | 10
|00:00:00.01
| 14 | 963K| 963K| 1045K (0)|

| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

|* 3 | VIEW | | 1 | 1 | 10
|00:00:00.01
| 7 | | | |

|* 4 | WINDOW SORT PUSHED RANK| | 1 | 1 | 11
|00:00:00.01
| 7 | 9216 | 9216 | 8192 (0)|

| 5 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

--------------------------------------------------------------------------------
---------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("R"."ID"="T1"."ID")

3 - filter("R"."POSITION"<=10)

4 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("HEIGHT")
DESC )<=1
0)

--------SQL 4--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID 8689w6f7pf5zk, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM (SELECT ID,

FIRSTNAME, LASTNAME, HEIGHT, AGE,
(FIRST_VALUE(HEIGHT) OVER (O
RDER BY HEIGHT DESC))*HEIGHT

MY_PROD, MAX_PROD FROM (SELECT MAX(PROD) MAX_PROD
FROM
(SELECT

HEIGHT*LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) PROD FROM
T1))
, T1) WHERE

MY_PROD>=MAX_PROD

Plan hash value: 3191157405

--------------------------------------------------------------------------------
---------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time
| Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
---------------------------------------

|* 1 | VIEW | | 1 | 1 | 10
|00:00:00.01
| 14 | | | |

| 2 | WINDOW SORT | | 1 | 1 | 16
|00:00:00.01
| 14 | 2048 | 2048 | 2048 (0)|

| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 16
|00:00:00.01
| 14 | | | |

| 4 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

| 5 | BUFFER SORT | | 16 | 1 | 16
|00:00:00.01
| 7 | 9216 | 9216 | 8192 (0)|

| 6 | VIEW | | 1 | 1 | 1
|00:00:00.01
| 7 | | | |

| 7 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01
| 7 | | | |

| 8 | VIEW | | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

| 9 | WINDOW SORT | | 1 | 1 | 16
|00:00:00.01
| 7 | 2048 | 2048 | 2048 (0)|

| 10 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01
| 7 | | | |

--------------------------------------------------------------------------------
---------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("MY_PROD">="MAX_PROD")

--------SQL 5--------

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

SQL_ID fj8kscbh6101p, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM T1 MINUS

SELECT ID, FIRSTNAME, LASTNAME, HEIGHT, AGE FROM (SELECT
RANK()
OVER (ORDER BY

HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT, (COUNT(*) OVER
(PARTITIO
N BY 1)-10)/COUNT(*) OVER

(PARTITION BY 1) CUT_OFF, ID, FIRSTNAME, LASTNAME,
HEIGHT, A

GE FROM T1) WHERE

ROW_PERCENT<=CUT_OFF

Plan hash value: 1588766026

--------------------------------------------------------------------------------
------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
------------------------------------

| 1 | MINUS | | 1 | | 10
|00:00:00.01 |
14 | | | |

| 2 | SORT UNIQUE | | 1 | 1 | 16
|00:00:00.01 |
7 | 9216 | 9216 | 8192 (0)|

| 3 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

| 4 | SORT UNIQUE | | 1 | 1 | 6
|00:00:00.01 |
7 | 9216 | 9216 | 8192 (0)|

|* 5 | VIEW | | 1 | 1 | 6
|00:00:00.01 |
7 | | | |

| 6 | WINDOW SORT | | 1 | 1 | 16
|00:00:00.01 |
7 | 2048 | 2048 | 2048 (0)|

| 7 | WINDOW BUFFER | | 1 | 1 | 16
|00:00:00.01 |
7 | 2048 | 2048 | 2048 (0)|

| 8 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

--------------------------------------------------------------------------------
------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter("ROW_PERCENT"<="CUT_OFF")

--------SQL 6--------

3 DOROTHY SMITH 62
21

11 DOROTHY JOHNSON 62.5
21

2 SUSAN SMITH 65
20

10 SUSAN JOHNSON 65.5
20

4 JOHN SMITH 72
35

12 JOHN JOHNSON 72.5
35

5 DAVID SMITH 73
34

13 DAVID JOHNSON 73.5
34

7 ROBERT SMITH 76
45

15 ROBERT JOHNSON 79
45

SQL_ID 7z2367nrf9qmn, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM (SELECT

RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1)
ROW_PERCENT, (C
OUNT(*) OVER (PARTITION

BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF, ID,
FIRSTNAME, LAS

TNAME, HEIGHT,

AGE FROM T1) WHERE ROW_PERCENT>CUT_OFF

Plan hash value: 3210314700

--------------------------------------------------------------------------------
----------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Bu
ffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
----------------------------------

|* 1 | VIEW | | 1 | 1 | 10
|00:00:00.01 |
7 | | | |

| 2 | WINDOW SORT | | 1 | 1 | 16
|00:00:00.01 |
7 | 2048 | 2048 | 2048 (0)|

| 3 | WINDOW BUFFER | | 1 | 1 | 16
|00:00:00.01 |
7 | 2048 | 2048 | 2048 (0)|

| 4 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

--------------------------------------------------------------------------------
----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ROW_PERCENT">"CUT_OFF")

--------SQL 7--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID 15qkur1bf390v, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ID, T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT, T1.AGE FROM (SELECT

MAX(COUNTER) COUNTER FROM (SELECT LEVEL COUNTER FROM
DUA

L CONNECT BY LEVEL<=10)) C,

(SELECT RANK() OVER (ORDER BY HEIGHT DESC) RANKING, ID,
FIRSTNAME,
LASTNAME, HEIGHT, AGE

FROM T1) T1 WHERE T1.RANKING<=C.COUNTER

Plan hash value: 3951221488

--------------------------------------------------------------------------------
----------------------------------------------

| Id | Operation | Name | Starts | E-Rows |
A-Rows | A
-Time | Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
----------------------------------------------

| 1 | NESTED LOOPS | | 1 | 1 |
10 |00:0
0:00.01 | 7 | | | |

| 2 | VIEW | | 1 | 1 |
1 |00:0
0:00.01 | 0 | | | |

| 3 | SORT AGGREGATE | | 1 | 1 |
1 |00:0
0:00.01 | 0 | | | |

| 4 | VIEW | | 1 | 1 |
10 |00:0
0:00.01 | 0 | | | |

| 5 | CONNECT BY WITHOUT FILTERING| | 1 | |
10 |00:0
0:00.01 | 0 | | | |

| 6 | FAST DUAL | | 1 | 1 |
1 |00:0
0:00.01 | 0 | | | |

|* 7 | VIEW | | 1 | 1 |
10 |00:0
0:00.01 | 7 | | | |

| 8 | WINDOW SORT | | 1 | 1 |
16 |00:0
0:00.01 | 7 | 2048 | 2048 | 2048 (0)|

| 9 | TABLE ACCESS FULL | T1 | 1 | 1 |
16 |00:0
0:00.01 | 7 | | | |

--------------------------------------------------------------------------------
----------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

7 - filter("T1"."RANKING"<="C"."COUNTER")

--------SQL 8--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID 7mz08gmmb83kx, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM (SELECT ID,

FIRSTNAME, LASTNAME, HEIGHT, AGE FROM T1 ORDER


BY HEI
GHT DESC) WHERE ROWNUM<=10

Plan hash value: 270731910

--------------------------------------------------------------------------------
-------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
-------------------------------------

|* 1 | COUNT STOPKEY | | 1 | | 10
|00:00:00.01 |
7 | | | |

| 2 | VIEW | | 1 | 1 | 10
|00:00:00.01 |
7 | | | |

|* 3 | SORT ORDER BY STOPKEY| | 1 | 1 | 10
|00:00:00.01 |
7 | 9216 | 9216 | 8192 (0)|

| 4 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

--------------------------------------------------------------------------------
-------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=10)

3 - filter(ROWNUM<=10)

--------SQL 9--------

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

13 DAVID JOHNSON 73.5
34

5 DAVID SMITH 73
34

12 JOHN JOHNSON 72.5
35

4 JOHN SMITH 72
35

10 SUSAN JOHNSON 65.5
20

2 SUSAN SMITH 65
20

11 DOROTHY JOHNSON 62.5
21

3 DOROTHY SMITH 62
21

SQL_ID fbttsnj0vd9jh, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ID, T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT, T1.AGE FROM (SELECT

ID, FIRSTNAME, LASTNAME, HEIGHT, AGE, COUNT(*)
OVER (ORD
ER BY HEIGHT DESC) POSITION FROM

T1) T1, (SELECT LEVEL COUNTER FROM DUAL CONNECT BY
LEVEL<=1
0) C WHERE T1.POSITION=C.COUNTER

Plan hash value: 1392848152

--------------------------------------------------------------------------------
--------------------------------------------

| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-T
ime | Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
--------------------------------------------

|* 1 | HASH JOIN | | 1 | 1 |
10 |00:00:
00.01 | 7 | 933K| 933K| 1043K (0)|

| 2 | VIEW | | 1 | 1 |
16 |00:00:
00.01 | 7 | | | |

| 3 | WINDOW SORT | | 1 | 1 |
16 |00:00:
00.01 | 7 | 2048 | 2048 | 2048 (0)|

| 4 | TABLE ACCESS FULL | T1 | 1 | 1 |
16 |00:00:
00.01 | 7 | | | |

| 5 | VIEW | | 1 | 1 |
10 |00:00:
00.01 | 0 | | | |

| 6 | CONNECT BY WITHOUT FILTERING| | 1 | |
10 |00:00:
00.01 | 0 | | | |

| 7 | FAST DUAL | | 1 | 1 |
1 |00:00:
00.01 | 0 | | | |

--------------------------------------------------------------------------------
--------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."POSITION"="C"."COUNTER")

--------SQL 10--------

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

SQL_ID f67f4jgmjx70n, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM T1

WHERE HEIGHT> (SELECT MAX(HEIGHT) FROM T1 WHERE HEIGHT< (SELECT
MAX(H

EIGHT) FROM T1

WHERE HEIGHT< (SELECT MAX(HEIGHT) FROM T1 WHERE HEIGHT <
(SELECT MAX(

HEIGHT) FROM T1

WHERE HEIGHT< (SELECT MAX(HEIGHT) FROM T1 WHERE HEIGHT <
(SELECT MAX(H

EIGHT) FROM T1

WHERE HEIGHT< (SELECT MAX(HEIGHT) FROM T1 WHERE HEIGHT <
(SELECT MAX(

HEIGHT) FROM T1

WHERE HEIGHT< (SELECT MAX(HEIGHT) FROM T1 WHERE HEIGHT <
(SELECT MAX(H

EIGHT) FROM T1

WHERE HEIGHT< (SELECT MAX(HEIGHT) FROM T1 )))))))))))

Plan hash value: 1643191997

--------------------------------------------------------------------------------
--------------------------

| Id | Operation | Name | Starts |
E-Rows | A-Row
s | A-Time | Buffers |

--------------------------------------------------------------------------------
--------------------------

|* 1 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
0 |00:00:00.01 | 85 |

| 2 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 77 |

|* 3 | TABLE ACCESS FULL | T1 | 1 |
1 |
6 |00:00:00.01 | 77 |

| 4 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 70 |

|* 5 | TABLE ACCESS FULL | T1 | 1 |
1 |
7 |00:00:00.01 | 70 |

| 6 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 63 |

|* 7 | TABLE ACCESS FULL | T1 | 1 |
1 |
8 |00:00:00.01 | 63 |

| 8 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 56 |

|* 9 | TABLE ACCESS FULL | T1 | 1 |
1 |
9 |00:00:00.01 | 56 |

| 10 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 49 |

|* 11 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
0 |00:00:00.01 | 49 |

| 12 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 42 |

|* 13 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
1 |00:00:00.01 | 42 |

| 14 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 35 |

|* 15 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
2 |00:00:00.01 | 35 |

| 16 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 28 |

|* 17 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
3 |00:00:00.01 | 28 |

| 18 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 21 |

|* 19 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
4 |00:00:00.01 | 21 |

| 20 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 14 |

|* 21 | TABLE ACCESS FULL | T1 | 1 |
1 | 1
5 |00:00:00.01 | 14 |

| 22 | SORT AGGREGATE | | 1 |
1 |
1 |00:00:00.01 | 7 |

| 23 | TABLE ACCESS FULL| T1 | 1 |
1 | 1
6 |00:00:00.01 | 7 |

--------------------------------------------------------------------------------
--------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("HEIGHT">)

3 - filter("HEIGHT"<)

5 - filter("HEIGHT"<)

7 - filter("HEIGHT"<)

9 - filter("HEIGHT"<)

11 - filter("HEIGHT"<)

13 - filter("HEIGHT"<)

15 - filter("HEIGHT"<)

17 - filter("HEIGHT"<)

19 - filter("HEIGHT"<)

21 - filter("HEIGHT"<)

--------SQL 12--------

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

SQL_ID 0yj3u9ctd1cf1, child number 0

-------------------------------------

select /*+ GATHER_PLAN_STATISTICS */ * from T1 where height >= (


select
MIN(height) from T1 c1 where 10 > (select count(*) from T1 c2
where
c2.height > c1.height) )

Plan hash value: 698638760

--------------------------------------------------------------------------------
--------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | B
uffers |

--------------------------------------------------------------------------------
--------

|* 1 | TABLE ACCESS FULL | T1 | 1 | 1 | 10
|00:00:00.01 |
127 |

| 2 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01 |
119 |

|* 3 | FILTER | | 1 | | 10
|00:00:00.01 |
119 |

| 4 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
7 |

| 5 | SORT AGGREGATE | | 16 | 1 | 16
|00:00:00.01 |
112 |

|* 6 | TABLE ACCESS FULL| T1 | 16 | 1 | 120
|00:00:00.01 |
112 |

--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("HEIGHT">=)

3 - filter(<10)

6 - filter("C2"."HEIGHT">:B1)

--------SQL 12--------

11 DOROTHY JOHNSON 62.5
21

13 DAVID JOHNSON 73.5
34

2 SUSAN SMITH 65
20

4 JOHN SMITH 72
35
5 DAVID SMITH 73
34
7 ROBERT SMITH 76
45
3 DOROTHY SMITH 62
21

15 ROBERT JOHNSON 79
45

10 SUSAN JOHNSON 65.5
20

12 JOHN JOHNSON 72.5
35

SQL_ID 07rna05vs1f4u, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ t1.* FROM t1 JOIN t1 t2 ON


t1.HEIGHT <=
t2.HEIGHT GROUP BY t1.ID, t1.FIRSTNAME,t1.LASTNAME, t1.HEIGHT, t1.AGE
HAVING
COUNT(*) <= 10

Plan hash value: 1311652346

--------------------------------------------------------------------------------
-------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Bu
ffers |

--------------------------------------------------------------------------------
-------

|* 1 | FILTER | | 1 | | 10
|00:00:00.01 |
119 |

| 2 | HASH GROUP BY | | 1 | 1 | 16
|00:00:00.01 |
119 |

| 3 | NESTED LOOPS | | 1 | 1 | 136
|00:00:00.01 |
119 |

| 4 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 |

|* 5 | TABLE ACCESS FULL| T1 | 16 | 1 | 136
|00:00:00.01 |
112 |

--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(COUNT(*)<=10)

5 - filter("T1"."HEIGHT"<="T2"."HEIGHT")

--------SQL 13--------

10 SUSAN JOHNSON 65.5
20
7 ROBERT SMITH 76
45

4 JOHN SMITH 72
35

12 JOHN JOHNSON 72.5
35
13 DAVID JOHNSON 73.5
34
11 DOROTHY JOHNSON 62.5
21

2 SUSAN SMITH 65
20
3 DOROTHY SMITH 62
21

5 DAVID SMITH 73
34

15 ROBERT JOHNSON 79
45

SQL_ID bvczdmkuv8pj2, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ID, T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT, T1.AGE FROM

T1, (SELECT T1.HEIGHT, COUNT(*) RANKING FROM T1, T1
T2 WHE

RE T1.HEIGHT<=T2.HEIGHT

GROUP BY T1.HEIGHT HAVING COUNT(*) BETWEEN 1 AND 10) T2
WHERE T1.H
EIGHT=T2.HEIGHT

Plan hash value: 228847817

--------------------------------------------------------------------------------
------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
------------------------------------

|* 1 | HASH JOIN | | 1 | 1 | 10
|00:00:00.01 |
126 | 963K| 963K| 1166K (0)|

| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

| 3 | VIEW | | 1 | 1 | 10
|00:00:00.01 |
119 | | | |

|* 4 | FILTER | | 1 | | 10
|00:00:00.01 |
119 | | | |

| 5 | HASH GROUP BY | | 1 | 1 | 16
|00:00:00.01 |
119 | | | |

| 6 | NESTED LOOPS | | 1 | 1 | 136
|00:00:00.01 |
119 | | | |

| 7 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

|* 8 | TABLE ACCESS FULL| T1 | 16 | 1 | 136
|00:00:00.01 |
112 | | | |

--------------------------------------------------------------------------------
------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."HEIGHT"="T2"."HEIGHT")

4 - filter((COUNT(*)>=1 AND COUNT(*)<=10))

8 - filter("T1"."HEIGHT"<="T2"."HEIGHT")

--------SQL 14--------

10 SUSAN JOHNSON 65.5
20

15 ROBERT JOHNSON 79
45

7 ROBERT SMITH 76
45

4 JOHN SMITH 72
35

12 JOHN JOHNSON 72.5
35
13 DAVID JOHNSON 73.5
34
11 DOROTHY JOHNSON 62.5
21

2 SUSAN SMITH 65
20
3 DOROTHY SMITH 62
21

5 DAVID SMITH 73
34

SQL_ID 6mvtmhybc5d4f, child number 0

-------------------------------------

SELECT /*+ GATHER_PLAN_STATISTICS */ T1.ID, T1.FIRSTNAME,
T1.LASTNAME,
T1.HEIGHT, T1.AGE FROM

T1, (SELECT T1.HEIGHT, COUNT(*) OVER (PARTITION BY 1)
TOTAL_COUNT,
COUNT(*) RANKING FROM

T1, T1 T2 WHERE T1.HEIGHT>T2.HEIGHT GROUP BY
T1.HEIGHT) T2 WHER
E

(T2.TOTAL_COUNT-T2.RANKING+1) BETWEEN 1 AND 10 AND
T2.HEIGHT=T1.HEIGHT


Plan hash value: 1648888936

--------------------------------------------------------------------------------
------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time |
Buffers | OMem | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
------------------------------------

|* 1 | HASH JOIN | | 1 | 1 | 10
|00:00:00.01 |
126 | 963K| 963K| 1166K (0)|

| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

|* 3 | VIEW | | 1 | 1 | 10
|00:00:00.01 |
119 | | | |

| 4 | WINDOW BUFFER | | 1 | 1 | 15
|00:00:00.01 |
119 | 2048 | 2048 | 2048 (0)|

| 5 | HASH GROUP BY | | 1 | 1 | 15
|00:00:00.01 |
119 | | | |

| 6 | NESTED LOOPS | | 1 | 1 | 120
|00:00:00.01 |
119 | | | |

| 7 | TABLE ACCESS FULL| T1 | 1 | 1 | 16
|00:00:00.01 |
7 | | | |

|* 8 | TABLE ACCESS FULL| T1 | 16 | 1 | 120
|00:00:00.01 |
112 | | | |

--------------------------------------------------------------------------------
------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T2"."HEIGHT"="T1"."HEIGHT")

3 - filter(("T2"."TOTAL_COUNT"-"T2"."RANKING"+1>=1 AND
"T2"."TOTAL_COUNT"-"T2
"."RANKING"+1<=10))

8 - filter("T1"."HEIGHT">"T2"."HEIGHT")

--------SQL--------

0 new messages