SQL实现与优化实例：rownum与TOP-N语句优化、分页查询优化

博主： admin
发布时间：2020 年 09 月 30 日
845次浏览
6999字数
分类：

rownum

rownum 列是 Oracle 的伪列。常用于结果集的 TOP-N 查询和分页查询技术。

使用 rownum 要注意如下几点：

1、在一个查询中，只可以使用 rownum <= N 来做返回记录数的限制，不能使用 rownum >= N 或者 between ... and ... 来做返回记录数的限制：

SQL> select rownum,t.* from scott.emp t where rownum <= 5;    结果的前5条记录
SQL> select rownum,t.* from scott.emp t where rownum >= 5;     没有记录返回

解决办法是使用内联视图将其实体化：

SQL> select * from (select rownum rn,t.* from scott.emp t) where rn >= 5;

2、rownum <= N 与另一条件做 and 时，是在另一条件基础上的 rownum <= N，而不是两个独立条件的并集（intersect)。体会一下：

SQL> select ename,sal,deptno from emp where deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING  5000 10
MILLER 1300 10

SQL> select ename,sal,deptno from emp where rownum <=1;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20

SQL> select ename,sal,deptno from emp where rownum <=1 and deptno=10; 
等价于：
SQL> select ename,sal,deptno from emp where deptno=10 and rownum <=1; 

ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10

3、在一个排序语句中，是先查出结果，生成 rownum，再排序，而不是排完序后再生成 rownum，例如：

select rownum rn, object_id, object_name
from dba_objects
order by object_id desc; 


        RN  OBJECT_ID OBJECT_NAME
---------- ---------- ------------------------
        37          8 C_FILE#_BLOCK#
        18          7 I_TS#
        22          6 C_TS#
        27          5 CLU$
        50          4 TAB$
         6          3 I_OBJ#
        49          2 C_OBJ#

TOP-N 语句及其优化

准备实验用的数据：

SQL> create table cyobject as select * from dba_objects;
SQL> create index idx_cy_cyobject on cyobject(object_id);

要求：查询 object_id 最大的前10个对象。

下面是常见的写法：

SQL> select * from (
select object_id, object_name from cyobject order by object_id desc
) where rownum <= 10;

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |     10 |00:00:00.14 |    2297 |       |       |          |
|*  1 |  COUNT STOPKEY          |          |      1 |        |     10 |00:00:00.14 |    2297 |       |       |          |
|   2 |   VIEW                  |          |      1 |    168K|     10 |00:00:00.14 |    2297 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|          |      1 |    168K|     10 |00:00:00.14 |    2297 |  2250K|   697K| 1999K (0)|
|   4 |     TABLE ACCESS FULL   | CYOBJECT |      1 |    168K|    157K|00:00:00.06 |    2297 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

思考一下为什么 Oracle 没有走索引呢？因索引不包含空值，而索引列没有 NOT NULL 约束，SQL 语句中也没有排除空值，如果走索引，查询结果必然不对，所以 Oracle 不能走索引。如果索引列有 NOT NULL 约束，或者在查询语句中排除空值，则 Oracle 可以走索引了：

SQL> select * from (
select object_id, object_name from cyobject where object_id is not null order by object_id desc
) where rownum <= 10;


-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |     10 |00:00:00.01 |       5 |
|*  1 |  COUNT STOPKEY                |                 |      1 |        |     10 |00:00:00.01 |       5 |
|   2 |   VIEW                        |                 |      1 |    168K|     10 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|     10 |00:00:00.01 |       5 |
|*  4 |     INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |     10 |     10 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------

尽管第一种写法做了全表扫描，但并没有对所有行进行排序，见 SORT ORDER BY STOPKEY，这就是 TOP-N 的优势。
在符合使用索引的条件下，如果在排序列上加上索引，可以极大地提高效率，但要注意索引失效的问题。
但是，不论是否走索引，我们都可以在在执行计划中可以看到 STOPKEY 字样。

