视图:
create or replace view vw_ywjj as
select a.* from yw0000 a,ywjj00 b where a.id=b.ywid比如:select * from vw_ywjj t where t.id='123'
要花费3秒而直接查询语句:
select a.* from yw0000 a,ywjj00 b where a.id=b.ywid and a.id='123'
却只要0.1秒请问这是为什么,如何处理视图
Oracle视图
create or replace view vw_ywjj as
select a.* from yw0000 a,ywjj00 b where a.id=b.ywid比如:select * from vw_ywjj t where t.id='123'
要花费3秒而直接查询语句:
select a.* from yw0000 a,ywjj00 b where a.id=b.ywid and a.id='123'
却只要0.1秒请问这是为什么,如何处理视图
Oracle视图
这样写试试:
select * from vw_ywjj where id='123'如果再没有使用索引、你得考虑一下、为什么CBO不选择使用索引?常见不使用索引的原因大概有6个:㈠ 违背复合索引"前缀性"原理
㈡ 隐式转换
㈢ 索引列被污染
㈣ select count(*) from t;(或类似查询)
㈤ 25%
㈥ 有一段时间木有分析表了
2 select a.* from emp a,dept d where a.deptno=d.deptno;
SQL> select a.* from emp a,dept d where a.deptno=d.deptno and a.empno=7369;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |SQL> select * from test_view t where t.empno=7369;
执行计划
----------------------------------------------------------
Plan hash value: 2949544139--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
oracle的解析是从后面开始解析的,第二种写法中,因为有a.id='123',就直接的先过滤了一些数据。两者的数据量是不一样的