视图中使用ROWNUM要注意
昨天晚上帮一位兄弟优化一个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,否则无法谓词推入。