row_number() over 和union的性能比较

SQL> set autotrace traceonly;
SQL> ed
已写入 file afiedt.buf

1 select id, name, sal, cydate
2 from (select id,
3 name,
4 sal,
5 cydate,
6 row_number() over(partition by name order by cydate desc) rs

7 from awen.row_number
8 where id = 12)
9* where rs < 4
SQL> /

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 2241757104

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10 | 600 | 10 (10)| 00:00:01 |

|* 1 | VIEW | | 10 | 600 | 10 (10)| 00:00:01 |

|* 2 | WINDOW SORT PUSHED RANK| | 10 | 470 | 10 (10)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | ROW_NUMBER | 10 | 470 | 9 (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RS"<4)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "NAME" ORDER BY
INTERNAL_FUNCTION("CYDATE") DESC )<4)
3 - filter("ID"=12)

Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL> ed
已写入 file afiedt.buf

1 (select id, name, sal, cydate
2 from (select id, name, sal, cydate, rownum rs, rn
3 from (select id, name, sal, cydate, rownum rn
4 from awen.row_number
5 where id = 12
6 and name = 'a'
7 order by cydate desc))
8 where rs < 4) union
9 (select id, name, sal, cydate
10 from (select id, name, sal, cydate, rownum rs, rn
11 from (select id, name, sal, cydate, rownum rn
12 from awen.row_number
13 where id = 12
14 and name = 'b'
15 order by cydate desc))
16* where rs < 4)
SQL> /

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 3795593878

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 10 | 600 | 22 (60)| 00:00:01 |

| 1 | SORT UNIQUE | | 10 | 600 | 22 (60)| 00:00:01 |

| 2 | UNION-ALL | | | | |

|* 3 | VIEW | | 5 | 300 | 10 (10)| 00:00:01 |

| 4 | COUNT | | | | | |

| 5 | VIEW | | 5 | 235 | 10 (10)| 00:00:01 |

| 6 | SORT ORDER BY | | 5 | 235 | 10 (10)| 00:00:01 |

| 7 | COUNT | | | | | |

|* 8 | TABLE ACCESS FULL| ROW_NUMBER | 5 | 235 | 9 (0)| 00:00:01 |

|* 9 | VIEW | | 5 | 300 | 10 (10)| 00:00:01 |

| 10 | COUNT | | | | | |

| 11 | VIEW | | 5 | 235 | 10 (10)| 00:00:01 |

| 12 | SORT ORDER BY | | 5 | 235 | 10 (10)| 00:00:01 |

| 13 | COUNT | | | | | |

|* 14 | TABLE ACCESS FULL| ROW_NUMBER | 5 | 235 | 9 (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("RS"<4)
8 - filter("ID"=12 AND "NAME"='a')
9 - filter("RS"<4)
14 - filter("ID"=12 AND "NAME"='b')

Note
-----
- dynamic sampling used for this statement


统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed