VIEW_STU_SCHEME 为视图
cj_lncjb ,cj_lrcjb 为表
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lncjb a, VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'
union
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lrcjb a,VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and a.lrzt='1' and b.xh='1923206'
--这个语句查询时间需要0.06秒
但是 先创建视图:
create or replace view v_allcj as
select kch,kcm,xh from cj_lncjb
union
select kch,kcm,xh from cj_lrcjb where lrzt='1'
然后执行:
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name from
v_allcj a,VIEW_STU_SCHEME b where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'
--这个语句执行10分钟都没出结果
请问一下这是为什么?
cj_lncjb ,cj_lrcjb 为表
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lncjb a, VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'
union
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lrcjb a,VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and a.lrzt='1' and b.xh='1923206'
--这个语句查询时间需要0.06秒
但是 先创建视图:
create or replace view v_allcj as
select kch,kcm,xh from cj_lncjb
union
select kch,kcm,xh from cj_lrcjb where lrzt='1'
然后执行:
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name from
v_allcj a,VIEW_STU_SCHEME b where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'
--这个语句执行10分钟都没出结果
请问一下这是为什么?
解决方案 »
- Pro*C编译问题(oraxlc命令没有找到)
- TNS: 监听程序在 CONNECT_DATA中未获得 SERVICE_NAME
- ORACLE死锁会导致ORA-03113错误吗?
- oracle10G安装哪个,可以查看哪些语句在运行?
- 存储过程
- LONG ROW字段怎么处理?
- 在ORACLE 里,有个字段名叫DATE,我想键视图,用到这个字段,怎样能引用?
- 同样监听无法启动
- 紧急询问:如何定义和实现返回可变数组或嵌套表的函数?如何把可变数组或嵌套表当作函数传递?
- oracle转换mysql一些问题。
- *************求教一个ORACLE数据库分布系统方案*****************
- 怎样再触发器中并行执行多条sql语句?
cj_lncjb ,cj_lrcjb 为表
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lncjb a, VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'---在这里为何哥哥不改为A.XH = '1923206' 把WHERE b.kch=a.kch AND A.XH = '1923206'
union
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name
from cj_lrcjb a,VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and a.lrzt='1' and b.xh='1923206'
同样 WHERE b.kch=a.kch and A.xh='1923206'
--这个语句查询时间需要0.06秒
但是 先创建视图:
create or replace view v_allcj as
select kch,kcm,xh from cj_lncjb
union
select kch,kcm,xh from cj_lrcjb where lrzt='1'
然后执行:
select a.kch,a.kcm,a.xh,b.stu_sch_group_num,b.stu_sch_group_name from
v_allcj a,VIEW_STU_SCHEME b where b.kch=a.kch and b.xh=a.xh and b.xh='1923206'
from (select kch,kcm,xh from cj_lncjb
union
select kch,kcm,xh from cj_lrcjb where lrzt='1')a,
VIEW_STU_SCHEME b
where b.kch=a.kch and b.xh=a.xh and b.xh='1923206' 你会发现这句应该不会比用视图快
物化视图不是随着数据的改变而实时改变的
tiandehui1985() 谢谢,不过不知道这样改了会不会提高查询效率,但是最少看起来更规范