相关链接
·listagg函数 ORA-01489: result
·Oracle 序列详解与12C新特性
·使用10053trace 揭秘 Oracle 12C
·Oracle分页写法大全
·Oracle进程和运行时限制

使用10053trace 揭秘 Oracle 12C TOPN 新特性

 在上一篇文章<<Oracle分页写法大全>>中介绍了Oracle 12C推出的新式TOP 写法.

今天我们就来揭秘以下其具体的实现方式
 
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> 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> 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> 
 
 
 
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分页写法大全>> http://t.bosenrui.com/dba/021b6bd6a9c40a1bc9fdc66b2a6fb0c9.html
中第二种写法是一样的.让我们来回顾一下
 
 
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@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
  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))
 

--至此我们可以下结论说,所谓的新特性就是给你一种简化的写法,但是实际的处理过程还是和之前一样.
--仅仅是提高了开发效率,但是并没有提高运行效率.
 

上一篇:Oracle 序列详解与12C新特性|下一篇:Oracle分页写法大全

微信扫一扫
获取大数据学习资料