一种常见的 TOP-N 写法是参考了 Oracle 分页写法，做了三层嵌套，下面分别是不走索引和走索引时的执行计划：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject order by object_id desc) t1) 
     where rn <= 10;

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     10 |00:00:00.40 |    2297 |       |       |          |
|*  1 |  VIEW                 |          |      1 |    168K|     10 |00:00:00.40 |    2297 |       |       |          |
|   2 |   COUNT               |          |      1 |        |    157K|00:00:00.36 |    2297 |       |       |          |
|   3 |    VIEW               |          |      1 |    168K|    157K|00:00:00.33 |    2297 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |    168K|    157K|00:00:00.30 |    2297 |    22M|  1753K|   20M (0)|
|   5 |      TABLE ACCESS FULL| CYOBJECT |      1 |    168K|    157K|00:00:00.06 |    2297 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------


SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject where object_id is not null order by object_id desc) t1) 
     where rn <= 10;
     
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |     10 |00:00:00.28 |    4287 |
|*  1 |  VIEW                          |                 |      1 |    168K|     10 |00:00:00.28 |    4287 |
|   2 |   COUNT                        |                 |      1 |        |    157K|00:00:00.23 |    4287 |
|   3 |    VIEW                        |                 |      1 |    168K|    157K|00:00:00.19 |    4287 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|    157K|00:00:00.15 |    4287 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |    168K|    157K|00:00:00.05 |     352 |
------------------------------------------------------------------------------------------------------------

TOP-N 总结：
1、使用两层嵌套写法，不要使用三层嵌套写法。
2、评估在排序列上创建索引，并确认使用上索引。

分页语句及优化

准备实验用的数据：

SQL> create table cyobject as select * from dba_objects;
SQL> create index idx_cy_cyobject on cyobject(object_id);

要求：查询 cyobject 表 object_id 由高到低第5至第10的前10个对象。

下面是常见的写法：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject order by object_id desc) t1) 
     where rn between 5 and 10;

第1层对数据进行排序；
第2层将 rownum 实体化；
第3层取出需要的范围数据。

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      6 |00:00:00.43 |    2297 |       |       |          |
|*  1 |  VIEW                 |          |      1 |    168K|      6 |00:00:00.43 |    2297 |       |       |          |
|   2 |   COUNT               |          |      1 |        |    157K|00:00:00.39 |    2297 |       |       |          |
|   3 |    VIEW               |          |      1 |    168K|    157K|00:00:00.36 |    2297 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |    168K|    157K|00:00:00.33 |    2297 |    22M|  1753K|   20M (0)|
|   5 |      TABLE ACCESS FULL| CYOBJECT |      1 |    168K|    157K|00:00:00.08 |    2297 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

优化写法：

select * from 
(
select t.*, rownum rn from 
(select * from cyobject order by object_id desc) t 
where rownum <= 10
) where rn>=6;

Plan hash value: 3125449024

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |      5 |00:00:00.20 |    2297 |       |       |          |
|*  1 |  VIEW                    |          |      1 |     10 |      5 |00:00:00.20 |    2297 |       |       |          |
|*  2 |   COUNT STOPKEY          |          |      1 |        |     10 |00:00:00.20 |    2297 |       |       |          |
|   3 |    VIEW                  |          |      1 |    168K|     10 |00:00:00.20 |    2297 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|          |      1 |    168K|     10 |00:00:00.20 |    2297 |  5297K|   950K| 4708K (0)|
|   5 |      TABLE ACCESS FULL   | CYOBJECT |      1 |    168K|    157K|00:00:00.07 |    2297 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

这儿的分页语句没有使用索引的原因跟上面是一样的，下面分别是使用上索引后，优化前后的执行计划：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject where object_id is not null order by object_id desc) t1) 
     where rn between 5 and 10;

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |      6 |00:00:00.26 |    4287 |
|*  1 |  VIEW                          |                 |      1 |    168K|      6 |00:00:00.26 |    4287 |
|   2 |   COUNT                        |                 |      1 |        |    157K|00:00:00.21 |    4287 |
|   3 |    VIEW                        |                 |      1 |    168K|    157K|00:00:00.17 |    4287 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|    157K|00:00:00.13 |    4287 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |    168K|    157K|00:00:00.04 |     352 |
------------------------------------------------------------------------------------------------------------

select * from 
(
select t.*, rownum rn from 
(select * from cyobject where object_id is not null order by object_id desc) t 
where rownum <= 10
) where rn>=6; 


------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |      5 |00:00:00.01 |       5 |
|*  1 |  VIEW                          |                 |      1 |     10 |      5 |00:00:00.01 |       5 |
|*  2 |   COUNT STOPKEY                |                 |      1 |        |     10 |00:00:00.01 |       5 |
|   3 |    VIEW                        |                 |      1 |    168K|     10 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|     10 |00:00:00.01 |       5 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |     10 |     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

