Oracle 11gR2 – 查看功能列评估

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |    25 | 54200 |   144   (0)|
|   1 |  FAST DUAL                           |            |     1 |       |     2   (0)|
|   2 |  FAST DUAL                           |            |     1 |       |     2   (0)|
|*  3 |  VIEW                                |            |    25 | 54200 |   144   (0)|
|*  4 |   COUNT STOPKEY                      |            |       |       |            |
|   5 |    VIEW                              |            |    26 | 56030 |   144   (0)|
|   6 |     COUNT                            |            |       |       |            |
|*  7 |      FILTER                          |            |       |       |            |
|   8 |       NESTED LOOPS ANTI              |            |    30 |  3210 |   144   (0)|
|   9 |        NESTED LOOPS OUTER            |            |    30 |  2580 |   114   (0)|
|  10 |         NESTED LOOPS ANTI            |            |    30 |  2220 |    84   (0)|
|  11 |          NESTED LOOPS ANTI           |            |    32 |  1824 |    52   (0)|
|  12 |           TABLE ACCESS BY INDEX ROWID| DEMISE     |   130K|  5979K|    18   (0)|
|  13 |            INDEX FULL SCAN           | DEMISEI4   |    34 |       |     3   (0)|
|* 14 |           INDEX RANGE SCAN           | CODESGENI3 |     1 |    10 |     1   (0)|
|* 15 |          INDEX RANGE SCAN            | TNNTMVINI1 |     1 |    17 |     1   (0)|
|* 16 |         INDEX RANGE SCAN             | USERDATAI1 |     1 |    12 |     1   (0)|
|* 17 |        INDEX RANGE SCAN              | DMSELEASI4 |     1 |    21 |     1   (0)|
|  18 |       TABLE ACCESS FULL              | QCDUAL     |     1 |       |     3   (0)|
----------------------------------------------------------------------------------------

我完全理解第二个计划是做得少,但这并不能解释为什么函数没有被评估…至少不是我可以解决的.

解决方法

A)使用优化排序完全扫描行源(限于前N行)或

B)行源的索引访问完全没有排序

这里是案例A的简化示例

SELECT *
 FROM
   (SELECT a.*,ROWNUM rnum
   FROM
     ( SELECT * FROM test_view_one ORDER BY id
     ) a
   WHERE ROWNUM <= 25
   )
 WHERE rnum >= 1

相应的执行计划如下所示(请注意,我也预先发布了部分内容
列投影 – 我将很快解释原因)：

-----------------------------------------------------------------------------------------
 | Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT         |      |    25 |   975 |       |  1034   (1)| 00:00:01 |
 |*  1 |  VIEW                    |      |    25 |   975 |       |  1034   (1)| 00:00:01 |
 |*  2 |   COUNT STOPKEY          |      |       |       |       |            |          |
 |   3 |    VIEW                  |      | 90000 |  2285K|       |  1034   (1)| 00:00:01 |
 |*  4 |     SORT ORDER BY STOPKEY|      | 90000 |   439K|  1072K|  1034   (1)| 00:00:01 |
 |   5 |      TABLE ACCESS FULL   | TEST | 90000 |   439K|       |   756   (1)| 00:00:01 |
 -----------------------------------------------------------------------------------------


 Column Projection Information (identified by operation id):
 -----------------------------------------------------------
 ... 
    3 - "A"."ID"[NUMBER,22],"A"."FUNCTION_COLUMN"[NUMBER,22]
    4 - (#keys=1) "ID"[NUMBER,"MY_PACKAGE"."MY_FUNCTION"("ID")[22]
    5 - "ID"[NUMBER,22]

在执行过程中,使用FULL SCAN访问该表,即所有记录都是红色的.
优化在SORT操作中：SORT ORDER BY STOPKEY表示不是全部
对行进行排序,但只保留前25个行和sortet.

这里是案例B的执行计划

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!