优化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 )并不会影响视图合并。
阅读(6737) | 评论(0) | 转发(0) |
