Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2844808
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2014-12-24 17:25:15

优化SQL时候遇到一个长SQL,里面视图中含有关键字row_number() over(partition by... order by ),
由于太长,执行计划中看不出是否视图合并。

那么实验测试一下。

--普通视图
create view v_emp as select EMPNO,ENAME,JOB,SAL,DEPTNO from emp where DEPTNO in ('20','30');

--含有row_number() over(partition by... order by )的视图
create view v_emp_2 as 
select EMPNO,ENAME,JOB,SAL,DEPTNO,row_number() over(partition by DEPTNO order by DEPTNO) num 
from emp
where DEPTNO in ('20','30');

--这两个视图中,v_emp_2只比v_emp对了一个num列,其他都一样。
SQL> select * from v_emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7499 ALLEN      SALESMAN        1600         30
      7521 WARD       SALESMAN        1250         30
      7566 JONES      MANAGER         2975         20
      7654 MARTIN     SALESMAN        1250         30
      7698 BLAKE      MANAGER         2850         30
      7788 SCOTT      ANALYST         3000         20
      7844 TURNER     SALESMAN        1500         30
      7876 ADAMS      CLERK           1100         20
      7900 JAMES      CLERK            950         30
      7902 FORD       ANALYST         3000         20


11 rows selected.

SQL> select * from v_emp_2;

     EMPNO ENAME      JOB              SAL     DEPTNO        NUM
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK            800         20          1
      7876 ADAMS      CLERK           1100         20          2
      7566 JONES      MANAGER         2975         20          3
      7788 SCOTT      ANALYST         3000         20          4
      7902 FORD       ANALYST         3000         20          5
      7900 JAMES      CLERK            950         30          1
      7844 TURNER     SALESMAN        1500         30          2
      7654 MARTIN     SALESMAN        1250         30          3
      7521 WARD       SALESMAN        1250         30          4
      7499 ALLEN      SALESMAN        1600         30          5
      7698 BLAKE      MANAGER         2850         30          6

11 rows selected.



--普通视图的执行计划
select e.EMPNO,e.ENAME,e.JOB,e.SAL,e.DEPTNO,d.DNAME from v_emp e,dept d
where e.DEPTNO=d.DEPTNO;


-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     6 |   228 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                    |         |     6 |   228 |     6  (17)| 00:00:01 |
|   2 |   INLIST ITERATOR             |         |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     2 |    26 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_DEPT |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | EMP     |     9 |   225 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="D"."DEPTNO")
   4 - access("D"."DEPTNO"=20 OR "D"."DEPTNO"=30)
   5 - filter("DEPTNO"=20 OR "DEPTNO"=30)

--含有row_number() over(partition by... order by )的视图,和上面的没有区别
--也说明了row_number() over(partition by... order by )并不会影响视图合并。
select e.EMPNO,e.ENAME,e.JOB,e.SAL,e.DEPTNO,d.DNAME from v_emp_2 e,dept d
where e.DEPTNO=d.DEPTNO;

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     6 |   228 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                    |         |     6 |   228 |     6  (17)| 00:00:01 |
|   2 |   INLIST ITERATOR             |         |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     2 |    26 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_DEPT |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | EMP     |     9 |   225 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="D"."DEPTNO")
   4 - access("D"."DEPTNO"=20 OR "D"."DEPTNO"=30)
   5 - filter("DEPTNO"=20 OR "DEPTNO"=30)
   
   
结论:
   row_number() over(partition by... order by )并不会影响视图合并。
阅读(6660) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~