昨天晚上帮一位兄弟优化一个ebs的sql。

sql有好几百行。

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

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

那个sql要跑几十秒。

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

那位兄弟看运行计划用 plsql 工具 F5 查看。

----记住这句话。谁用F5看运行计划谁就是菜鸟。之后通过查看 explain plan for ...这样的运行计划解决这个问题

该问题相似：

sqlplus / as sysdba

grant dba to scott;

sqlplus scott/tiger

create 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 200

SQL> set autot trace

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

create 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，否则无法谓词推入。