| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Limitting result without ROWNUM
Gerard H. Pille wrote:
> 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.
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
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
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
|* 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
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
|* 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
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
|* 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
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
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
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
| 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
SELECT /*+ GATHER_PLAN_STATISTICS */ ID, FIRSTNAME, LASTNAME,
HEIGHT,
AGE FROM (SELECT ID,
FIRSTNAME, LASTNAME, HEIGHT, AGE FROM T1 ORDER BY HEI
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
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
|* 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
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
|* 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
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
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
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
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--------
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Jan 22 2007 - 06:38:51 CST
 |
 |