ROWNUM的一个性能陷阱

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2009-06-07 15:00:23

分享到 

在之前的文章<Oracle中ROWNUM的使用技巧>中，我们已经介绍过ROWNUM的使用及一些要注意的问题。这里，再介绍一种由ROWNUM导致的性能陷阱。事实上，如果能清楚了解ROWNUM的基本机制，那么对这个陷阱就很容易理解。   我们有定义如下视图，   CREATE OR REPLACE VIEW MY_VIEW AS SELECT ROWNUM AS ID,        T0.COMP_ID AS COMPANY_ID,        T0.SH_ID AS B_NUM,        '' AS L_NUM,        '' AS C_NUM,        T0.C_COMP_ID AS SP_COMP_ID,        '' AS C_TYPE,        T0.REC_UPD_DT AS LAST_MOD_DT FROM T_COM T0 WHERE T0.DELETED_IND = 0 AND T0.VIEW_TYPE = 'BK'   在引用表T_COM的(COMP_ID, VIEW_TYPE, DELETED_IND, SH_ID)已经建立索引，且COMP_ID的选择性很强，VIEW_TYPE和DELETED_IND选择性则很弱。 再看下面的查询   select id,        company_id,        c_num,        b_num,        l_num,        last_mod_dt,        c_type,        sp_comp_id from MY_VIEW where company_id = '1111111111'   嗯，优化器应该先进行查询重写（Predicate Pushing），将主查询中的company_id = '1111111111'与视图中的T0.DELETED_IND = 0 AND T0.VIEW_TYPE = 'BK'共同作用到表T_COM上，命中到索引。但是，是这样吗，看看它的查询计划：   ------------------------------------------------------------------------------------------ | Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT     |                   |   397K|    26M| 49582   (2)| 00:09:55 | |*  1 |  FILTER              |                   |       |       |            |          | |*  2 |   VIEW               | MY_VIEW           |   397K|    26M| 49582   (2)| 00:09:55 | |   3 |    COUNT             |                   |       |       |            |          | |*  4 |     TABLE ACCESS FULL| T_COM             |   397K|    23M| 49582   (2)| 00:09:55 | ------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("COMPANY_ID"='111111111')    3 - filter("T0"."DELETED_IND"=0 AND "T0"."VIEW_TYPE"='BK')   并未命中索引。仔细分析视图的定义语句，其中包含了对ROWNUM的查询。ROWNUM是一个虚字段，只有产生结果集时才会有值。因此，为保证逻辑结果，优化器并没有将视图查询条件与外部主查询条件合并后再执行查询操作，而是先执行子查询部分（条件不足以命中索引），得到结果集（计划中的第一步Full Table Scan）和对应的ROWNUM值（计划中的第二步COUNT）以后再根据外部条件对结果集过滤（计划中的第三步FILTER）。当我们将ROWNUM从视图中拿掉，执行计划就能像我们之前所预想的命中索引了。   CREATE OR REPLACE VIEW MY_VIEW AS SELECT 1 AS ID,        T0.COMP_ID AS COMPANY_ID,        T0.SH_ID AS B_NUM,        '' AS L_NUM,        '' AS C_NUM,        T0.C_COMP_ID AS SP_COMP_ID,        '' AS C_TYPE,        T0.REC_UPD_DT AS LAST_MOD_DT FROM T_COM T0 WHERE T0.DELETED_IND = 0 AND T0.VIEW_TYPE = 'BK'     ------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |            |   299 | 20631 |   340   (1)| 00:00:05 | |   1 |  TABLE ACCESS BY INDEX ROWID| T_COM      |   299 | 20631 |   340   (1)| 00:00:05 | |*  2 |   INDEX RANGE SCAN          | T_COM_INDX4|   299 |       |    77   (0)| 00:00:01 | ------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("T0"."COMP_ID"='111111111' AND "T0"."DELETED_IND"=0 AND               "T0"."VIEW_TYPE"='BK')        filter("T0"."DELETED_IND"=0 AND "T0"."VIEW_TYPE"='BK')   如果开发人员不深入理解ROWNUM的机制，而简单的将其理解为一个子增长的字段的话，就很容易掉如这样的陷阱了。     补充：看看假如在子查询或视图中存在ROWNUM时，查询重写和不重写的逻辑结果的差异吧。   SQL> select id, owner, object_name   2  from (select rownum as id, owner, object_name from t_test1 where object_name like 'TEM%') v   3  where owner='DEMO';           ID OWNER                                              OBJECT_NAME ---------- -------------------------------------------------- ------------------------          5 DEMO                                               TEMP_BOOK_CMP SQL> SQL> select id, owner, object_name   2  from (select rownum as id, owner, object_name from t_test1 where object_name like 'TEM%' and owner='DEMO') v;           ID OWNER                                              OBJECT_NAME ---------- -------------------------------------------------- ------------------------          1 DEMO                                               TEMP_BOOK_CMP   真实字段的内容没有不同，但是由ROWNUM返回的数据就不同了。   --The end--