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? 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
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.
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 --
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
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.
> 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...
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.
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)
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.
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.
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.
> 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
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.
> 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
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?
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.
:-)
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.
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.
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;
>
> 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.
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
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
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--------