SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
Session altered.
- 按工资升序输出雇员信息表的数据
SQL> select * from emp order by sal ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
- 输入工资最低的前五个员工 ```sql
SQL> select * 2 from emp 3 order by sal 4 fetch first 5 rows only ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 输出按工资最少的第4行(offset 3 跳过前三行 )到第9行(4+5)
注意经测试 fetch first 5 rows only 与 fetch next 5 rows only ;效果相同
```sql
SQL> select *
2 from emp
3 order by sal
4 offset 3 rows fetch first 5 rows only ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 返回工资最少的前30%雇员信息 14行*30%=4.2行 向上取整 ceil(4.2)=5
SQL> select *
2 from emp
3 order by sal
4 fetch first 30 percent rows only ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 返回工资最少的前25%雇员信息 14行*50%=3.5行 向上取整 ceil(4.2)=4
SQL> select *
2 from emp
3 order by sal
4 fetch first 25 percent rows only ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
-
返回工资最少的前25%雇员信息 14行*50%=3.5行 向上取整 ceil(4.2)=4
如果遇到工资一样的员工即使其不在30%内也进行显示.
请注意第5行,他之所以可以返回是由于MARTIN工资与WARD相同.
SQL> select *
2 from emp
3 order by sal
4 fetch first 25 percent rows with ties ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
Session altered.
下面我们来研究一下这个特性的原理
以下内容节选自10053 trace
这一部分是原始语句,未经过任何处理.我们如何输入的,他就如何显示.
******************************************
----- Current SQL Statement for this session (sql_id=f0s7yhz59gmb2) -----
select *
from emp
order by sal
fetch first 5 rows only
*******************************************
trace文件1755行
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPNO" "EMPNO","from$_subquery$_002"."ENAME" "ENAME","from$_subquery$_002"."JOB" "JOB","from$_subquery$_002"."MGR" "MGR","from$_subquery$_002"."HIREDATE" "HIREDATE","from$_subquery$_002"."SAL" "SAL","from$_subquery$_002"."COMM" "COMM","from$_subquery$_002"."DEPTNO" "DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO","EMP"."SAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber" FROM "DAO"."EMP" "EMP") "from$_subquery$_002" WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=5 ORDER BY "from$_subquery$_002"."rowlimit_$_0"
Objects referenced in the statement
EMP[EMP] 93262, type = 1
Objects in the hash table
Hash table Object 93262, type = 1, ownerid = 27230621063487795:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
好吧SQL很长我们来格式化一下
SELECT "from$_subquery$_002"."EMPNO" "EMPNO",
"from$_subquery$_002"."ENAME" "ENAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."MGR" "MGR",
"from$_subquery$_002"."HIREDATE" "HIREDATE",
"from$_subquery$_002"."SAL" "SAL",
"from$_subquery$_002"."COMM" "COMM",
"from$_subquery$_002"."DEPTNO" "DEPTNO"
FROM (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "rowlimit_$_0",
ROW_NUMBER() OVER(ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber"
FROM "DAO"."EMP" "EMP") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <= 5
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
真相了吧,原来还是使用的分析函数.和上一篇文章«Oracle分页写法大全»
中第二种写法是一样的.让我们来回顾一下
dao@bosenriu.com>dao@bosenriu.com>select object_id ,object_name
2 from dao_objects
3 order BY object_id desc
4 fetch first 5 rows only ;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
92015 DAO_OBJECTS
92005 WRM$_DEEP_PURGE_INTERVAL
92003 WRI$_OPTSTAT_HISTGRM_HISTORY
92002 SYS_IL0000012928C00016$$
92001 SYS_LOB0000012928C00016$$
Execution Plan
----------------------------------------------------------
Plan hash value: 1623199189
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 525 | | 1168 (1)| 00:00:01 |
|* 1 | VIEW | | 5 | 525 | | 1168 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 90914 | 2663K| 3576K| 1168 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 90914 | 2663K| | 426 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=5)
dao@bosenriu.com>select rn,object_id,object_name
2 from (
3 select object_id ,object_name ,row_number() over(order by object_id desc) rn --使用分析函数获取按object_id降序排序的序号
4 from dao_objects )
5 where rn <=5 ;
RN OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1 92015 DAO_OBJECTS
2 92005 WRM$_DEEP_PURGE_INTERVAL
3 92003 WRI$_OPTSTAT_HISTGRM_HISTORY
4 92002 SYS_IL0000012928C00016$$
5 92001 SYS_LOB0000012928C00016$$
Execution Plan
----------------------------------------------------------
Plan hash value: 1623199189
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 460 | | 1168 (1)| 00:00:01 |
|* 1 | VIEW | | 5 | 460 | | 1168 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 90914 | 2663K| 3576K| 1168 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 90914 | 2663K| | 426 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=5)
我们可以发现执行计划完全相同,所谓的新特性也就是新瓶装老酒.
在trace文件的2194行得到具体执行计划
----- Current SQL Statement for this session (sql_id=f0s7yhz59gmb2) -----
select *
from emp
order by sal
fetch first 5 rows only
sql_text_length=57
sql=select *
from emp
order by sal
fetch first 5 rows only
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | VIEW | | 5 | 565 | 4 | 00:00:01 |
| 2 | WINDOW SORT PUSHED RANK | | 14 | 532 | 4 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 | 00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."SAL")<=5)
Content of other_xml column
===========================
db_version : 12.1.0.2
parse_schema : DAO
plan_hash_full : 4103900579
plan_hash : 3291446077
plan_hash_2 : 4103900579
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
- 在trace文件中的3833行 我们可以得到第二条语句
******************************************
----- Current SQL Statement for this session (sql_id=ds9pr1zdbk372) -----
select *
from emp
order by sal
offset 3 rows fetch first 5 rows only
*******************************************
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPNO" "EMPNO","from$_subquery$_002"."ENAME" "ENAME","from$_subquery$_002"."JOB" "JOB","from$_subquery$_002"."MGR" "MGR","from$_subquery$_002"."HIREDATE" "HIREDATE","from$_subquery$_002"."SAL" "SAL","from$_subquery$_002"."COMM" "COMM","from$_subquery$_002"."DEPTNO" "DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO","EMP"."SAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber" FROM "DAO"."EMP" "EMP") "from$_subquery$_002" WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (3>=0) THEN FLOOR(TO_NUMBER(3)) ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">3 ORDER BY "from$_subquery$_002"."rowlimit_$_0"
Objects referenced in the statement
EMP[EMP] 93262, type = 1
Objects in the hash table
Hash table Object 93262, type = 1, ownerid = 27230621063487795:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
依旧我们格式化一下
SELECT "from$_subquery$_002"."EMPNO" "EMPNO",
"from$_subquery$_002"."ENAME" "ENAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."MGR" "MGR",
"from$_subquery$_002"."HIREDATE" "HIREDATE",
"from$_subquery$_002"."SAL" "SAL",
"from$_subquery$_002"."COMM" "COMM",
"from$_subquery$_002"."DEPTNO" "DEPTNO"
FROM (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "rowlimit_$_0",
ROW_NUMBER() OVER(ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber"
FROM "DAO"."EMP" "EMP") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <= CASE WHEN(3 >= 0)
THEN FLOOR(TO_NUMBER(3))
ELSE 0 END + 5
AND "from$_subquery$_002"."rowlimit_$$_rownumber" > 3
ORDER BY "from$_subquery$_002"."rowlimit_$_0" ;
套路,全是套路啊.还是分析函数.不过这个高级了一点加了一个case
如果你输入的跳过行数(offsert N rows 中的N)为负数,case会把它变成0
如果不考虑这一部分,其实SQL可以简化成
SELECT "from$_subquery$_002"."EMPNO" "EMPNO",
"from$_subquery$_002"."ENAME" "ENAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."MGR" "MGR",
"from$_subquery$_002"."HIREDATE" "HIREDATE",
"from$_subquery$_002"."SAL" "SAL",
"from$_subquery$_002"."COMM" "COMM",
"from$_subquery$_002"."DEPTNO" "DEPTNO"
FROM (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "rowlimit_$_0",
ROW_NUMBER() OVER(ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber"
FROM "DAO"."EMP" "EMP") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <=3 + 5
AND "from$_subquery$_002"."rowlimit_$$_rownumber" > 3
ORDER BY
- 在trace的5976行我们得到了最终的执行计划
----- Current SQL Statement for this session (sql_id=ds9pr1zdbk372) -----
select *
from emp
order by sal
offset 3 rows fetch first 5 rows only
sql_text_length=71
sql=select *
from emp
order by sal
offset 3 rows fetch first 5 rows only
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | VIEW | | 14 | 1582 | 4 | 00:00:01 |
| 2 | WINDOW SORT PUSHED RANK | | 14 | 532 | 4 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 | 00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (3>=0) THEN 3 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">3))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."SAL")<=CASE WHEN (3>=0) THEN 3 ELSE 0 END +5)
- 注意这条,写法基本上一模一样的.只不过人家是>= < 我们写的是between and
dao@etlbi.com>select rn,object_id,object_name
2 from (
3 select object_id ,object_name ,row_number() over(order by object_id desc) rn
4 from dao_objects )
5 where rn between 6 and 10 ;
RN OBJECT_ID OBJECT_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
6 92000 WRI$_OPTSTAT_HISTHEAD_HISTORY
7 91999 WRI$_ADV_SEGADV_SEGROW
8 91998 WRI$_ADV_OBJSPACE_CHROW_DATA
9 91997 WRI$_ADV_OBJSPACE_TREND_DATA
10 91991 WRP$_REPORTS_TIME_BANDS
Execution Plan
----------------------------------------------------------
Plan hash value: 1623199189
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 920 | | 1168 (1)| 00:00:01 |
|* 1 | VIEW | | 10 | 920 | | 1168 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 90914 | 2663K| 3576K| 1168 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 90914 | 2663K| | 426 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=6 AND "RN"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=10)
dao@bosenriu.com>select object_id ,object_name
2 from dao_objects
3 order BY object_id desc
4 offset 5 rows fetch first 5 rows only ;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
92000 WRI$_OPTSTAT_HISTHEAD_HISTORY
91999 WRI$_ADV_SEGADV_SEGROW
91998 WRI$_ADV_OBJSPACE_CHROW_DATA
91997 WRI$_ADV_OBJSPACE_TREND_DATA
91991 WRP$_REPORTS_TIME_BANDS
Execution Plan
----------------------------------------------------------
Plan hash value: 1623199189
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90914 | 9322K| | 1168 (1)| 00:00:01 |
|* 1 | VIEW | | 90914 | 9322K| | 1168 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 90914 | 2663K| 3576K| 1168 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 90914 | 2663K| | 426 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0) THEN 5
ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=CASE
WHEN (5>=0) THEN 5 ELSE 0 END +5)
- 继续看在10053 trace文件的第7615行
******************************************
----- Current SQL Statement for this session (sql_id=7dq0n9stpp0nd) -----
select *
from emp
order by sal
fetch first 30 percent rows only
*******************************************
继续看在10053 trace文件的第9313行
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPNO" "EMPNO","from$_subquery$_002"."ENAME" "ENAME","from$_subquery$_002"."JOB" "JOB","from$_subquery$_002"."MGR" "MGR","from$_subquery$_002"."HIREDATE" "HIREDATE","from$_subquery$_002"."SAL" "SAL","from$_subquery$_002"."COMM" "COMM","from$_subquery$_002"."DEPTNO" "DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO","EMP"."SAL" "rowlimit_$_0",ROW_NUMBER() OVER ( ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber",COUNT(*) OVER () "rowlimit_$$_total" FROM "DAO"."EMP" "EMP") "from$_subquery$_002" WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*30/100) ORDER BY "from$_subquery$_002"."rowlimit_$_0"
Objects referenced in the statement
EMP[EMP] 93262, type = 1
Objects in the hash table
Hash table Object 93262, type = 1, ownerid = 27230621063487795:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
格式化一下
SELECT "from$_subquery$_002"."EMPNO" "EMPNO",
"from$_subquery$_002"."ENAME" "ENAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."MGR" "MGR",
"from$_subquery$_002"."HIREDATE" "HIREDATE",
"from$_subquery$_002"."SAL" "SAL",
"from$_subquery$_002"."COMM" "COMM",
"from$_subquery$_002"."DEPTNO" "DEPTNO"
FROM (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "rowlimit_$_0",
ROW_NUMBER() OVER(ORDER BY "EMP"."SAL") "rowlimit_$$_rownumber",
COUNT(*) OVER() "rowlimit_$$_total" --在这算了一下返回的总行数
FROM "DAO"."EMP" "EMP") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber" <=
CEIL("from$_subquery$_002"."rowlimit_$$_total" * 30 / 100) --这一步利用百分百获取需要返回的行数ceil的含义是向上取整
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
- 在10053 trace文件的第9750行找到了最终的执行计划
----- Current SQL Statement for this session (sql_id=7dq0n9stpp0nd) -----
select *
from emp
order by sal
fetch first 30 percent rows only
sql_text_length=65
sql=select *
from emp
order by sal
fetch first 30 percent rows only
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | VIEW | | 14 | 1764 | 4 | 00:00:01 |
| 2 | WINDOW SORT | | 14 | 532 | 4 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 | 00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*30/100))
现在让我们来看下最后一个SQL语句就是 with ties
在10053 trace文件的15136行我们找到语句
******************************************
----- Current SQL Statement for this session (sql_id=470bwr6797w3v) -----
select *
from emp
order by sal
fetch first 25 percent rows with ties
*******************************************
在10053 trace文件的16861行
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPNO" "EMPNO","from$_subquery$_002"."ENAME" "ENAME","from$_subquery$_002"."JOB" "JOB","from$_subquery$_002"."MGR" "MGR","from$_subquery$_002"."HIREDATE" "HIREDATE","from$_subquery$_002"."SAL" "SAL","from$_subquery$_002"."COMM" "COMM","from$_subquery$_002"."DEPTNO" "DEPTNO" FROM (SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO","EMP"."SAL" "rowlimit_$_0",RANK() OVER ( ORDER BY "EMP"."SAL") "rowlimit_$$_rank",COUNT(*) OVER () "rowlimit_$$_total" FROM "DAO"."EMP" "EMP") "from$_subquery$_002" WHERE "from$_subquery$_002"."rowlimit_$$_rank"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*25/100) ORDER BY "from$_subquery$_002"."rowlimit_$_0"
Objects referenced in the statement
EMP[EMP] 93262, type = 1
Objects in the hash table
Hash table Object 93262, type = 1, ownerid = 27230621063487795:
No Dynamic Sampling Directives for the object
Return code in qosdInitDirCtx: ENBLD
===================================
SPD: END context at statement level
===================================
格式化一下
SELECT "from$_subquery$_002"."EMPNO" "EMPNO",
"from$_subquery$_002"."ENAME" "ENAME",
"from$_subquery$_002"."JOB" "JOB",
"from$_subquery$_002"."MGR" "MGR",
"from$_subquery$_002"."HIREDATE" "HIREDATE",
"from$_subquery$_002"."SAL" "SAL",
"from$_subquery$_002"."COMM" "COMM",
"from$_subquery$_002"."DEPTNO" "DEPTNO"
FROM (SELECT "EMP"."EMPNO" "EMPNO",
"EMP"."ENAME" "ENAME",
"EMP"."JOB" "JOB",
"EMP"."MGR" "MGR",
"EMP"."HIREDATE" "HIREDATE",
"EMP"."SAL" "SAL",
"EMP"."COMM" "COMM",
"EMP"."DEPTNO" "DEPTNO",
"EMP"."SAL" "rowlimit_$_0",
RANK() OVER(ORDER BY "EMP"."SAL") "rowlimit_$$_rank", --注意这里row_number函数已经被偷偷的换成了RANK了
COUNT(*) OVER() "rowlimit_$$_total"
FROM "DAO"."EMP" "EMP") "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rank" <=
CEIL("from$_subquery$_002"."rowlimit_$$_total" * 25 / 100)
ORDER BY "from$_subquery$_002"."rowlimit_$_0"
- 在10053 trace文件的17299行找到执行计划
----- Current SQL Statement for this session (sql_id=470bwr6797w3v) -----
select *
from emp
order by sal
fetch first 25 percent rows with ties
sql_text_length=70
sql=select *
from emp
order by sal
fetch first 25 percent rows with ties
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | VIEW | | 14 | 1764 | 4 | 00:00:01 |
| 2 | WINDOW SORT | | 14 | 532 | 4 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 | 00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*25/100))
至此我们可以下结论说,所谓的新特性就是给你一种简化的写法,但是实际的处理过程还是和之前一样.
仅仅是提高了开发效率,但是并没有提高运行效率.