视图中使用ROWNUM要注意

昨天晚上帮一位兄弟优化一个ebs的sql。sql有好几百行。

SQL的样子是select .... from 视图 where ....过滤条件

视图的代码贴出来就不给大家看了，比较长，另外设计保密

那个sql要跑几十秒。然后那位兄弟把视图代码弄出来，再加过滤条件，0.2秒出结果。 他搞了半天没搞出来。

那位兄弟看执行计划用 plsql 工具 F5 查看。  ----记住这句话，谁用F5看执行计划谁就是菜鸟。之后通过查看 explain plan for ...这种执行计划解决问题

该问题类似：

sqlplus / as sysdbagrant dba to scott;sqlplus scott/tigercreate table test as select * from dba_objects;SQL> create table test as select * from dba_objects;表已创建。create or replace view push_test as select rownum as id,a.* from test a;SQL> create or replace view push_test as select rownum as id,  2  a.* from test a;视图已创建。create index idx_object_id on test(object_id);SQL> set lines 200 pages 200SQL> set autot traceSQL> select * from push_test where object_id=2;执行计划----------------------------------------------------------Plan hash value: 677040414---------------------------------------------------------------------------------| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |           | 69219 |    14M|   291   (1)| 00:00:04 ||*  1 |  VIEW               | PUSH_TEST | 69219 |    14M|   291   (1)| 00:00:04 ||   2 |   COUNT             |           |       |       |            |          ||   3 |    TABLE ACCESS FULL| TEST      | 69219 |    13M|   291   (1)| 00:00:04 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=2)Note-----   - dynamic sampling used for this statement (level=6)统计信息----------------------------------------------------------        338  recursive calls          0  db block gets       1323  consistent gets       1033  physical reads          0  redo size       1459  bytes sent via SQL*Net to client        420  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedcreate or replace view push2_test as select a.* from test a;SQL> create or replace view push2_test as select  2  a.* from test a;视图已创建。SQL> select * from push2_test where object_id=2;执行计划----------------------------------------------------------Plan hash value: 985375477---------------------------------------------------------------------------------------------| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |               |     1 |   207 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |     1 |   207 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."OBJECT_ID"=2)Note-----   - dynamic sampling used for this statement (level=6)统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size       1403  bytes sent via SQL*Net to client        420  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

意思就是说 在视图 创建的时候 select rownum as ..... 多了这个ROWNUM 导致过滤条件推入不进视图，只能在视图外面过滤。 怎么看在外面过滤呢？

SQL> select * from push_test where object_id=2;执行计划----------------------------------------------------------Plan hash value: 677040414---------------------------------------------------------------------------------| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |           | 69219 |    14M|   291   (1)| 00:00:04 ||*  1 |  VIEW               | PUSH_TEST | 69219 |    14M|   291   (1)| 00:00:04 ||   2 |   COUNT             |           |       |       |            |          ||   3 |    TABLE ACCESS FULL| TEST      | 69219 |    13M|   291   (1)| 00:00:04 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=2)Note-----   - dynamic sampling used for this statement (level=6)统计信息----------------------------------------------------------        338  recursive calls          0  db block gets       1323  consistent gets       1033  physical reads          0  redo size       1459  bytes sent via SQL*Net to client        420  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

看ID=1 这里 view 前面有* ,*就表示过滤， * 在view前面，说明过滤是在view上面过滤，而不是在里面过滤的。这个就导致跑得慢了。因为视图里面有rownum，CBO 必须做一个count操作，这个时候无法进行谓词推入，因为推入了，原始sql语句意义变化。

最终征求那位哥们意见，能否去掉 视图的 rownum ，如果能去掉，就能优化，如果不能去掉，那sql无法优化。 最终肯定是 干掉了。

记住了， 视图的select后面最好不要包含rownum，否则无法谓词推入。