TOP-N 总结：
1、使用优化写法。
2、评估在排序列上创建索引，并确认使用上索引。

-- By 许望（RHCA、OCM、VCP）

最后修改：2020 年 09 月 30 日 06 : 17 PM

如果觉得我的文章对你有用，请随意赞赏

Tags: Oracle暂未分类

SQL实现与优化实例：rownum与TOP-N语句优化、分页查询优化

admin • 2020 年 09 月 30 日

rownum

rownum 列是 Oracle 的伪列。常用于结果集的 TOP-N 查询和分页查询技术。

SQL> select rownum,t.* from scott.emp t where rownum <= 5;    结果的前5条记录
SQL> select rownum,t.* from scott.emp t where rownum >= 5;     没有记录返回

解决办法是使用内联视图将其实体化：

SQL> select * from (select rownum rn,t.* from scott.emp t) where rn >= 5;

2、rownum <= N 与另一条件做 and 时，是在另一条件基础上的 rownum <= N，而不是两个独立条件的并集（intersect)。体会一下：

SQL> select ename,sal,deptno from emp where deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING  5000 10
MILLER 1300 10

SQL> select ename,sal,deptno from emp where rownum <=1;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20

SQL> select ename,sal,deptno from emp where rownum <=1 and deptno=10; 
等价于：
SQL> select ename,sal,deptno from emp where deptno=10 and rownum <=1; 

ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10

3、在一个排序语句中，是先查出结果，生成 rownum，再排序，而不是排完序后再生成 rownum，例如：

select rownum rn, object_id, object_name
from dba_objects
order by object_id desc; 


        RN  OBJECT_ID OBJECT_NAME
---------- ---------- ------------------------
        37          8 C_FILE#_BLOCK#
        18          7 I_TS#
        22          6 C_TS#
        27          5 CLU$
        50          4 TAB$
         6          3 I_OBJ#
        49          2 C_OBJ#

TOP-N 语句及其优化

准备实验用的数据：

SQL> create table cyobject as select * from dba_objects;
SQL> create index idx_cy_cyobject on cyobject(object_id);

要求：查询 object_id 最大的前10个对象。

下面是常见的写法：

SQL> select * from (
select object_id, object_name from cyobject order by object_id desc
) where rownum <= 10;

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |     10 |00:00:00.14 |    2297 |       |       |          |
|*  1 |  COUNT STOPKEY          |          |      1 |        |     10 |00:00:00.14 |    2297 |       |       |          |
|   2 |   VIEW                  |          |      1 |    168K|     10 |00:00:00.14 |    2297 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|          |      1 |    168K|     10 |00:00:00.14 |    2297 |  2250K|   697K| 1999K (0)|
|   4 |     TABLE ACCESS FULL   | CYOBJECT |      1 |    168K|    157K|00:00:00.06 |    2297 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

SQL> select * from (
select object_id, object_name from cyobject where object_id is not null order by object_id desc
) where rownum <= 10;


-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |     10 |00:00:00.01 |       5 |
|*  1 |  COUNT STOPKEY                |                 |      1 |        |     10 |00:00:00.01 |       5 |
|   2 |   VIEW                        |                 |      1 |    168K|     10 |00:00:00.01 |       5 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|     10 |00:00:00.01 |       5 |
|*  4 |     INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |     10 |     10 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------

一种常见的 TOP-N 写法是参考了 Oracle 分页写法，做了三层嵌套，下面分别是不走索引和走索引时的执行计划：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject order by object_id desc) t1) 
     where rn <= 10;

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     10 |00:00:00.40 |    2297 |       |       |          |
|*  1 |  VIEW                 |          |      1 |    168K|     10 |00:00:00.40 |    2297 |       |       |          |
|   2 |   COUNT               |          |      1 |        |    157K|00:00:00.36 |    2297 |       |       |          |
|   3 |    VIEW               |          |      1 |    168K|    157K|00:00:00.33 |    2297 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |    168K|    157K|00:00:00.30 |    2297 |    22M|  1753K|   20M (0)|
|   5 |      TABLE ACCESS FULL| CYOBJECT |      1 |    168K|    157K|00:00:00.06 |    2297 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------


SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject where object_id is not null order by object_id desc) t1) 
     where rn <= 10;
     
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |     10 |00:00:00.28 |    4287 |
|*  1 |  VIEW                          |                 |      1 |    168K|     10 |00:00:00.28 |    4287 |
|   2 |   COUNT                        |                 |      1 |        |    157K|00:00:00.23 |    4287 |
|   3 |    VIEW                        |                 |      1 |    168K|    157K|00:00:00.19 |    4287 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|    157K|00:00:00.15 |    4287 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |    168K|    157K|00:00:00.05 |     352 |
------------------------------------------------------------------------------------------------------------

TOP-N 总结：
1、使用两层嵌套写法，不要使用三层嵌套写法。
2、评估在排序列上创建索引，并确认使用上索引。

分页语句及优化

准备实验用的数据：

SQL> create table cyobject as select * from dba_objects;
SQL> create index idx_cy_cyobject on cyobject(object_id);

要求：查询 cyobject 表 object_id 由高到低第5至第10的前10个对象。

下面是常见的写法：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject order by object_id desc) t1) 
     where rn between 5 and 10;

第1层对数据进行排序；
第2层将 rownum 实体化；
第3层取出需要的范围数据。

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      6 |00:00:00.43 |    2297 |       |       |          |
|*  1 |  VIEW                 |          |      1 |    168K|      6 |00:00:00.43 |    2297 |       |       |          |
|   2 |   COUNT               |          |      1 |        |    157K|00:00:00.39 |    2297 |       |       |          |
|   3 |    VIEW               |          |      1 |    168K|    157K|00:00:00.36 |    2297 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |    168K|    157K|00:00:00.33 |    2297 |    22M|  1753K|   20M (0)|
|   5 |      TABLE ACCESS FULL| CYOBJECT |      1 |    168K|    157K|00:00:00.08 |    2297 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

优化写法：

select * from 
(
select t.*, rownum rn from 
(select * from cyobject order by object_id desc) t 
where rownum <= 10
) where rn>=6;

Plan hash value: 3125449024

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |      1 |        |      5 |00:00:00.20 |    2297 |       |       |          |
|*  1 |  VIEW                    |          |      1 |     10 |      5 |00:00:00.20 |    2297 |       |       |          |
|*  2 |   COUNT STOPKEY          |          |      1 |        |     10 |00:00:00.20 |    2297 |       |       |          |
|   3 |    VIEW                  |          |      1 |    168K|     10 |00:00:00.20 |    2297 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|          |      1 |    168K|     10 |00:00:00.20 |    2297 |  5297K|   950K| 4708K (0)|
|   5 |      TABLE ACCESS FULL   | CYOBJECT |      1 |    168K|    157K|00:00:00.07 |    2297 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

这儿的分页语句没有使用索引的原因跟上面是一样的，下面分别是使用上索引后，优化前后的执行计划：

SQL> select * from 
     (select rownum rn, t1.* from 
        (select * from cyobject where object_id is not null order by object_id desc) t1) 
     where rn between 5 and 10;

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |      6 |00:00:00.26 |    4287 |
|*  1 |  VIEW                          |                 |      1 |    168K|      6 |00:00:00.26 |    4287 |
|   2 |   COUNT                        |                 |      1 |        |    157K|00:00:00.21 |    4287 |
|   3 |    VIEW                        |                 |      1 |    168K|    157K|00:00:00.17 |    4287 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|    157K|00:00:00.13 |    4287 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |    168K|    157K|00:00:00.04 |     352 |
------------------------------------------------------------------------------------------------------------

select * from 
(
select t.*, rownum rn from 
(select * from cyobject where object_id is not null order by object_id desc) t 
where rownum <= 10
) where rn>=6; 


------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |      5 |00:00:00.01 |       5 |
|*  1 |  VIEW                          |                 |      1 |     10 |      5 |00:00:00.01 |       5 |
|*  2 |   COUNT STOPKEY                |                 |      1 |        |     10 |00:00:00.01 |       5 |
|   3 |    VIEW                        |                 |      1 |    168K|     10 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CYOBJECT        |      1 |    168K|     10 |00:00:00.01 |       5 |
|*  5 |      INDEX FULL SCAN DESCENDING| IDX_CY_CYOBJECT |      1 |     10 |     10 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

TOP-N 总结：
1、使用优化写法。
2、评估在排序列上创建索引，并确认使用上索引。