我有一个查询,如下所示：
select <field list>
from <table list>
where <join conditions>
and <condition list>
and PrimaryKey in (select PrimaryKey from <table list>
where <join list>
and <condition list>)
and PrimaryKey not in (select PrimaryKey from <table list>
where <join list>
and <condition list>)
子选择查询都有自己的多个子选择查询,我没有显示,以免混乱语句.
我团队的开发者之一认为一个观点会更好.我不同意,sql语句使用程序传入的变量(基于用户的登录ID).
在使用视图时应该使用sql语句吗？有什么样的性能增益问题是在自己的运行sql语句与常规表相对于视图. (请注意,所有联接/条件与索引列相关,因此不应该是一个问题.)
编辑澄清…
这是我正在使用的查询：
select obj_id
from object
where obj_id in(
(select distinct(sec_id)
from security
where sec_type_id = 494
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
and sec_obj_id in (
select obj_id from object
where obj_ot_id in (select of_ot_id
from obj_form
left outer join obj_type
on ot_id = of_ot_id
where ot_app_id = 87
and of_id in (select sec_obj_id
from security
where sec_type_id = 493
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
)
and of_usage_type_id = 131
)
)
)
)
or
(obj_ot_id in (select of_ot_id
from obj_form
left outer join obj_type
on ot_id = of_ot_id
where ot_app_id = 87
and of_id in (select sec_obj_id
from security
where sec_type_id = 493
and (
(sec_usergroup_id = 3278
and sec_usergroup_type_id = 230)
or
(sec_usergroup_id in (select ug_gi_id
from user_group
where ug_ui_id = 3278)
and sec_usergroup_type_id = 231)
)
)
and of_usage_type_id = 131
)
and
obj_id not in (select sec_obj_id
from security
where sec_type_id = 494)
)
解决方法
根据数据库供应商,一般来说,针对视图执行查询会将视图中定义的sql与Where子句谓词和Order By子句排序表达式相结合,该表达式附加到您传递给View的sql中,以提出一个组合的完整SQL查询执行.然后执行它就好像它已经被传递给查询processsor,所以应该没有区别.
视图是一种组织工具,而不是性能增强工具.
When an sql statement references a nonindexed view,the parser and query optimizer analyze the source of both the sql statement and the view and then resolve them into a single execution plan. There is not one plan for the sql statement and a separate plan for the view.
